Skip to Main Content
  • Questions

  • SYS_REFCURSOR and FETCH with multi-table cursor

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Vance.

Asked: January 07, 2011 - 1:29 am UTC

Last updated: May 26, 2022 - 1:21 pm UTC

Version: 11.1.0

Viewed 100K+ times! This question is

You Asked

Hello Tom!

We have a situation where we are using Java to access data in an Oracle database. Some of the objects are broken out into three tables as follows:

* Primary table (e.g. MY_OBJECT)
* Secondary table (e.g. MY_OBJECT_2)
* Custom table (e.g. MY_OBJECT_C)

The primary table contains core attributes that are frequently accessed for life cycle management, critical state information, etc. The secondary table contains core attributes that are infrequently accessed. The custom table contains attributes that are customer-specific. (This is a COTS product that is being developed.)

We have a stored procedure with an output reference cursor parameter. Originally, it was written to only return the data in the primary table, such as the following:

    CREATE OR REPLACE PROCEDURE my_procedure
    (
        ...
        ...
        o_result_set OUT SYS_REFCURSOR
    ) AS
    
    BEGIN
    
        -- Initialize the output
        o_result_set := NULL;
    
        ...
        ...
    
        OPEN o_result_set FOR
            SELECT
                *
            FROM
                my_object
            WHERE
                ...
                ...;
    END;


The unit test for this procedure used a %ROWTYPE designator for the variable to fetch the result sets into such as the following:

DECLARE

    v_my_object_records SYS_REFCURSOR;
    v_my_object_record my_object%ROWTYPE;
    
BEGIN

    -- Call the procedure
    my_procedure(
        v_my_object_records
    );
    
    -- Loop through the resulting data
    IF (v_my_object_records IS NOT NULL)
    THEN
        LOOP
            FETCH v_my_object_records INTO v_my_object_record;
            EXIT WHEN v_my_object_records%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE('*    ' || v_my_object_record.object_id);
        END LOOP;
        CLOSE v_my_object_records;
    END IF;
END;


This works great. As you can see, the result set is fetched into a variable defined as type my_object%ROWTYPE.

However, the need arose for returning one of the columns in the secondary table. Since this is a COTS framework, and the custom attributes (columns) are not known at design time of the stored procedure, we decided to extend the stored procedure to also return the secondary and custom attributes in a manner such as follows:

    CREATE OR REPLACE PROCEDURE my_procedure
    (
        ...
        ...
        o_result_set OUT SYS_REFCURSOR
    ) AS
    
    BEGIN
    
        -- Initialize the output
        o_result_set := NULL;
    
        ...
        ...
    
        OPEN o_result_set FOR
            SELECT
                m.*, m2.*, mc.*
            FROM
                my_object m
            INNER JOIN
                my_object_2 m2
            ON
                m.object_id = m2.object_id
            INNER JOIN
                my_object_c mc
            ON
                m.object_id = mc.object_id
            WHERE
                ...
                ...;
    END;


This procedure compiles fine and even appears to work fine in the Java code. However, we are not able to produce a PL/SQL unit test for this because there is no clean "ROWTYPE" to define for the output in this instance, since the records do not correspond to a row in a table.


Question: Is there a way to process this kind of result set variable within PL/SQL?


I much prefer to use PL/SQL for the unit test of the stored procedure.

Note that we cannot define a data type with a composite of the table columns and rely on this for the product, since this is a framework and the custom attributes are not known at design time. We did try to create one just for unit test purposes like the following:

DECLARE

    TYPE my_object_record_type IS RECORD (
        my_object_field_1 ...,
        my_object_field_2 ...,
        ...
        my_object_2_field_1 ...,
        my_object_2_field_2 ...,
        ...
        my_object_c_field_1 ...,
        my_object_c_field_2 ...,
        ...
    );

    TYPE my_object_ref_cursor_type IS REF CURSOR return my_object_record_type;

    v_my_object_records my_object_ref_cursor_type;
    v_my_object_record my_object_record_type;
    
BEGIN

    -- Call the procedure
    my_procedure(
        v_my_object_records
    );
    
    -- Loop through the resulting data
    IF (v_my_object_records IS NOT NULL)
    THEN
        LOOP
            FETCH v_my_object_records INTO v_my_object_record;
            EXIT WHEN v_my_object_records%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE('*    ' || v_my_object_record.object_id);
        END LOOP;
        CLOSE v_my_object_records;
    END IF;
END;


However, when we attempt to fetch data into a variable based on this data type we get the following error:

    ORA-06504: PL/SQL: Return types of Result Set variables or query do not match


There must be a way to do this. Thanks in advance any help you can provide. I have scoured the Internet and could not find anything close to this. My apologies in advance if this is dealt with elsewhere.

and Tom said...

