Oracle中自定义类型:
CREATE OR REPLACE TYPE LIST_VARCHAR AS VARRAY(50) of VARCHAR2(500)存储过程:
create or replace procedure WJW_TEST(IN_BLNG_CDV IN VARCHAR2, IN_PST_CDV IN VARCHAR2,OUT_SY OUT LIST_VARCHAR, OUT_SRCH_COUNT OUT NUMBER, OUT_STATUS OUT NUMBER, OUT_SQLCODE OUT NUMBER) is
......
begin
......
end WJW_TEST;java程序:import java.sql.Connection;import oracle.sql.ARRAY;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.OracleCallableStatement;......ARRAY recordArray;
String s = "{call WJW_TEST(?,?,?,?,?,?)}";
OracleCallableStatement oraclecallablestatement = (OracleCallableStatement)con.prepareCall(s);
s = null;
String parameter1="";
String parameter2="";
oraclecallablestatement.setString(1,parameter1);
oraclecallablestatement.setString(2,parameter2);
oraclecallablestatement.registerOutParameter(3,OracleTypes.ARRAY,"LIST_VARCHAR");
oraclecallablestatement.registerOutParameter(4,2);
oraclecallablestatement.registerOutParameter(5,2);
oraclecallablestatement.registerOutParameter(6,2);
oraclecallablestatement.executeUpdate();
System.out.println("aaa"); //测试输出1
recordArray =oraclecallablestatement.getARRAY(3); //在这个地方出错
System.out.println("bbb"); //测试输出2......运行结果:
aaa //测试输出1
java.lang.ClassCastException //出错信息
请问,我取出的类型是ARRAY型呀,为什么会出错?怎么改?
CREATE OR REPLACE TYPE LIST_VARCHAR AS VARRAY(50) of VARCHAR2(500)存储过程:
create or replace procedure WJW_TEST(IN_BLNG_CDV IN VARCHAR2, IN_PST_CDV IN VARCHAR2,OUT_SY OUT LIST_VARCHAR, OUT_SRCH_COUNT OUT NUMBER, OUT_STATUS OUT NUMBER, OUT_SQLCODE OUT NUMBER) is
......
begin
......
end WJW_TEST;java程序:import java.sql.Connection;import oracle.sql.ARRAY;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.OracleCallableStatement;......ARRAY recordArray;
String s = "{call WJW_TEST(?,?,?,?,?,?)}";
OracleCallableStatement oraclecallablestatement = (OracleCallableStatement)con.prepareCall(s);
s = null;
String parameter1="";
String parameter2="";
oraclecallablestatement.setString(1,parameter1);
oraclecallablestatement.setString(2,parameter2);
oraclecallablestatement.registerOutParameter(3,OracleTypes.ARRAY,"LIST_VARCHAR");
oraclecallablestatement.registerOutParameter(4,2);
oraclecallablestatement.registerOutParameter(5,2);
oraclecallablestatement.registerOutParameter(6,2);
oraclecallablestatement.executeUpdate();
System.out.println("aaa"); //测试输出1
recordArray =oraclecallablestatement.getARRAY(3); //在这个地方出错
System.out.println("bbb"); //测试输出2......运行结果:
aaa //测试输出1
java.lang.ClassCastException //出错信息
请问,我取出的类型是ARRAY型呀,为什么会出错?怎么改?
(2) IN_PST_CDV IN VARCHAR2,
(3) OUT_SY OUT LIST_VARCHAR,
(4) OUT_SRCH_COUNT OUT NUMBER,
(5) OUT_STATUS OUT NUMBER,
(6) OUT_SQLCODE OUT NUMBER
--------------------------------------------
只是你的oracle存储工程的六个参数,
recordArray =oraclecallablestatement.getARRAY(3);取的是哪个呀?
是上面的(3)还是(5)?
recordArray =oraclecallablestatement.getARRAY(3); 换成recordArray =oraclecallablestatement.getArray(3);
虽然能取得值,却是乱码!结果是"???".为什么呀?
以下是我的测试程序,jdk1.5+oracle 8.16 通过
package dbtest;import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;public class oraarrt
{
public static void main(String[] argc)
{
try { int oracleId = CharacterSet.ZHS16GBK_CHARSET;
CharacterSet dbCharset = CharacterSet.make(oracleId);
DriverManager.registerDriver ( new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection
("jdbc:oracle:oci8:system/manager@ora9");
ARRAY recordArray;
String s = "{call WJW_TEST(?,?,?,?,?,?)}";
OracleCallableStatement oraclecallablestatement = (OracleCallableStatement)conn.prepareCall(s);
s = null;
String parameter1="";
String parameter2="";
oraclecallablestatement.setString(1,parameter1);
oraclecallablestatement.setString(2,parameter2);
oraclecallablestatement.registerOutParameter(3,OracleTypes.ARRAY,"LIST_VARCHAR");
oraclecallablestatement.registerOutParameter(4,2);
oraclecallablestatement.registerOutParameter(5,2);
oraclecallablestatement.registerOutParameter(6,2);
oraclecallablestatement.executeUpdate();
System.out.println("aaa"); //测试输出1
recordArray =oraclecallablestatement.getARRAY(3); //在这个地方出错
String[] values = (String[]) recordArray.getArray();
for (int i = 0; i < values.length; i++)
{
oracle.sql.CHAR out_value = new oracle.sql.CHAR(values[i],dbCharset );
System.out.println(">> index " + i + " = " + out_value);
}
System.out.println("bbb"); //测试输出2
}
catch(Exception e)
{
System.out.println(e);
}
}
}
------------------------------------------
aaa
>> index 0 = 111
>> index 1 = 222
>> index 2 = 333
bbb
create or replace procedure WJW_TEST(IN_BLNG_CDV IN VARCHAR2, IN_PST_CDV IN VARCHAR2,OUT_SY OUT LIST_VARCHAR, OUT_SRCH_COUNT OUT NUMBER, OUT_STATUS OUT NUMBER, OUT_SQLCODE OUT NUMBER) is
begin
OUT_SRCH_COUNT:=1;
OUT_SY:=LIST_VARCHAR('111','222','333');
end WJW_TEST;
/
在有这个标志的地方出错"//在这个地方出错",
错误信息是:java.lang.ClassCastException