| Returning resultsets [message #7902] |
Wed, 16 July 2003 07:57  |
Naveen
Messages: 75
Registered: June 2001
|
Member |
|
|
Hi,
I have a typical problem. I have to return entire row to the fron end(Java, JSP, Struts) in a stored procedure. The row itself comprises of columns from two or more tables. My problem is that our application server(JRun 3.1) does not support refcursor, record types. It supports only varray but that too one cannot set any value but can only get value from the varray. So, please advice how should i return the result set to the fron end in a stored procedure. Sample code helps a lot.
Thank you
Naveen
|
|
|
|
| Re: Returning resultsets [message #7908 is a reply to message #7902] |
Wed, 16 July 2003 14:51  |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
You can return a result set as PL/SQL table(s)
SQL> INSERT INTO T (id) SELECT rownum FROM USER_TABLES
2 WHERE rownum<11;
10 rows created.
SQL> create or replace package vv
2 AS
3 TYPE my_type IS TABLE OF t%ROWTYPE
4 INDEX BY BINARY_INTEGER;
5 o_id my_type;
6
7 PROCEDURE value_outside(o_id OUT my_type);
8 END;
9 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY vv
2
3 AS
4
5 PROCEDURE value_outside (o_id OUT my_type)
6 IS
7
8 TYPE cur_test IS REF CURSOR;
9 r_test cur_test;
10 i NUMBER:=1;
11 BEGIN
12
13 OPEN r_test FOR SELECT *FROM t;
14 LOOP
15 EXIT WHEN r_test%NOTFOUND;
16 FETCH r_test INTO o_id(i);
17 i:=i+1;
18 END LOOP;
19 CLOSE r_test;
20
21 END;
22
23 END;
24 /
Package body created.
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 varid vv.my_type;
3 varcnt NUMBER;
4 BEGIN
5 vv.value_outside(varid);
6 varcnt:=varid.COUNT;
7 DBMS_OUTPUT.PUT_LINE (varcnt);
8 FOR i IN 1.. varcnt
9 LOOP
10 DBMS_OUTPUT.PUT_LINE (varid(i).id);
11 END LOOP;
12 END;
13 /
10
1
2
3
4
5
6
7
8
9
10
PL/SQL procedure successfully completed.
|
|
|
|
| Re: Returning resultsets [message #7918 is a reply to message #7908] |
Wed, 16 July 2003 23:18 |
Naveen
Messages: 75
Registered: June 2001
|
Member |
|
|
Hi,
But neither JDBC drivers nor JRun supports pl/sql table datatype. How should i return the result set in this situation. Should i just ask the front end guys to use select statements instead of procedures or is there any other way.
Thank you
Naveen
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
