Solved

Dynamic Refcursor Row Type

Posted on 2001-09-16
9
3,172 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
  • 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
 

Expert Comment

by:m_eglit
ID: 6487024
sorry, there is a error in PACKAGE BODY name, of course it must be  SOME_PKG... :)))
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now