Send a nested table using sys_refcursor in Oracle10gv2

Question

I need to send some data to Jasper Reports using a sys_refcursor. This data is the result of a query and an evaluation of the query results in pl/sql. The idea is to count some values filtering by several columns in the same table, and that can't be done in a query with subselects due to the filtering restrictions. Sorry for not being very clear but I'm under a NDA. However, I can post some code and explain the important part of the functionality that I must achieve. The project is based on Java and uses Oracle 10gv2 and JasperReports 3.6.1, and this can't be updated (so no Oracle v12).

I have Procedure with an Associative Array populated with the keys and the values I have to return. Keys represents the filtering results associated with every column type on the destination report, and the values are numbers that must populate the correct column. Here is the Procedure creation and the declaration for the Associative Array.

create or replace PROCEDURE test_proc02(test_cursor OUT sys_refcursor) IS

    /* associative arrays declaration */

    TYPE transfer_type IS TABLE OF NUMBER
        INDEX BY VARCHAR2(10);
    transfer_table transfer_type; 

But one of the problems is that I can't use an Associative Array with a sys_refcursor like this:

Select * from table(cast(transfer_table AS transfer_type))

So I copy the Associative Array values into a Nested Table, believing that the previous select will work with that structure. Here is part of the code

/* nested table declaration */

TYPE transfer_nt_type IS TABLE OF VARCHAR2(20);

/* nested table initilization */
transfer_nt transfer_nt_type := transfer_nt_type();

/* some variables */
transfer_id VARCHAR2(10);
transfer_number NUMBER;
nt_counter INTEGER := 0;
nt_iter VARCHAR2(10);


/* copying AA into NT */
nt_iter := transfer_table.FIRST;
WHILE (nt_iter IS NOT NULL)
LOOP        
    nt_counter := nt_counter+1;
    transfer_nt.EXTEND;
    transfer_nt(nt_counter):=transfer_table(nt_iter);
    dbms_output.put_line('nested table ('||nt_counter||'): '||transfer_nt(nt_counter));
    nt_iter := transfer_table.NEXT(nt_iter);
END LOOP;

/* Trying to send NT to JR */
OPEN travelCursor FOR SELECT * FROM TABLE(cast(transfer_nt AS transfer_nt_type));

/* ERROR */
PLS-00382: expression is of wrong type

I don't care about the method, I just want to send the data to JR to generate a report, so if I have to replace the full Procedure structure is fine with me. I've searched for days here in stackoverflow and other sources, but nothing seems to work, so I'm not sure if all my concept ideas are wrong or so.

Any ideas? Thanks.

EDIT:

The Type declaration for transfer_nt_type was wrong, copied from a previous version. Now it's the correct one. The data of the AA is something like this:

Key       value
--------------
A548521     5
A325411     12
A329471     9

The total amount of pairs is 32, with the key as a varchar2(10) and the value as a number. The content (VARCHAR(20)) of the final nested table is:

A548521#5,A325411#12,A329471#9

The type is declared at schema level. I've tried also:

OPEN travelCursor FOR 
        SELECT CAST(MULTISET(
            SELECT * FROM TABLE(transfer_nt)
            ORDER BY 1) AS transfer_nt_type)
        INTO transfer_nt_out FROM DUAL;

With the same results. Both data structures have been tested and printed with dbms_output flawlessly, data inside the structures is correct. I need at least to send the values, at a given order if possible. Keys are not important if I can maintain a certain order in the value response.

Edited to reflect the Alex Poole proposal. Before the Procedure BEGIN:

FUNCTION transfer_func (transfer_table transfer_type)RETURN transfer_nt_type PIPELINED IS

      --TYPE transfer_type IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
      --transfer_table transfer_type; 

      nt_iter VARCHAR2(10);

      BEGIN

          nt_iter := transfer_table.FIRST;
          WHILE (nt_iter IS NOT NULL)
          LOOP
            PIPE ROW (nt_iter || '#' || transfer_table(nt_iter));
            nt_iter := transfer_table.NEXT(nt_iter);
          END LOOP;

    END transfer_func;

