oracle cursor and ref cursor

Posted on 2012-08-16
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
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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

    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;
    cur := DBMS_SQL.TO_CURSOR_NUMBER(refcur);
    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 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    You can do it without dbms_sql.  I just don't see the need to do it.
    LVL 23

    Expert Comment

    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...
    LVL 2

    Author Comment

    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 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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

    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

    Title # Comments Views Activity
    Oracle Standby Databases 3 40
    oracle 10g table containing BLOBS 3 33
    Add 0 to end of Number 21 58
    Oracle Database creation fails 5 19
    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    754 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

    21 Experts available now in Live!

    Get 1:1 Help Now