Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Dynamic Refcursor Row Type

Posted on 2001-09-16
9
Medium Priority
?
3,330 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 600 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

722 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