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!
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;