Before Procedure END:

OPEN travelCursor for select * from table(transfer_func(transfer_table));

Same error:

PLS-00382: expression is of wrong type

Final edit and solution:

Finally I solved the problem using GTT. I don't know why, but the first time I tried this method Oracle Developer return the same error as with the other possible solutions. I tried the oldest method: close the program, reset the machine and start from the beginning· And that worked! only with GTT, of course.

nt_iter := transfer_table.FIRST;
WHILE (nt_iter IS NOT NULL)
LOOP        
        nt_counter := nt_counter+1;
        INSERT INTO transfer_temp VALUES(nt_iter,transfer_table(nt_iter),06);

        nt_iter := transfer_table.NEXT(nt_iter);
END LOOP;

    OPEN test_cursor FOR select * from transfer_temp order by transfer_temp.id;

CREATE GLOBAL TEMPORARY TABLE transfer_temp (
        id           VARCHAR(20),
        value         NUMBER,
        month         NUMBER
        )
        ON COMMIT PRESERVE ROWS;

Thanks everyone for the help!

Answer 1

You could use a pipelined function instead, using a schema-level table type:

create or replace TYPE transfer_nt_type AS TABLE OF VARCHAR2(20)
/

create or replace FUNCTION test_func02
RETURN transfer_nt_type PIPELINED IS

  TYPE transfer_type IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
  transfer_table transfer_type; 

  nt_iter VARCHAR2(10);

BEGIN

  -- sample data
  transfer_table('A548521') := 5;
  transfer_table('A325411') := 12;
  transfer_table('A329471') := 9;

  nt_iter := transfer_table.FIRST;
  WHILE (nt_iter IS NOT NULL)
  LOOP
    PIPE ROW (nt_iter || '#' || transfer_table(nt_iter));
    nt_iter := transfer_table.NEXT(nt_iter);
  END LOOP;

  RETURN;

END test_func02;
/

Then you can do:

select * from table(test_func02);

which gets:

Result Sequence     
--------------------
A325411#12
A329471#9
A548521#5

If you're restricted to a ref cursor then you can add a wrapper procedure:

create or replace PROCEDURE test_proc02(test_cursor OUT sys_refcursor) IS
BEGIN

  OPEN test_cursor FOR SELECT * FROM TABLE(test_func02);

END test_proc02;
/

var rc refcursor;

exec test_proc02(:rc);

print rc;

Result Sequence     
--------------------
A325411#12
A329471#9
A548521#5

Answer 2

A (low perf perhaps) no-brainer solution would be to write the result to a temporary table, then

OPEN travelCursor FOR SELECT * FROM That_Temp_Table;

Answer 3

I believe that you have problem because:

1) You defined your type inside procedure

So try to create type like in example above

CREATE OR REPLACE TYPE "TSTRINGTABLE" AS TABLE OF VARCHAR2(20)
/

2) You use select * from on indexed table...

if you need to store 2 values in your nested table, so create type table of records with record like

CREATE OR REPLACE TYPE "TSTRING2LIST_RECORD" AS OBJECT
(
  column1_value VARCHAR2(4000),
  column2_value VARCHAR2(4000)
)
/

CREATE OR REPLACE TYPE "TSTRING2LIST_TABLE" IS TABLE OF TSTRING2LIST_RECORD
/

after that you can make declare it in your procedure and use it whatever you want, my code below could be broken, because I write it without IDE, but you need to catch my idea:

PROCEDURE idk(cur out sys_refcursor) IS
  l_table tstring2list_table := tstring2list_table();

BEGIN
  l_table.extend;
  l_table(l_table.count) := tstring2list_record('idx1', 'value1');
  l_table.extend;
  l_table(l_table.count) := tstring2list_record('idx2', 'value2');
  l_table.extend;
  l_table(l_table.count) := tstring2list_record('idx3', 'value3');

  OPEN cur for select column2_value from table(l_table);
END;

you also can use bulk collect or dynamic SQL or whatever:

SELECT tstring2list_record(t.col1, t.col2) BULK COLLECT
  INTO l_table
  FROM some_table t