| using ref cursor and for .. loop [message #37516] |
Tue, 12 February 2002 18:26  |
diaz
Messages: 58
Registered: October 2001
|
Member |
|
|
DECLARE
type sqlcur is REF cursor;
sqlst varchar(200);
c1 sqlcur;
val varchar2(20);
--
function rtn_cursor (sqlstmt varchar) return types.sqlcur as
mycursor types.sqlcur;
begin
open mycursor for sqlstmt;
return mycursor;
end;
--
BEGIN
sqlst := 'select * from test';
c1 := rtn_cursor(sqlst);
for j in c1 loop
dbms_output.put_line(j.id);
dbms_output.put_line(j.nama);
end loop;
END;
/
this ain't working :
ERROR at line 1:
ORA-06550: line 29, column 10:
PLS-00221: 'C1' is not a procedure or is undefined
ORA-06550: line 29, column 1:
PL/SQL: Statement ignored
i believe that is because if the cursor is already open so, it has to be fetch
not do for .. loop
but i want to ignore using fetch because if i want to do : select * i should have many variables to catch the fetched result ..
so.....
can somebody give solutions..
thanks
|
|
|
|
| Re: using ref cursor and for .. loop [message #37525 is a reply to message #37516] |
Wed, 13 February 2002 01:20  |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
try this..it uses scott.emp table
DECLARE
type sqlcur is REF cursor;
sqlst varchar(200);
c1 sqlcur;
val varchar2(20);
tt emp%rowtype;
--
function rtn_cursor (sqlstmt varchar) return sqlcur as
mycursor sqlcur;
begin
open mycursor for sqlstmt;
return mycursor;
end;
--
BEGIN
sqlst := 'select * from emp';
c1 := rtn_cursor(sqlst);
LOOP
FETCH c1 INTO tt;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.put_line (tt.ename);
DBMS_OUTPUT.put_line (tt.sal);
END LOOP;
CLOSE c1;
END;
/
|
|
|
|
|
|
| Re: using ref cursor and for .. loop [message #38838 is a reply to message #37525] |
Mon, 20 May 2002 03:54 |
Bruno
Messages: 12
Registered: May 2002
|
Junior Member |
|
|
BUT!!
in this way i can only write the contains of the cursor.
My problem is that i want to return the hole cursor, and i have to catch it with LiveWire (ssjs). The catching is not a problem. But i have always the same problem. If a define a type with the table%rowtype i can not make an dynamic sql with string concatenation. If i do not define the type i recieve the cursor empty. Oracle says also that the cursor.rowcount returns zero.
Can someone helps me?
please.
Bruno
PD: Excuse my english. i'm sure there's a lot of mistakes... but, it's not my language. ;-)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
