我的程序如下:
package text;import java.sql.*; import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;public class text {
String dbUrl = "jdbc:oracle:thin:@192.168.0.194:1521:ORCL";
String theUser = "demo1";
String thePw = "demo1";
static Connection c = null;
static Statement conn;
ResultSet rs = null;
public text(){
try
{
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
c = DriverManager.getConnection(dbUrl, theUser, thePw);
}
catch (Exception e)
{
e.printStackTrace();
}
} public void close() {
try
{
c.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
public static void main(String[] args) throws SQLException
{
text newConnect=new text();
final String T_NUMBER = "T_NUMBER";
CallableStatement cstmt = null;
String procedure = "{call transferData(?)}";
ArrayDescriptor varchar2Desc = null;
String a[]={"123","12b","faf"};
try { cstmt = c.prepareCall(procedure);
varchar2Desc = ArrayDescriptor.createDescriptor(T_NUMBER,c);
ARRAY vArray = new ARRAY(varchar2Desc,c,a);
cstmt.setArray(1,vArray);
cstmt.executeUpdate();
c.commit();
}
catch (SQLException e) {
e.printStackTrace();
}
newConnect.close();
}
}
运行结果后,数据库中友记录数但没有具体数据,但将数组a改为int a[]={1,2,3,4};时能正确插入数据。也就是说当插入数据是整形数据时执行正确,但当数据位字符串数组时,不能正确插入。求高手指教,在此万分感谢!
package text;import java.sql.*; import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;public class text {
String dbUrl = "jdbc:oracle:thin:@192.168.0.194:1521:ORCL";
String theUser = "demo1";
String thePw = "demo1";
static Connection c = null;
static Statement conn;
ResultSet rs = null;
public text(){
try
{
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
c = DriverManager.getConnection(dbUrl, theUser, thePw);
}
catch (Exception e)
{
e.printStackTrace();
}
} public void close() {
try
{
c.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
public static void main(String[] args) throws SQLException
{
text newConnect=new text();
final String T_NUMBER = "T_NUMBER";
CallableStatement cstmt = null;
String procedure = "{call transferData(?)}";
ArrayDescriptor varchar2Desc = null;
String a[]={"123","12b","faf"};
try { cstmt = c.prepareCall(procedure);
varchar2Desc = ArrayDescriptor.createDescriptor(T_NUMBER,c);
ARRAY vArray = new ARRAY(varchar2Desc,c,a);
cstmt.setArray(1,vArray);
cstmt.executeUpdate();
c.commit();
}
catch (SQLException e) {
e.printStackTrace();
}
newConnect.close();
}
}
运行结果后,数据库中友记录数但没有具体数据,但将数组a改为int a[]={1,2,3,4};时能正确插入数据。也就是说当插入数据是整形数据时执行正确,但当数据位字符串数组时,不能正确插入。求高手指教,在此万分感谢!
引用 5 楼 hebeishimeng 的回复:
看看表的字段类型,长度
建表语句:
create table test_table (id varchar2(100));
存储过程:
create or replace procedure transferData(autoTypeIds t_number) is
begin
for i in 1..autoTypeIds.count loop
insert into test_table(id) values(autoTypeIds(i));
end loop;
commit;
end transferData;
其中t_number为自定义类型:
create or replace type t_number as table of varchar2(100)