CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/
create or replace procedure taskselect(ts_id in varchar2,p_rc out pkg_test.myrctype)
as
str varchar2(50);
begin
str:='select task_name,amount from tasks where task_id='''||ts_id||'''';
open p_rc for str;
end;
/declare
v_rc pkg_test.myrctype;
tn varchar(20);
tam number(10,0);
begin
taskselect('19980001',v_rc);
fetch v_rc into tn,tam;
loop
exit when v_rc%nofound;
dbms_output.put_line(tn||' '||tam);
fetch v_rc into tn,tam;
end loop;
end;
/
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/
create or replace procedure taskselect(ts_id in varchar2,p_rc out pkg_test.myrctype)
as
str varchar2(50);
begin
str:='select task_name,amount from tasks where task_id='''||ts_id||'''';
open p_rc for str;
end;
/declare
v_rc pkg_test.myrctype;
tn varchar(20);
tam number(10,0);
begin
taskselect('19980001',v_rc);
fetch v_rc into tn,tam;
loop
exit when v_rc%nofound;
dbms_output.put_line(tn||' '||tam);
fetch v_rc into tn,tam;
end loop;
end;
/
解决方案 »
- 变量绑定VARIABLE v_output_my integer出错,请求大家帮助。
- 满分相送,关于scheduler 创建的job不能自动运行的问题
- SQLException问题
- 统计语句问题?
- 急!!求助ORACLE中IN函数的问题!!
- Oracle Dblink查询速度慢
- exp导出的几个特殊要求,请大哥帮忙
- 关于oracle9体系结构方面的初级问题,分不够,另外开贴!问题见内!
- pro*c数据类型问题
- 表a有字段'学生id','科目','分数',怎么查出结果集如:'学生id','科目1分数','科目2分数'..'科目n分数'
- 一个相当难的问题,高手请进(欢迎在线讨论)
- Oricle9i能否装在win2k专业版下
但请问我那种方法有什么错误?
用你这种方法的话,怎样在java中得到这个返回到结果集?
若确定select task_name,amount into tn,tam from tasks where task_id=ts_id;只有一条记录,也可这样。create or replace procedure taskselect(ts_id in varchar2,tn out varchar2,tam out number)
as
begin
select task_name,amount into tn,tam from tasks where task_id=ts_id;
end;
/
declare
tn varchar(20);
tam number(10,0);
begin
taskselect('19980001',tn,tam);
dbms_output.put_line(tn||' '||tam);
end;
/
SQL> create package task_search
2 as
3 type ta_search is ref cursor;
4 procedure taskselect(ts_id varchar2,ts_sor out ta_search);
5 end task_search;
6 /程序包已创建。SQL> create package body task_search
2 as
3 procedure taskselect(ts_id varchar2,ts_sor out ta_search)
4 is
5 begin
6 open ts_sor for
7 select task_name,amount from tasks where task_id=ts_id;
8 end taskselect;
9 end task_search;
10 /程序包主体已创建。
创建的包和过程都成功,只是在java中调用用游标返回的结果集时出现错误,所以改用最上面的方法,返回的记录不止一条。
主要是请教如何在java中“调用”结果集的问题。
ResultSet rstSet = null;
rstSet = (ResultSet) call.getObject(3);call.getObject(2)为你要返回的结果CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/
create or replace procedure taskselect(ts_id in varchar2,p_rc out pkg_test.myrctype)
as
str varchar2(50);
begin
str:='select task_name,amount from tasks where task_id='''||ts_id||'''';
open p_rc for str;
end;
/
…………
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbc:odbc:datamy","system","pqh0720");
…………String procedure = "{?=call task_search.taskselect(?,?)}";
CallableStatement cstmt = conn.prepareCall(procedure);
cstmt.registerOutParameter(2,Types.OTHER);
cstmt.setString(1,searchTaskID);
cstmt.execute();
ResultSet rs=(ResultSet)cstmt.getObject(2);
while(rs.next()){
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
}
…………
但是还是错误,错误提示为
javax.servlet.ServletException: [Oracle][ODBC][Ora]ORA-24334: ????????
请问sky2003202,你调试成功了吗?能否帮我调试一下,这个问题困了我一个星期了,急呀!
->
String strSQL = "{call task_search.taskselect(?,?)}";
javax.servlet.ServletException: [Oracle][ODBC][Ora]ORA-06550: ? 1 ?, ? 7 ?:
PLS-00306: ?? 'taskselect' ??????????
ORA-06550: ? 1 ?, ? 7 ?:
PL/SQL: Statement ignored
我看过资料,好像一般有输出变量的话call前都要用?=的
我就是这么用的你看看你是不是别的地方还有错
CallableStatement cstmt = conn.prepareCall(procedure)这句你改了么?
和
cstmt= pConn.prepareCall(sqlStr);
好像没什么不同呀?
我改了试了还是不行,出现的错误和我上面说的一样
create package task_search
as
type ta_search is ref cursor;
procedure gettask(ta_id varchar2,ts_sor out ta_search);
end task_search;
程序包体为:
create package body task_search
as
procedure gettask(ta_id varchar2,ts_sor out ta_search)
is
begin
open ts_sor for
select task_name,amount from tasks where task_id=ts_id;
end gettask;
end task_search;
在jsp中的调用语句为:
…………
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbc:odbc:datamy","system","pqh0720");
…………
String sqlStr = "{call task_search.gettask(?,?)}";//按你的意思去掉了?=
CallableStatement cstmt = conn.prepareCall(sqlStr);
cstmt.registerOutParameter(2,Types.OTHER);
cstmt.setString(1,searchTaskID);//searchTaskID是从表单中提交的数据
cstmt.executeQuery();
ResultSet rs=null;
rs=(ResultSet)cstmt.getObject(2);
while(rs.next()){
System.out.println(rs.getString(1));
}
is
begin
open ts_sor for
'select task_name,amount from tasks where task_id = :ta_id'
using ta_id;
end gettask;
end task_search;
试了,还是不行!
我认为其实
open ts_sor for
'select task_name,amount from tasks where task_id = :ta_id';
using ta_id;
和
open ts_sor for
select task_name,amount from tasks where task_id=ts_id;
都是可以的。
究竟是什么地方出了问题呢?!
SQL> create or replace package task_search
2 as
3 type ta_search is ref cursor;
4 procedure gettask(ta_id varchar2,ts_sor out ta_search);
5 end task_search;
6 /程序包已创建。SQL> create or replace package body task_search
2 as
3 procedure gettask(ta_id varchar2,ts_sor out ta_search)
4 is
5 begin
6 open ts_sor for
7 'select task_name,amount from tasks where task_id = :ta_id'
8 using ta_id;
9 end gettask;
10 end task_search;
11 /程序包主体已创建。
javax.servlet.ServletException: [Oracle][ODBC][Ora]ORA-24334: ????????
我在想是不是在java中调用游标时出现的问题。因为我看到一般的JDBC Driver是用的oracle.driver,因此在注册输出变量时用的是
cstmt.registerOutParameter(2,OracleTypes.CURSOR);
在返回结果集时用的是
rs=(ResultSet)cstmt.getCursor(2);
而我用的JDBC Driver是jdbc-odbc桥,所以在注册输出变量时用的是
cstmt.registerOutParameter(2,Types.OTHER);
在返回结果集时用的是
rs=(ResultSet)cstmt.getObject(2);
不知道这个地方有没有问题?
String procedure = "{?=call task_search.taskselect(?,?)}";
象sky2003202说的将这句前面的?=去掉即可