| returning multiple rows in an OUT parameter [message #120202] |
Wed, 18 May 2005 07:10  |
kieranl
Messages: 6
Registered: May 2005
|
Junior Member |
|
|
I have a simple procedure that selects multiple columns from all the rows in a table, and then needs to return all these rows
The problem being...is there any way to output these rows in one call, using an OUT parameter?
i had thought about using collections, but it looks like they can only return a single column in the array (is this correct?)
or is there a way to output via a DBMS_OUTPUT.PUT_LINE where we could loop through a cursor that performs the select on the table (we're using JDBC)
|
|
|
|
| Re: returning multiple rows in an OUT parameter [message #120208 is a reply to message #120202] |
Wed, 18 May 2005 07:37  |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
There are several ways.
One (if you return only one row) is to use RECORD type for
OUT parameter:
SQL> create procedure get_row(ename in emp.ename%type, rw out emp%rowtype)
2 is
3 begin
4 select * into rw from emp where ename = get_row.ename;
5 end;
6 /
Procedure created.
SQL> declare
2 row emp%rowtype;
3 begin
4 get_row('KING',row);
5 dbms_output.put_line(row.ename || ' has id = ' || row.empno);
6 end;
7 /
KING has id = 7839
PL/SQL procedure successfully completed.
If you are going to return many rows, you can use REF CURSOR:
SQL> create procedure get_rc (ename in emp.ename%type, rc out sys_refcursor)
2 is
3 begin
4 open rc for 'select * from emp where ename = :1' using ename;
5 end;
6 /
Procedure created.
SQL> declare
2 row emp%rowtype;
3 rc sys_refcursor;
4 begin
5 get_rc('KING',rc);
6
7 fetch rc into row;
8 while (rc%found) loop
9 dbms_output.put_line(row.ename || ' has id = ' || row.empno);
10 fetch rc into row;
11 end loop;
12
13 close rc;
14 end;
15 /
KING has id = 7839
PL/SQL procedure successfully completed.
or table of records (collections can contain not only one
column but records also):
SQL> create or replace package tab_pkg
2 is
3 type row_tab is table of emp%rowtype index by binary_integer;
4 procedure get_recs(ename in emp.ename%type, rws out nocopy row_tab);
5 end;
6 /
Package created.
SQL> create or replace package body tab_pkg
2 is
3 procedure get_recs(ename in emp.ename%type, rws out nocopy row_tab)
4 is
5 begin
6 select * bulk collect into rws from emp where ename = get_recs.ename;
7 end;
8 end;
9 /
Package body created.
SQL> declare
2 etab tab_pkg.row_tab;
3 begin
4 tab_pkg.get_recs('KING',etab);
5 for i in 1..etab.count loop
6 dbms_output.put_line(etab(i).ename || ' has id = ' || etab(i).empno);
7 end loop;
8 end;
9 /
KING has id = 7839
PL/SQL procedure successfully completed.
DBMS_OUTPUT can't be used for output in Java.
Rgds.
|
|
|
|
| Re: returning multiple rows in an OUT parameter [message #120264 is a reply to message #120208] |
Wed, 18 May 2005 11:28 |
kieranl
Messages: 6
Registered: May 2005
|
Junior Member |
|
|
thanks for that, looks like REF CURSOR is the one I need to use, one more question though:
If using REF CURSOR with your example (with the restriction removed):
SQL> create procedure get_rc (ename in emp.ename%type, rc out sys_refcursor)
2 is
3 begin
4 open rc for 'select * from emp;
5 end;
6 /
without too much detail being needed, how would this be procedure be called in java? i.e. because the out parameter is a REF CURSOR, should there be any special treatment of this?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
