[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3423
  • Last Modified:

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

0
Ravindra76
Asked:
Ravindra76
  • 5
  • 3
1 Solution
 
Ravindra76Author Commented:
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
 
kretzschmarCommented:
well,
also oracle cannot look into the future and
is therefore not able to provide structures,
which are not defined before.

meikl
0
 
m_eglitCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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

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
 
m_eglitCommented:
hmmmmm

l_query: = 'select * from blablabal'

open l_return for l_query; -- l_return REFCUR type

or I missunderstand something.....
0
 
Ravindra76Author Commented:
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
 
Ravindra76Author Commented:
Finally i ruled out the option of Week ref-cursor........

Dynamic SQL is helpful for me....

0
 
Ravindra76Author Commented:
deleting this question is not good
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now