Question: Is there a way to process this kind of result set variable within PL/SQL?


answer: yes, in 11g.

ops$tkyte%ORA11GR2> declare
  2          l_rcursor sys_refcursor;
  3          l_colCnt number;
  4          l_descTbl dbms_sql.desc_tab;
  5  begin
  6          open l_rcursor for select * from all_users;
  7  
  8          dbms_sql.describe_columns
  9          ( c       => dbms_sql.to_cursor_number(l_rcursor),
 10            col_cnt => l_colCnt,
 11            desc_t  => l_descTbl );
 12  
 13          for i in 1 .. l_colCnt
 14          loop
 15                  dbms_output.put_line( l_descTbl(i).col_name );
 16          end loop;
 17  end;
 18  /
USERNAME
USER_ID
CREATED

PL/SQL procedure successfully completed.



You can use dbms_sql.to_cursor_number on a ref cursor to convert it into a dbms_sql cursor type and then use dbms_sql to process it. You can describe the query, fetch from it, get the i'th column, etc.


Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Vance Duncan, January 07, 2011 - 5:10 pm UTC 

Wow! Never would have figured that out.

I think I'm getting closer, but now I'm getting another error with the attempted follow on. I suppose I'm doing something wrong, but I can't seem to figure out what. I'm certainly pulling out my hair...

The stored procedure was untouched. I left the "OPEN result_set FOR..." statement just as it was and modified the unit test script as follows:

<code>
DECLARE

    v_my_object_records SYS_REFCURSOR;

    v_cursor_number INTEGER;
    v_desc_tab DBMS_SQL.DESC_TAB;
    v_column_count INTEGER;

    v_temp_name VARCHAR2(30);
    v_temp_object_id VARCHAR2(64);
    v_temp_timestamp TIMESTAMP;

    -- Call the procedure
    my_procedure(
        v_my_object_records
    );

    -- Loop through the result set
    IF (v_my_object_records IS NOT NULL)
    THEN
        v_cursor_number := DBMS_SQL.TO_CURSOR_NUMBER(v_execution_records);
        DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_number, v_column_count, v_desc_tab);
        WHILE (DBMS_SQL.FETCH_ROWS(v_cursor_number) > 0)
        LOOP
            DBMS_OUTPUT.PUT('*   ');
            FOR i IN 1..v_column_count
            LOOP
                v_temp_name := v_desc_tab(i).col_name;
                IF (v_temp_name = 'OBJECT_ID')
                THEN
                    DBMS_SQL.COLUMN_VALUE(v_cursor_number, i, v_temp_object_id);
                    DBMS_OUTPUT.PUT(' ' || v_temp_name || '=' || v_temp_object_id);
                ELSIF (v_temp_name = 'TRANSACTION_TIME')
                THEN
                    DBMS_SQL.COLUMN_VALUE(v_cursor_number, i, v_temp_timestamp);
                    DBMS_OUTPUT.PUT(' ' || v_temp_name || '=' || NVL(TO_CHAR(v_temp_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
                END IF;
            END LOOP;
            DBMS_OUTPUT.PUT_LINE('');
        END LOOP;
        DBMS_SQL.CLOSE_CURSOR(v_cursor_number);
    END IF;


The DBMS_SQL.TO_CURSOR_NUMBER and DBMS_SQL.DESCRIBE_COLUMNS calls are working fine. I put a loop to print out the column names, so I can see the structure is correct. However, when I attempt to execute DBMS_SQL.FETCH_ROWS I get the following error:

    ORA-01007: variable not in select list
    ORA-06512: at "SYS.DBMS_SQL", line 1530
    ORA-06512: at line 84
    ORA-06512: at line 272


Is there something else I need to do to get the cursor ready for processing? What am I missing?

Thanks again for your help.</code>
Tom Kyte
January 08, 2011 - 12:33 pm UTC

I quickly hacked my "print_table" routine to do most of this - not 100% tested, error handling is non-existent, etc - but it shows the process

Look at the view text for DBA_TAB_COLS for the meaning of the types (the numbers)

ops$tkyte%ORA11GR2> create or replace
  2  procedure print_ref_cursor
  3  ( p_query in out sys_refcursor,
  4    p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss' )
  5  is
  6      l_theCursor     integer;
  7      l_columnValue   varchar2(4000);
  8      l_descTbl       dbms_sql.desc_tab2;
  9      l_colCnt        number;
 10      l_date          date;
 11  begin
 12      l_theCursor := dbms_sql.to_cursor_number( p_query );
 13      dbms_sql.describe_columns2
 14      ( l_theCursor, l_colCnt, l_descTbl );
 15  
 16      -- define all columns to be cast to varchar2's, we
 17      -- are just printing them out
 18      for i in 1 .. l_colCnt loop
 19          if ( l_descTbl(i).col_type in ( 12, 178, 179, 180, 181, 231 ) )
 20          then
 21              dbms_sql.define_column
 22              (l_theCursor, i, l_date );
 23          else
 24              dbms_sql.define_column
 25              (l_theCursor, i, l_columnValue, 4000);
 26          end if;
 27      end loop;
 28  
 29      while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
 30      loop
 31          for i in 1 .. l_colCnt loop
 32              if ( l_descTbl(i).col_type in ( 12, 178, 179, 180, 181, 231 ) )
 33              then
 34                  dbms_sql.column_value( l_theCursor, i, l_date );
 35                  l_columnValue := to_char( l_date, p_date_fmt );
 36              else
 37                  dbms_sql.column_value
 38                  ( l_theCursor, i, l_columnValue );
 39              end if;
 40               dbms_output.put_line
 41                 ( rpad( l_descTbl(i).col_schema_name || '.' ||
 42                  l_descTbl(i).col_name, 30 ) || ': ' || l_columnValue );
 43          end loop;
 44          dbms_output.put_line( '-----------------' );
 45      end loop;
 46      dbms_sql.close_cursor( l_theCursor );
 47  end;
 48  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2      l_cursor sys_refcursor;
  3  begin
  4      open l_cursor for select * from all_users where rownum = 1;
  5      print_ref_cursor( l_cursor );
  6      dbms_output.put_line( rpad('*',72,'*'));
  7      open l_cursor for select * from all_objects where rownum = 1;
  8      print_ref_cursor( l_cursor );
  9  end;
 10  /
.USERNAME                     : SYS
.USER_ID                      : 0
.CREATED                      : 05-sep-2010 15:40:00
-----------------
************************************************************************
.OWNER                        : SYS
.OBJECT_NAME                  : ICOL$
.SUBOBJECT_NAME               :
.OBJECT_ID                    : 20
.DATA_OBJECT_ID               : 2
.OBJECT_TYPE                  : TABLE
.CREATED                      : 05-sep-2010 15:39:55
.LAST_DDL_TIME                : 05-sep-2010 15:47:41
.TIMESTAMP                    : 2010-09-05:15:39:55
.STATUS                       : VALID
.TEMPORARY                    : N
.GENERATED                    : N
.SECONDARY                    : N
.NAMESPACE                    : 1
.EDITION_NAME                 :
-----------------

PL/SQL procedure successfully completed.

Vance Duncan, January 08, 2011 - 11:49 pm UTC

I really appreciate your time helping me out with this, Tom. I hope Oracle appreciates you as well. They certainly should...

I looked in DBA_TAB_COLS and don't see anything that appears to provide a numeric column type value. I checked the Internet and found some references to DBMS_TYPES, which does appear to contain some of the values you indicated but not all. I also found a horribly complicated query on the internet that joined a bunch of SYS$xxx tables, pulling the numeric value from one of them and using a DECODE to convert the numeric value to a data type name. But I am yet to find a definition for these anywhere.

In any event, I decided to just use the values you provided and give this a shot. The first problem I had was with my TIMESTAMP fields. When I tried to map the col_type values 12, 178, 179, 180, 181, 231 to a DATE variable I got the following error:

ERROR at line 1:
ORA-06562: type of out argument must match type of column or bind variable
ORA-06512: at "SYS.DBMS_SQL", line 2058
ORA-06512: at line 109
ORA-06512: at line 514


So I decided to use TIMESTAMP for the same column type values instead. That got rid of the above error.

I was finally able to get a unit test working that calls the procedure exactly once. However, the unit test needs to make repeated calls to the stored procedure to step through data in some of the tables of the application. It's simulating a pagination of data in the tables for a user interface. I found that repeated calls using this technique cause the following error after the 2nd attempt:

ERROR at line 1:
ORA-21779: duration not active


Charming.

If I subsequently attempt to run the unit test script again while still in SQL*Plus, I get errors like the following:

ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5792
Session ID: 137 Serial number: 2320


ERROR:
ORA-03114: not connected to ORACLE


As you can imagine, I had no idea what this could be. So I started putting debug statements all over the place. Interestingly enough, the error occurred in a portion of the code nowhere related to the DBMS_SQL package calls.

So I thought that maybe this was some "out there" situation due to the fact that I was using the "SELECT e.*, e2.*, ec.*" technique. I decided to change the stored procedure to use only "SELECT e.*" and then I could put together two unit tests: 1 using the DBMS_SQL stuff and another using the far simpler ROWTYPE and "FETCH INTO" approach (since now it's just a single table in the result set).

The short of it is it works flawlessly with the ROWTYPE technique but has the same exact malificent behavior with the DBMS_SQL technique.

One thing is I put the code to handle the repeated unit test calls inside a local procedure in the unit test script. The full script (sorry about the size) is as follows:

DECLARE

    -- Used with RTTM_NEXT_N_FETCH stored procedure
    v_fetch_table VARCHAR2(30);
    v_fetch_type VARCHAR2(20);
    v_fetch_size INTEGER;
    v_trade_status rttm_execution.trade_status%TYPE;
    v_boundary_from TIMESTAMP := NULL;
    v_boundary_to TIMESTAMP := NULL;
    v_sliding_from TIMESTAMP := NULL;
    v_sliding_to TIMESTAMP := NULL;
    v_oldest_timestamp TIMESTAMP;
    v_newest_timestamp TIMESTAMP;
    v_execution_records SYS_REFCURSOR;
    v_clearing_records SYS_REFCURSOR;

    -- Used for DBMS_SQL stuff
    v_cursor_number INTEGER;
    v_desc_tab DBMS_SQL.DESC_TAB;
    v_column_count INTEGER;
    v_col_name VARCHAR2(30);
    v_col_value_timestamp TIMESTAMP;
    v_col_value_varchar2 VARCHAR2(4000);

    -- Used to obtain/print cursor results
    v_record_count INTEGER;
    c_execution_id VARCHAR2(4000);
    c_trade_status VARCHAR2(4000);
    c_insert_timestamp TIMESTAMP;
    c_symbol VARCHAR2(4000);
    c_clearing_id VARCHAR2(4000);
    c_security_id VARCHAR2(4000);

    PROCEDURE run_test IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(RPAD('*', 80, '*'));
        DBMS_OUTPUT.PUT_LINE('* Input:');
        DBMS_OUTPUT.PUT_LINE('*    v_fetch_table=' || v_fetch_table);
        DBMS_OUTPUT.PUT_LINE('*    v_fetch_type=' || v_fetch_type);
        DBMS_OUTPUT.PUT_LINE('*    v_fetch_size=' || v_fetch_size);
        DBMS_OUTPUT.PUT_LINE('*    v_trade_status=' || v_trade_status);
        DBMS_OUTPUT.PUT_LINE('*    v_boundary_from=' || NVL(TO_CHAR(v_boundary_from, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
        DBMS_OUTPUT.PUT_LINE('*    v_boundary_to=' || NVL(TO_CHAR(v_boundary_to, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
        DBMS_OUTPUT.PUT_LINE('*    v_sliding_from=' || NVL(TO_CHAR(v_sliding_from, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
        DBMS_OUTPUT.PUT_LINE('*    v_sliding_to=' || NVL(TO_CHAR(v_sliding_to, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
        DBMS_OUTPUT.PUT_LINE('*    v_oldest_timestamp=' || NVL(TO_CHAR(v_oldest_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
        DBMS_OUTPUT.PUT_LINE('*    v_newest_timestamp=' || NVL(TO_CHAR(v_newest_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));

        rttm_next_n_fetch(
            v_fetch_table,
            v_fetch_type,
            v_fetch_size,
            v_trade_status,
            v_boundary_from,
            v_boundary_to,
            v_sliding_from,
            v_sliding_to,
            v_oldest_timestamp,
            v_newest_timestamp,
            v_execution_records,
            v_clearing_records
        );

        DBMS_OUTPUT.PUT_LINE('* Output:');
        DBMS_OUTPUT.PUT_LINE('*    v_sliding_from=' || NVL(TO_CHAR(v_sliding_from, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
        DBMS_OUTPUT.PUT_LINE('*    v_sliding_to=' || NVL(TO_CHAR(v_sliding_to, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
        DBMS_OUTPUT.PUT_LINE('*    v_oldest_timestamp=' || NVL(TO_CHAR(v_oldest_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));
        DBMS_OUTPUT.PUT_LINE('*    v_newest_timestamp=' || NVL(TO_CHAR(v_newest_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF'), 'null'));

        DBMS_OUTPUT.PUT_LINE('* Execution:');
        IF (v_execution_records IS NULL)
        THEN
            DBMS_OUTPUT.PUT_LINE('*    NULL');
        ELSE
            v_cursor_number := DBMS_SQL.TO_CURSOR_NUMBER(v_execution_records);
            DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_number, v_column_count, v_desc_tab);
            FOR i IN 1..v_column_count
            LOOP
                IF (v_desc_tab(i).col_type in (12, 178, 179, 180, 181, 231))
                THEN
                    DBMS_SQL.DEFINE_COLUMN(v_cursor_number, i, v_col_value_timestamp);
                ELSE
                    DBMS_SQL.DEFINE_COLUMN(v_cursor_number, i, v_col_value_varchar2, 4000);
                END IF;
            END LOOP;
            v_record_count := 0;
            WHILE (DBMS_SQL.FETCH_ROWS(v_cursor_number) > 0)
            LOOP
                v_record_count := v_record_count + 1;
                c_execution_id := NULL;
                c_trade_status := NULL;
                c_insert_timestamp := NULL;
                c_symbol := NULL;
                FOR i IN 1..v_column_count
                LOOP
                    v_col_name := v_desc_tab(i).col_name;
                    IF (v_col_name = 'EXECUTION_ID')
                    THEN
                        DBMS_SQL.COLUMN_VALUE(v_cursor_number, i, c_execution_id);
                    ELSIF (v_col_name = 'TRADE_STATUS')
                    THEN
                        DBMS_SQL.COLUMN_VALUE(v_cursor_number, i, c_trade_status);
                    ELSIF (v_col_name = 'INSERT_TIMESTAMP')
                    THEN
                        DBMS_SQL.COLUMN_VALUE(v_cursor_number, i, c_insert_timestamp);
                    ELSIF (v_col_name = 'SYMBOL')
                    THEN
                        DBMS_SQL.COLUMN_VALUE(v_cursor_number, i, c_symbol);
                    ELSIF (v_desc_tab(i).col_type in (12, 178, 179, 180, 181, 231))
                    THEN
                        DBMS_SQL.COLUMN_VALUE(v_cursor_number, i, v_col_value_timestamp);
                    ELSE
                        DBMS_SQL.COLUMN_VALUE(v_cursor_number, i, v_col_value_varchar2);
                    END IF;
                END LOOP;
                DBMS_OUTPUT.PUT_LINE(
                    '*    Record #' || v_record_count
                    || ' execution_id=' || c_execution_id
                    || ' trade_status=' || c_trade_status
                    || ' insert_timestamp=' || to_char(c_insert_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF')
                    || ' symbol=' || c_symbol
                );
            END LOOP;
            DBMS_SQL.CLOSE_CURSOR(v_cursor_number);
        END IF;

        -- Just close clearing records for now
        IF (v_clearing_records IS NOT NULL)
        THEN
            CLOSE v_clearing_records;
        END IF;
    END;
BEGIN

    v_boundary_from := NULL;
    v_boundary_to := NULL;
    v_sliding_from := NULL;
    v_sliding_to := NULL;
    v_fetch_table := 'Execution';
    v_fetch_type := 'First';
    v_fetch_size := 10;
    v_trade_status := 'Unmatched';
    run_test();

    insert into rttm_execution (execution_id, trade_status, insert_timestamp, symbol) values ('E000', 'Unmatched', to_timestamp(to_char(sysdate, 'YYYY-MM-DD') || '08:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF'), 'PRODUCT_000');
    insert into rttm_execution_2 (execution_id, transaction_time) values ('E000', to_timestamp(to_char(sysdate, 'YYYY-MM-DD') || '08:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF'));
    insert into rttm_execution_c (execution_id) values ('E000');
...
... etc.
...

    insert into rttm_clearing (clearing_id, trade_status, insert_timestamp, security_id) values ('C000', 'Unmatched', to_timestamp(to_char(sysdate, 'YYYY-MM-DD') || '08:00', 'YYYY-MM-DD HH24:MI:SS.FF'), 'PRODUCT_019X');
    insert into rttm_clearing_2 (clearing_id, transaction_time) values ('C000', to_timestamp(to_char(sysdate, 'YYYY-MM-DD') || '08:00', 'YYYY-MM-DD HH24:MI:SS.FF'));
    insert into rttm_clearing_c (clearing_id) values ('C000');
...
... etc.
...

    run_test();
    v_fetch_type := 'Next';
    run_test();
    run_test();
    v_fetch_type := 'Refresh';
    run_test();
    v_fetch_type := 'Next';
    run_test();
    run_test();
    v_fetch_type := 'Last';
    run_test();
    v_fetch_type := 'Previous';
    run_test();
    run_test();
    run_test();
    run_test();
    v_fetch_table := 'Clearing';
    v_fetch_type := 'First';
    run_test();
    v_fetch_type := 'Next';
    run_test();
    v_fetch_type := 'Refresh';
    run_test();
    v_fetch_type := 'Next';
    run_test();
    run_test();
    run_test();
    v_fetch_type := 'Last';
    run_test();
    v_fetch_type := 'Previous';
    run_test();
    run_test();
    run_test();
    run_test();
END;
/

ROLLBACK;


I wondered if there might be something about the fact that these DBMS_SQL calls are being made within a local procedure. Perhaps if I actually created a unit test procedure and ran it from there it would work better.

No dice. Same exact error. After running the "run_test" procedure twice, I get the same error:

DECLARE
*
ERROR at line 1:
ORA-21779: duration not active


Question: Any ideas? Do you see something that is missing?

It looks like using Java for a unit test would be infinitely simpler. The frustrating thing is I am virtually certain the procedure works. I just can't prove it with a unit test.

Tom Kyte
January 10, 2011 - 7:46 am UTC

You didn't see this decode statement???

       decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                       2, decode(c.scale, null,
                                 decode(c.precision#, null, 'NUMBER', 'FLOAT'),
                                 'NUMBER'),
                       8, 'LONG',
                       9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                       12, 'DATE',
                       23, 'RAW', 24, 'LONG RAW',
                       58, nvl2(ac.synobj#, (select o.name from obj$ o
                                where o.obj#=ac.synobj#), ot.name),
                       69, 'ROWID',
                       96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
                       100, 'BINARY_FLOAT',
                       101, 'BINARY_DOUBLE',
                       105, 'MLSLABEL',
                       106, 'MLSLABEL',
                       111, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
                       113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                       121, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       122, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       123, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       178, 'TIME(' ||c.scale|| ')',
                       179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
                       180, 'TIMESTAMP(' ||c.scale|| ')',
                       181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE',
                       231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZONE',
                       182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',
                       183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||
                             c.scale || ')',
                       208, 'UROWID',
                       'UNDEFINED'),
       decode(c.type#, 111, 'REF'),



that is in the view text of DBA_TAB_COLS


Make your example really teeny tiny and make it reproduce the error in full and make it 100% complete (routine to call and everything - use all_users, all_objects - etc to test with)

and then I'll take a look. The code is too large right now. Get rid of everything that is NOT RELEVANT to reproducing the issue - everything.

Pass array to an procedure

A, July 08, 2011 - 4:37 am UTC

Hello Tom,
Is there a way better way of passing array to an stored procedure in Oracle 9i?


create or replace PACKAGE pkg_getdetails AS
TYPE expInt_Rec IS RECORD (
ID exptable.ID%type,
REF exptable.REF%type);
TYPE expInt_Table IS TABLE OF expInt_Rec INDEX BY BINARY_INTEGER;
PROCEDURE getdetails(expInt_list IN expInt_Table,
p_status OUT varchar2,
P_ERROR_CODE OUT number,
P_ERROR_MESSAGE OUT varchar2);
END;

create or replace PACKAGE BODY pkg_getdetails AS
PROCEDURE getdetails(expInt_list IN expInt_Table,
p_status OUT varchar2,
P_ERROR_CODE OUT number,
P_ERROR_MESSAGE OUT varchar2
) IS
ls_Id exptable.ID%type;
BEGIN
p_status :='Ok';
for i in expInt_list.first..expInt_list.last
loop
update exptable set ref=expInt_list(i).REF
where ID = expInt_list(i).ID;
end loop;
EXCEPTION
WHEN OTHERS THEN
p_Error_Code := SQLCODE;
p_Error_Message := SUBSTR(SQLERRM, 1, 255);
p_status :='Fail';
END getdetails;
END;


DECLARE
expInt_list pkg_getdetails.expInt_Table;
P_ERROR_CODE number;
P_ERROR_MESSAGE varchar2(255);
p_status varchar2(20);
BEGIN
expInt_list(1).ID:='000000001498238';
expInt_list(1).REF:='3555835';

expInt_list(2).ID:='000000001498308';
expInt_list(2).REF:='3557114';

expInt_list(3).ID:='000000001498419';
expInt_list(3).REF:='3556418';

pkg_getdetails.getdetails(expInt_list,p_status,P_ERROR_CODE,P_ERROR_MESSAGE);

dbms_output.put_line(p_status);
END;

Tom Kyte
July 08, 2011 - 2:49 pm UTC

what better way could there be? I mean it is just:


pkg_getdetails.getdetails(expInt_list,p_status,P_ERROR_CODE,P_ERROR_MESSAGE);



Your error handling however is pretty bad. I hate your code:
http://asktom.oracle.com/pls/asktom/asktom.search?p_string=%22i+hate+your+code%22 
    EXCEPTION
        WHEN OTHERS THEN
            p_Error_Code    := SQLCODE;
            p_Error_Message := SUBSTR(SQLERRM, 1, 255);
            p_status :='Fail';  
    END getdetails;



I would definitely ERASE that code.

also, you must use bulk processing for something like this:



 for i in expInt_list.first..expInt_list.last
    loop  
        update exptable set ref=expInt_list(i).REF
        where ID = expInt_list(i).ID;
    end loop;


has to be:

 forall i in expInt_list.first..expInt_list.last
        update exptable set ref=expInt_list(i).REF
        where ID = expInt_list(i).ID;



search this site for "forall" to see lots of examples..


If you were looking for an 'easier way to INITIALIZE an array', you could consider using an object type:



ops$tkyte%ORA11GR2> create type myType as object (
  2  id  number,
  3  ref varchar2(20)
  4  )
  5  /

Type created.

ops$tkyte%ORA11GR2> create type myArray as table of myType;
  2  /

Type created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          l_data myArray := myArray( mytype( 1, '1253' ), myType( 2, '452' ) );
  3  begin
  4          null;
  5  end;
  6  /

PL/SQL procedure successfully completed.

how to deal when one of the column is a cursor?

Yoann, February 19, 2013 - 9:54 am UTC

Using your code, I was trying to handle master/detail cursor, but I can't figure out how to deal with the sub cursor. I figure out that the col_type was 102 for the cursor, but I didn't succeed in getting the values, nor defining the type of the column
Example of code based on your code:

CREATE OR REPLACE PROCEDURE print_ref_cursor (p_query IN OUT SYS_REFCURSOR
, p_date_fmt IN VARCHAR2 DEFAULT 'dd-mon-yyyy hh24:mi:ss'
, p_level IN VARCHAR2 DEFAULT 0
) IS
l_thecursor INTEGER;
l_columnvalue VARCHAR2 (4000);
l_desctbl DBMS_SQL.desc_tab2;
l_colcnt NUMBER;
l_date DATE;
l_sys_rc SYS_REFCURSOR;
BEGIN
l_thecursor := DBMS_SQL.to_cursor_number (p_query);
DBMS_SQL.describe_columns2 (l_thecursor, l_colcnt, l_desctbl);

-- define all columns to be cast to varchar2's, we
-- are just printing them out
DBMS_OUTPUT.put_line ('First Loop');

FOR i IN 1 .. l_colcnt LOOP
CASE
WHEN l_desctbl (i).col_type IN (12, 178, 179, 180, 181, 231) THEN
DBMS_OUTPUT.put_line (' col is like a date');
DBMS_SQL.define_column (l_thecursor, i, l_date);
WHEN l_desctbl (i).col_type IN (102) THEN
DBMS_OUTPUT.put_line (' col is like a SYS REFCURSOR');
-- DBMS_SQL.define_column (l_thecursor, i, l_sys_rc); -- THIS DOESN'T WORK
ELSE
DBMS_OUTPUT.put_line (' col is like a VC2(4000). realID: ' || l_desctbl (i).col_type);
DBMS_SQL.define_column (l_thecursor, i, l_columnvalue, 4000);
END CASE;
END LOOP;


DBMS_OUTPUT.put_line ('While Loop');

WHILE (DBMS_SQL.fetch_rows (l_thecursor) > 0) LOOP
DBMS_OUTPUT.put_line (' in While Loop');

FOR i IN 1 .. l_colcnt LOOP
CASE
WHEN l_desctbl (i).col_type IN (12, 178, 179, 180, 181, 231) THEN
DBMS_SQL.COLUMN_VALUE (l_thecursor, i, l_date);
l_columnvalue := TO_CHAR (l_date, p_date_fmt);
WHEN l_desctbl (i).col_type IN (102) THEN
null;
-- DBMS_SQL.COLUMN_VALUE (l_thecursor, i, l_sys_rc);
-- print_ref_cursor (l_sys_rc, p_date_fmt, p_level);
ELSE
DBMS_SQL.COLUMN_VALUE (l_thecursor, i, l_columnvalue);
END CASE;

DBMS_OUTPUT
.put_line (
RPAD ('', p_level * 3, ' ')
|| RPAD (l_desctbl (i).col_schema_name || '.' || l_desctbl (i).col_name, 30)
|| ': '
|| l_columnvalue);
END LOOP;

DBMS_OUTPUT.put_line ('-----------------');
END LOOP;


IF (p_level > 0) THEN
DBMS_OUTPUT.put_line ('close cursor');
DBMS_SQL.close_cursor (l_thecursor);
END IF;
END;
/

DECLARE
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT au.*
, CURSOR (SELECT *
FROM all_tables at
WHERE au.username = at.owner AND ROWNUM <= 2)
FROM all_users au
WHERE ROWNUM <= 2;

print_ref_cursor (l_cursor);
END;

Question: Is it possible to do what I'm trying to achieve?
Tom Kyte
February 25, 2013 - 8:16 am UTC

http://docs.oracle.com/cd/E11882_01/appdev.112/e23448/d_sql.htm#sthref9157

dbms_sql doesn't have any bindings for a cursor type in current releases...

for vance's error of first post

Ranjan, July 05, 2013 - 1:19 am UTC

Hi Tom,

At the top of this page vance faced some error when the refcursor is kind of join(no clean rowtype).

I think he did some mistake which I have commented in the below codes.
#######
CREATE OR REPLACE PROCEDURE my_procedure
(
o_result_set OUT SYS_REFCURSOR
) AS

BEGIN

o_result_set := NULL;
OPEN o_result_set FOR
SELECT
*
FROM
emp,dept;
END;
/

_______________

DECLARE

v_my_object_records SYS_REFCURSOR;
type x is record (a number, b varchar2(200),c varchar2(100),d number,e date,f number,g number,h number,i number,j varchar2(100),k varchar2(100));
xx x;

BEGIN

-- Call the procedure
my_procedure(
v_my_object_records ---here he called by declaring refcursor variable type(might caused the error for him)
);

-- Loop through the resulting data
IF (v_my_object_records IS NOT NULL)
THEN
LOOP

FETCH v_my_object_records INTO xx;
EXIT WHEN v_my_object_records%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('* ' || xx.a);
END LOOP;
CLOSE v_my_object_records;
END IF;
END;
/

##############
output (no error)

* 7369
* 7499
* 7521
* 7566
* 7654
* 7698
* 7782
* 7788
* 7839
* 7844
* 7876
* 7900
* 7902
* 7934
* 7369
* 7499
* 7521
* 7566
* 7654
* 7698
* 7782
* 7788
* 7839
* 7844
* 7876
* 7900
* 7902
* 7934
* 7369
* 7499
* 7521
* 7566
* 7654
* 7698
* 7782
* 7788
* 7839
* 7844
* 7876
* 7900
* 7902
* 7934
* 7369
* 7499
* 7521
* 7566
* 7654
* 7698
* 7782
* 7788
* 7839
* 7844
* 7876
* 7900
* 7902
* 7934
Could you please give your view on this.



Appericiate

Krishna Kumar, June 09, 2015 - 9:23 am UTC

Thank you very much for your knowledge.
It help me a lot.

Very Helpful for New User of DBMS_SQL

John Rikala, June 27, 2015 - 5:50 pm UTC

Using DBMS_SQL for a refcursor passed in from another procedure was the problem I needed to solve, and the example provided was exactly what I needed to learn how to solve it.

Thanks a lot.

Need help to change print_ref_cursor without In Out parameter

NUTHAN, May 25, 2022 - 6:11 pm UTC

Hi Tom,

I am using this code to perform UNIT testing of the data returned by a sys_refcursor.

YOUR procedure "print_ref_cursor" really helped me a lot but i am 1 step to achive my requirements.

I have changed your procedure print_ref_cursor as a pipelined funtion which return a value of ( column, value ) pair result set.

Issue: By using IN OUT parameter to pass the cursor variable. I am not able to use it as a FUNCTION.

Could you please suggest me how to pass a cursor variable as only IN Paramater for "print_ref_cursor" procedure? ( without INOUT its throwing invalid cursor error)


I kindly request you to help me out as i am at the last step to achive my requirements.

Continuation to the above query

NUTHAN, May 25, 2022 - 6:41 pm UTC

CREATE OR REPLACE TYPE obj_row
As object
( colname VARCHAR2(4000),
colval VARCHAR2(4000)
);
/

CREATE OR REPLACE TYPE tab_obj_row IS TABLE OF obj_row;
/

CREATE OR REPLACE FUNCTION print_ref_cursor( p_result_set IN SYS_refcursor)
Return tab_obj_row pipelined
as
V_result_set SYS_REFCURSOR;
v_cursor_number INTEGER;
V_desc_tab dbms_sql.desc_tab;
V_column_cnt INTEGER;
v_value VARCHAR2(4000);
BEGIN
IF p_result_set IS NOT NULL THEN
V_CURSOR_NUMBER :=
dbms_sql.to_cursor_number(p_result_set); dbms_sql.describe_columns(v_cursor_number,v_column_cnt, v_desc_tab);
For i in 1..v_column_cnt
Loop
Dbms_sql.define_column(v_cursor_number, i,
v_value, 4000);
End loop;

While ( dbms_sql.fetch_rows (v_cursor_number) >0)
Loop

For i in 1..v_coln_cnt
Loop
dbms_sql.column_value(v_cursor_number, i, v_value);
pipe row(obj_row( v_desc_tab(i).col_name , v_value));
End loop;
End loop;
Dbms_sql.close_cursor(v_cursor_number);
End if;
End;
/


My usage requirements:

Declare
V_cursor sysrefcursor;
Begin
application_procedure ( v_cursor OUT sysr3fcursor);

Select count(*) INTO v_cnt from table ( print_ref_cursor( v_cursor))
Where colname = 'EMPID' and colval = '7369';

End;

If v_cnt exists my test case is passed.



I kindly request you to suggest me a way to write this function without using INOUT parameter


Chris Saxon
May 26, 2022 - 1:21 pm UTC

You can have an IN OUT REFCURSOR parameter. There are a couple of other issues here:

- You can't call a function with OUT parameters in SQL
- dbms_sql.to_cursor_number has an IN OUT parameter

Making the parameter IN only to overcome the first problem causes the second. To overcome this assign the input refcursor to your function to a local variable. Then use that in the call to dbms_sql.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library