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

Ravindra76

ASKER

NOTE:

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....

kretzschmar

well,
also oracle cannot look into the future and
is therefore not able to provide structures,
which are not defined before.

meikl

ASKER CERTIFIED SOLUTION

m_eglit

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

m_eglit

sorry, there is a error in PACKAGE BODY name, of course it must be SOME_PKG... :)))

Ravindra76

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?

m_eglit

hmmmmm

l_query: = 'select * from blablabal'

open l_return for l_query; -- l_return REFCUR type

or I missunderstand something.....

Ravindra76

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.........

Ravindra76

ASKER

Finally i ruled out the option of Week ref-cursor........

Dynamic SQL is helpful for me....

Ravindra76

ASKER

deleting this question is not good