Solved

Dynamic Refcursor Row Type

Posted on 2001-09-16
9
3,265 Views
Last Modified: 2012-05-04
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

0
Comment
Question by:Ravindra76
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 7

Author Comment

by:Ravindra76
ID: 6486739
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....




0
 
LVL 27

Expert Comment

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

meikl
0
 

Accepted Solution

by:
m_eglit earned 200 total points
ID: 6487019
You can simply replace procedure to function.. ina this case it' looks like this :

CREATE OR REPLCAE PACKAGE SOME_PKG AS

  -- Type definitions
  TYPE rt_weak IS REF CURSOR;

  FUNCTION retr(param in valrchar2)
      RETURN rt_weak;
END;
/
CREATE OR REPLACE PACKAGE BODY ADDRESSBOOK_PKG AS

  FUNCTION retr(param in valrchar2)
      RETURN rt_weak AS

      l_return rt_weak;
      BEGIN
      OPEN l_return FOR SELECT * FROM blablabla;
      RETURN l_return;
  END;
END;
/


0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Expert Comment

by:m_eglit
ID: 6487024
sorry, there is a error in PACKAGE BODY name, of course it must be  SOME_PKG... :)))
0
 
LVL 7

Author Comment

by:Ravindra76
ID: 6487193

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?


0
 

Expert Comment

by:m_eglit
ID: 6487236
hmmmmm

l_query: = 'select * from blablabal'

open l_return for l_query; -- l_return REFCUR type

or I missunderstand something.....
0
 
LVL 7

Author Comment

by:Ravindra76
ID: 6487604
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.........
0
 
LVL 7

Author Comment

by:Ravindra76
ID: 6542838
Finally i ruled out the option of Week ref-cursor........

Dynamic SQL is helpful for me....

0
 
LVL 7

Author Comment

by:Ravindra76
ID: 6542841
deleting this question is not good
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question