创建包:traveler_assistance_package
创建自定义数组:TYPE lgn IS table OF NVARCHAR2(20);
存储过程:
procedure country_languages
( country_n in NVARCHAR2,
c_n out NVARCHAR2,
l_ns_out out lgn,
ofls_out out lgn
);------------------------------------------
在oracle端调用这个存储过程是没有问题的,但是在jsp中用java调用就出错:java.sql.SQLException: 无效的名称模式: FOF.LGN------------------------------------------------------
我的java代码是这样的:String procedure = "{call traveler_assistance_package.country_languages(?,?,?,?) }";OracleCallableStatement cstmt=(OracleCallableStatement)con.prepareCall(procedure);
cstmt.setString(1,countryname); cstmt.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);//输出参数
cstmt.registerOutParameter(3,oracle.jdbc.OracleTypes.ARRAY,"LGN");
cstmt.registerOutParameter(4,oracle.jdbc.OracleTypes.ARRAY,"LGN");//换成 "traveler_assistance_package.LGN"也是不行
cstmt.execute();
//traveler_assistance_package.lgn");//,"VARCHAR2_TABLE");
String c_n=cstmt.getString(2);
ARRAY array = cstmt.getARRAY(3);
String[] splArray=(String[])array.getArray();ARRAY array1 = cstmt.getARRAY(4);
String[] oflArray=(String[])array1.getArray();----------------------------------------------------------------------这段java代码哪里错了啊,怎么修改啊~~~~(>_<)~~~~
高手帮帮忙吧~~
创建自定义数组:TYPE lgn IS table OF NVARCHAR2(20);
存储过程:
procedure country_languages
( country_n in NVARCHAR2,
c_n out NVARCHAR2,
l_ns_out out lgn,
ofls_out out lgn
);------------------------------------------
在oracle端调用这个存储过程是没有问题的,但是在jsp中用java调用就出错:java.sql.SQLException: 无效的名称模式: FOF.LGN------------------------------------------------------
我的java代码是这样的:String procedure = "{call traveler_assistance_package.country_languages(?,?,?,?) }";OracleCallableStatement cstmt=(OracleCallableStatement)con.prepareCall(procedure);
cstmt.setString(1,countryname); cstmt.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);//输出参数
cstmt.registerOutParameter(3,oracle.jdbc.OracleTypes.ARRAY,"LGN");
cstmt.registerOutParameter(4,oracle.jdbc.OracleTypes.ARRAY,"LGN");//换成 "traveler_assistance_package.LGN"也是不行
cstmt.execute();
//traveler_assistance_package.lgn");//,"VARCHAR2_TABLE");
String c_n=cstmt.getString(2);
ARRAY array = cstmt.getARRAY(3);
String[] splArray=(String[])array.getArray();ARRAY array1 = cstmt.getARRAY(4);
String[] oflArray=(String[])array1.getArray();----------------------------------------------------------------------这段java代码哪里错了啊,怎么修改啊~~~~(>_<)~~~~
高手帮帮忙吧~~
这个是不是用到数据库池了啊?
我们没有用到
我们写的这句话cstmt.registerOutParameter(3,oracle.jdbc.OracleTypes.ARRAY,"LGN");
不对吗 大侠帮看一下吧我们一开始定义了一个 table type的lng 然后帖子说java不识别table
然后就换成了object
create or replace type l_n_type as object
(
l_name nvarchar2(20)
)----在包外面定义的TYPE lgn IS table OF l_n_TYPE;----包内定义的
还是出错
java.sql.SQLException: 无效的名称模式: FOF.LGN
~~~~(>_<)~~~~
我的问题和下面这个帖子一样
http://topic.csdn.net/u/20080325/23/16c822ce-4a85-45a2-a092-e9509e58b884.html
但是我换成大写了还是不行啊?呜呜~~~
OracleCallableStatement cstmt=(OracleCallableStatement)con.prepareCall(procedure);
cstmt.setString(1,countryname);
ArrayDescriptor ad = ArrayDescriptor.createDescriptor("lgn", conn);
ARRAY lgn1= new ARRAY(ad, conn, new Sting[100]);
ARRAY lgn2= new ARRAY(ad, conn, new Sting[100]);
cstmt.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
cstmt.registerOutParameter(3,oracle.jdbc.OracleTypes.ARRAY,lgn1);
cstmt.registerOutParameter(4,oracle.jdbc.OracleTypes.ARRAY,lgn2);
cstmt.execute();
String c_n=cstmt.getString(2);
ARRAY array = cstmt.getARRAY(3);
String[] splArray=(String[])array.getArray();
ARRAY array1 = cstmt.getARRAY(4);
String[] oflArray=(String[])array1.getArray();
刚刚试过,报了如下错误。
An error occurred at line: 83 in the jsp file: /language.jsp
The method registerOutParameter(int, int, int) in the type CallableStatement is not applicable for the arguments (int, int, ARRAY)
80: ARRAY lgn2= new ARRAY(ad, con, new String[100]);
81: //
82: cstmt.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);//杈撳嚭鍙傛暟
83: cstmt.registerOutParameter(3,oracle.jdbc.OracleTypes.ARRAY,lgn1);//"TRAVERLER_ASSISTANCE_PACKEAGE.LGN");
84: cstmt.registerOutParameter(4,oracle.jdbc.OracleTypes.ARRAY,lgn2);//"TRAVERLER_ASSISTANCE_PACKEAGE.LGN");//,"NUMBER_TABLE");
85: cstmt.execute();
--------try it ,good luck
OracleCallableStatement cstmt=(OracleCallableStatement)con.prepareCall(procedure);
cstmt.setString(1,countryname);
ArrayDescriptor ad = ArrayDescriptor.createDescriptor("lgn", conn);
ARRAY lgn1= new ARRAY(ad, conn, new Sting[100]);
ARRAY lgn2= new ARRAY(ad, conn, new Sting[100]);
cstmt.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
cstmt.registerOutParameter(3,oracle.jdbc.OracleTypes.ARRAY);
cstmt.registerOutParameter(4,oracle.jdbc.OracleTypes.ARRAY);
cstmt.execute();
String c_n=cstmt.getString(2);
ARRAY lgn1= cstmt.getARRAY(3);
String[] splArray=(String[])lgn1.getArray();
ARRAY lgn2= cstmt.getARRAY(4);
String[] oflArray=(String[])lgn2.getArray();
大哥还是那个错误外加lgn1, lgn2重复定义了,然后我把
tring c_n=cstmt.getString(2);
ARRAY lgn1= cstmt.getARRAY(3);
String[] splArray=(String[])lgn1.getArray();
ARRAY lgn2= cstmt.getARRAY(4);
前面的两个ARRAY 去掉了,结果,运行出来的结果还是说 无效的名称模式
CREATE OR REPLACE TYPE USERNAME_ARRAY AS VARRAY(32) of varchar(32)CREATE OR REPLACE procedure make_logincard_pro (
p_cardsuitcode in varchar,
p_userseqidArr out USERSEQID_ARRAY ,
p_usernameArr out USERNAME_ARRAY
)
IS
v_addedtime date:= sysdate;
BEGIN
FOR ii IN 1 .. 10 LOOP
IF p_userseqidArr IS NULL THEN
p_userseqidArr := USERSEQID_ARRAY(ii);
ELSE
p_userseqidArr.EXTEND; --超过数组定义大小(50000)将抛出异常
p_userseqidArr(ii) := ii;
END IF; IF p_usernameArr IS NULL THEN
p_usernameArr := USERSEQID_ARRAY(ii || ''TT'');
ELSE
p_usernameArr.EXTEND; --超过数组定义大小(32)将抛出异常
p_usernameArr(ii) := ii || ''TT'';
END IF;
END LOOPEND make_logincard_pro ;
JAVA调用存储过程:
Connection con = session.connection();
java.sql.CallableStatement cst = con
prepareCall("call CNBT.test_pro(?,?,?)");
cst.setString(1, cardSuitCode);
cst.registerOutParameter(2, OracleTypes.ARRAY,"USERSEQID_ARRAY");
cst.registerOutParameter(3, OracleTypes.ARRAY,"USERNAME_ARRAY");java.sql.Array userSeqIdArr = cst.getArray(2);
java.sql.Array userNameArr = cst.getArray(3);--------给你参考下我认为应该是is table of不能当array使用,我明天查下java和oracle的api看看。
大哥谢谢O(∩_∩)O哈哈~
解决了,就是参照你最后发的这个帖子
我们申明了一个全局Type ,然后就解决了~
但怎么又
java.sql.SQLException: 索引中丢失 IN 或 OUT 参数:: 3