CODEWITHSUNDEEP
oracle-databaseplsqloracle11gref-cursorrowtype
West Bennett
West Bennett

Reputation: 594

Getting a Ref Cursor from Custom Cursor Type

We have an existing PL SQL procedure called "Get()" that, when provided with an order_no_ and a sequence_no_, returns a named REF CURSOR type called "CURSOR_TYPE". I need to make an "overload" of this method where the programmer just passes in the order_no_ (no sequence_no_), and it calls that method repeatedly and collects all of the records for all sequence_no_ values into a single return "CURSOR_TYPE" REF CURSOR. Here's what I wrote to try to accomplish this, trying to follow the example shown here. 

    PROCEDURE Get(order_no_ IN VARCHAR2, results_cursor OUT CURSOR_TYPE)
    AS
    --Declare a "nested table type" table
    TYPE CoC_RowType IS TABLE OF customer_order_charge_cfv%ROWTYPE; 
    CoC_RowTable CoC_RowType := CoC_RowType();
    CURSOR c1 IS
      SELECT coc.SEQUENCE_NO
      FROM customer_order_charge_cfv coc
      WHERE coc.ORDER_NO = order_no_;
    BEGIN
    FOR i in c1 LOOP
        CoC_RowTable.extend();
        Get(order_no_, i.sequence_no, results_cursor);
        FETCH results_cursor INTO CoC_RowTable(CoC_RowTable.count);
    END LOOP;

    OPEN results_cursor FOR SELECT * FROM TABLE(CoC_RowTable);

    END Get;

When I try to compile this code into my package, however, I get the following error:

    Compilation errors for PACKAGE BODY GLOB1APP.GFS_CUSTOMER_ORDER_CHARGE_CFP

    Error: PLS-00382: expression is of wrong type
    Line: 81
    Text: OPEN results_cursor FOR SELECT * FROM TABLE(CoC_RowTable);

    Error: PL/SQL: ORA-22905: cannot access rows from a non-nested table item
    Line: 81
    Text: OPEN results_cursor FOR SELECT * FROM TABLE(CoC_RowTable);

    Error: PL/SQL: SQL Statement ignored
    Line: 81
    Text: OPEN results_cursor FOR SELECT * FROM TABLE(CoC_RowTable);

And it appears to erroring on this line:

    OPEN results_cursor FOR SELECT * FROM TABLE(CoC_RowTable);

What am I doing wrong? And how can I accomplish my goal? Is this totally the wrong path to accomplishing this?

Upvotes: 0

Views: 1188

Answers (2)

Stew Ashton
Stew Ashton

Reputation: 1529

Why not change the existing procedure and query to do both? Simple example:

SQL> create table customer_order_charge_cfv(
  2    order_no number,
  3    sequence_no number
  4  );

Table CUSTOMER_ORDER_CHARGE_CFV created.

SQL> insert into customer_order_charge_cfv
  2  select 1,1 from dual union all
  3  select 1,2 from dual;

2 rows inserted.

SQL> create or replace PROCEDURE Get(
  2    order_no_ IN VARCHAR2, 
  3    sequence_no_ IN VARCHAR2 default null, 
  4    results_cursor OUT SYS_REFCURSOR
  5  ) AS
  6  BEGIN
  7  OPEN results_cursor FOR 
  8    SELECT *
  9    FROM customer_order_charge_cfv coc
 10    WHERE coc.ORDER_NO = order_no_
 11    and (coc.sequence_no = sequence_no_ or sequence_no_ is null);
 12  END Get;
 13  /

Procedure GET compiled

SQL> var rc refcursor;

SQL> exec get(1,2,:rc);

PL/SQL procedure successfully completed.


SQL> print :rc;

  ORDER_NO SEQUENCE_NO
---------- -----------
         1           2

SQL> exec get(1,results_cursor => :rc);

PL/SQL procedure successfully completed.

SQL> print :rc;

  ORDER_NO SEQUENCE_NO
---------- -----------
         1           1
         1           2

Regards, Stew

Upvotes: 1

Stew Ashton
Stew Ashton

Reputation: 1529

There are two "types" of TYPE in Oracle:

SQL types, created by SQL statements:

CREATE OR REPLACE TYPE...
/

and PL/SQL types, declared within PL/SQL code.

You are trying to use a PL/SQL type in a SQL statement. SQL statements only know about SQL types. You either have to create a SQL "object" type, then a SQL "table" type of that object, or else do something different.

Also, REF CURSORs don't work the way your code is trying to make them work. You would need to execute one SQL query that returns the same data as all these GET() calls, then return the cursor for that query without doing any fetching at all. The client does the fetching.

To get more detail, post the definition of CURSOR_TYPE and show what GET() is actually querying.

This could be easy or not depending on your answers.

Best regards, Stew Ashton

Upvotes: 0

Related Questions