Ravindra76
asked on
Dynamic Refcursor Row Type
Hi :-
I was able to open a refcursor without mentioning the ROWTYPE statically ( at compile time ...). But for fetching, i have to define the ROWTYPE. But for me that ROWTYPE should be dynamic . ( I mean i will pass different querys for a common refcursor type and i have to fetch the row dynamically........)
Is it possible with either static SQL OR Dynamic SQl ??
The code is as follows
-------------------------- ---
Package Head
-------------------------- ---
create or replace package test_head as
type my_choice is ref cursor;
procedure get_rec(out_cur out my_choice);
procedure test(in_my_cur in my_choice);
end test_head;
/
show errors;
-------------------------- --------
Packagd body
-------------------------- --------
create or replace package body test_head as
procedure get_rec(out_cur out my_choice)
is
begin
open out_cur for
select f_f1,f_f2 t_table;
end get_rec;
-------------------------- ---------- ---------- ----------
procedure test(in_my_cur in my_choice)
is
rec my_choice%ROWTYPE; /* This is the error area. If I am able to define this one dynamically, it will solve my problem. ....... PLS SUGGEST SOME THING HERE*/
begin
loop
fetch in_my_cur into rec;
exit when in_my_cur%notfound;
dbms_output.put_line(rec.f _f1);
/* NOTE. Even this filed shoud be parameter to this procedure */
end loop;
close in_my_cur;
null;
end test;
-------------------------- ---------- ---
end test_head;
/
show errors;
-------------------------- ---------- ---------- --
Error is
-------------------------- ---------- ---------- -----
LINE/COL ERROR
-------- -------------------------- ---------- ---------- ---------- ---------
12/6 PLS-00310: with %ROWTYPE attribute, 'MY_CHOICE' must name a
table, cursor or cursor-variable
12/6 PL/SQL: Item ignored
15/4 PL/SQL: SQL Statement ignored
15/25 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
17/5 PL/SQL: Statement ignored
17/26 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
Advanced thanks
I was able to open a refcursor without mentioning the ROWTYPE statically ( at compile time ...). But for fetching, i have to define the ROWTYPE. But for me that ROWTYPE should be dynamic . ( I mean i will pass different querys for a common refcursor type and i have to fetch the row dynamically........)
Is it possible with either static SQL OR Dynamic SQl ??
The code is as follows
--------------------------
Package Head
--------------------------
create or replace package test_head as
type my_choice is ref cursor;
procedure get_rec(out_cur out my_choice);
procedure test(in_my_cur in my_choice);
end test_head;
/
show errors;
--------------------------
Packagd body
--------------------------
create or replace package body test_head as
procedure get_rec(out_cur out my_choice)
is
begin
open out_cur for
select f_f1,f_f2 t_table;
end get_rec;
--------------------------
procedure test(in_my_cur in my_choice)
is
rec my_choice%ROWTYPE; /* This is the error area. If I am able to define this one dynamically, it will solve my problem. ....... PLS SUGGEST SOME THING HERE*/
begin
loop
fetch in_my_cur into rec;
exit when in_my_cur%notfound;
dbms_output.put_line(rec.f
/* NOTE. Even this filed shoud be parameter to this procedure */
end loop;
close in_my_cur;
null;
end test;
--------------------------
end test_head;
/
show errors;
--------------------------
Error is
--------------------------
LINE/COL ERROR
-------- --------------------------
12/6 PLS-00310: with %ROWTYPE attribute, 'MY_CHOICE' must name a
table, cursor or cursor-variable
12/6 PL/SQL: Item ignored
15/4 PL/SQL: SQL Statement ignored
15/25 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
17/5 PL/SQL: Statement ignored
17/26 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
Advanced thanks
well,
also oracle cannot look into the future and
is therefore not able to provide structures,
which are not defined before.
meikl
also oracle cannot look into the future and
is therefore not able to provide structures,
which are not defined before.
meikl
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sorry, there is a error in PACKAGE BODY name, of course it must be SOME_PKG... :)))
ASKER
NOT WOrking Statement (IF it works,my problem will be solved)
--------------------------
query :='fetch in_my_cur into s1,s2,s3;';
loop
DBMS_SQL.PARSE(cid, '' || query, dbms_sql.v7);
exit when in_my_cur%notfound;
--------------------------
Working statements
--------------------------
loop
fetch in_my_cur into s1,s2,s3;
exit when in_my_cur%notfound;
--------------------------
Why the DBMS_SQL.PARSE if failing by returning
fetch in_my_cur into s1,s2,s3;
ORA-00900: invalid SQL statement
And the same statement is working with out parsing?
any idea?
hmmmmm
l_query: = 'select * from blablabal'
open l_return for l_query; -- l_return REFCUR type
or I missunderstand something.....
l_query: = 'select * from blablabal'
open l_return for l_query; -- l_return REFCUR type
or I missunderstand something.....
ASKER
The problem is not with the function OR procedure.
It is the problem with Parsing.
DDL, DML statements are parsed with out any problem. But
"FETCH" statement is causing problem.....
My Final Goal: One cursor type ( weak ref cursor ) has to handle all processing from multiple procedures with different result set.........
It is the problem with Parsing.
DDL, DML statements are parsed with out any problem. But
"FETCH" statement is causing problem.....
My Final Goal: One cursor type ( weak ref cursor ) has to handle all processing from multiple procedures with different result set.........
ASKER
Finally i ruled out the option of Week ref-cursor........
Dynamic SQL is helpful for me....
Dynamic SQL is helpful for me....
ASKER
deleting this question is not good
ASKER
1. I am able to print the refcuror in get_rec procedure from the command prompt successfully even though i did not mention REFCURSOR row type in package_head.
That is the curse.........
But i can not declare the refcursor as
type my_choice is ref cursor return SOME_TABLE%ROWTYPE OR
type my_choice is ref cursor return SOME_RECORD_TYPE
If I declare like this, it is static binding at compile time and it won't server my purpose....