oracle cursor and ref cursor

Posted on 2012-08-16
Medium Priority
Last Modified: 2012-08-16
I have a cursor in Package A

cursor c1
select * from emp;

now what I want to do is pass this curosr in package A into a function
that will convert the curor into a ref curosor.

function convert_crsto_ref
            (y   Packagea.c1) return sys_refcursor;

any clues on how i can do this??

the actual cursor is huge and very complex.
Question by:jhacharya
  • 3
  • 2
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38301543
c1 is already a cursor.  turning it into a ref_cursor, is still a cursor.

I'm not understanding what you think this is going to do for you.

From what you posted:  you want to open c1, call a function with it that basically returns c1 back to you?
LVL 23

Accepted Solution

paquicuba earned 2000 total points
ID: 38302728
Make your cursor in the package a constant string and then convert it to a ref cursor and back to a cursor, see the example below:

create or replace package dropme_pkg is
sql_stmt clob := q'[select dummy from dual union all select 'Y' from dual]';
create or replace function dropme_func return sys_refcursor is
curid number := DBMS_SQL.OPEN_CURSOR;
curex pls_integer;
DBMS_SQL.PARSE(curid, dropme_pkg.sql_stmt, DBMS_SQL.NATIVE);
curex := DBMS_SQL.EXECUTE(curid);
return DBMS_SQL.TO_REFCURSOR(curid);
select dropme_func from dual
cur         pls_integer;
l_cols      pls_integer;
l_desc      DBMS_SQL.DESC_TAB;
refcur      sys_refcursor;
refcur := dropme_func;
DBMS_SQL.DESCRIBE_COLUMNS(cur, l_cols, l_desc);
FOR i IN 1 .. l_cols LOOP
        DBMS_OUTPUT.PUT('Column ' || i || ': ' || RPAD(l_desc(i).col_name,10));
        DBMS_OUTPUT.PUT('; Type: ' || CASE l_desc(i).col_type
                                         WHEN 1
                                         THEN 'VARCHAR2'
                                         WHEN 2
                                         THEN 'NUMBER'
                                         WHEN 12
                                         THEN 'DATE'
                                         ELSE 'MANY OTHERS NOT IN THIS SIMPLE EXAMPLE...'
     END LOOP;
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38302882
You can do it without dbms_sql.  I just don't see the need to do it.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 23

Expert Comment

ID: 38303178
You can do it without dbms_sql.  I just don't see the need to do it.
I agree, but since that's what the author wants...

Author Comment

ID: 38303222
The reason is that different groups are using the cursor ,
The dev group is developing and using he cursor in their procs,
The qa group would use that same cursor in reports and some front end procs
I need the 1st /2nd line support folks to be able to just run the cursor and get the results to determine actions to take to resolve problems that may occur,

So I need to keep the cursor consistent say in package a

The qa would use the generic function to select the appropriate cursor to pass back to their programs

The support folks could just run a simple select functionReturnCursor(cur a)from dual to interperet the results.

The dev team does not want to re code the cursors into strings, because they declare record types based on the cursor by cur/row type for speed.

So I am trying to find a way that would meet everyone's needs ...

Hence my idea of just having a function that would allow you to pass the cursor to a function so you could do a select from dual
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38303226
Again, a cursor and ref cursor are the same thing.  If you have the cursor to pass to a function, just use it.  What pass it into a function to just have it passed back.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
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 video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

831 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