CREATE OR REPLACE PACKAGE ado_callpkg AS
TYPE usr IS TABLE of varchar2(15) INDEX BY BINARY_INTEGER;
TYPE pwd IS TABLE of VARCHAR2(10) INDEX BY BINARY_INTEGER;
PROCEDURE getEmpNames (user OUT usr,passwd OUT pwd);
end ado_callpkg;
/CREATE OR REPLACE PACKAGE BODY ado_callpkg AS
PROCEDURE getEmpNames (user OUT usr,passwd OUT pwd)
IS
CURSOR c1 IS select username, password from userinfo;
cnt NUMBER DEFAULT 1;
c c1%ROWTYPE; BEGIN
open c1;
LOOP
FETCH c1 INTO c;
user(cnt):=c.username;
passwd(cnt):=c.password;
EXIT WHEN c1%NOTFOUND; -- process the data
cnt :=cnt+1;
END LOOP;
close c1;
END;
end ado_callpkg;
/
java调用:
CallableStatement toesUp = conn.prepareCall("{call ado_callpkg.getEmpNames(?,?) }");
toesUp.registerOutParameter(1, Types.VARCHAR);
toesUp.registerOutParameter(2, Types.VARCHAR);
无论注册输出参数为什么,总是报错。要么类型不匹配,要么类型错误。
换作oracle的类型,还是出错:
toesUp.registerOutParameter(1, OracleTypes.VARCHAR);
toesUp.registerOutParameter(2, OracleTypes.VARCHAR);
请问,如何才能取得存储过程返回的数组值呢?
TYPE usr IS TABLE of varchar2(15) INDEX BY BINARY_INTEGER;
TYPE pwd IS TABLE of VARCHAR2(10) INDEX BY BINARY_INTEGER;
PROCEDURE getEmpNames (user OUT usr,passwd OUT pwd);
end ado_callpkg;
/CREATE OR REPLACE PACKAGE BODY ado_callpkg AS
PROCEDURE getEmpNames (user OUT usr,passwd OUT pwd)
IS
CURSOR c1 IS select username, password from userinfo;
cnt NUMBER DEFAULT 1;
c c1%ROWTYPE; BEGIN
open c1;
LOOP
FETCH c1 INTO c;
user(cnt):=c.username;
passwd(cnt):=c.password;
EXIT WHEN c1%NOTFOUND; -- process the data
cnt :=cnt+1;
END LOOP;
close c1;
END;
end ado_callpkg;
/
java调用:
CallableStatement toesUp = conn.prepareCall("{call ado_callpkg.getEmpNames(?,?) }");
toesUp.registerOutParameter(1, Types.VARCHAR);
toesUp.registerOutParameter(2, Types.VARCHAR);
无论注册输出参数为什么,总是报错。要么类型不匹配,要么类型错误。
换作oracle的类型,还是出错:
toesUp.registerOutParameter(1, OracleTypes.VARCHAR);
toesUp.registerOutParameter(2, OracleTypes.VARCHAR);
请问,如何才能取得存储过程返回的数组值呢?
CallableStatement cs = null; try {
connection = DataSourceApplication.getInstance().getConnection();
cs = connection.prepareCall("{call ado_callpkg.getEmpNames(?,?) }"); cs.setDate(1, list);
cs.execute(); log.warn("存储过程调用 ok");
} catch (SQLException e) {
e.printStackTrace();
throw new RunloanException(e.getMessage());
} catch (NamingException e) {
e.printStackTrace();
throw new RunloanException(e.getMessage());
} finally { try {
cs.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
} }
}
怀疑中,试了一下,编译都通不过呀。
兄弟帮帮忙,再帮忙看下呢?