Hi,
I am working on Oracle 11g R2 Enterprise Edition on RHEL machine.
My motive is to collect the data into a Oracle multi-row pl/sql collection and then use it as a normal oracle table to use it as future sql queries
create or replace PROCEDURE Procedure_test (cv_1 out sys_refcursor)
AS
c_cursor SYS_REFCURSOR;
TYPE list_rec IS RECORD
( col1 NUMBER,
col2 table1.col2%TYPE,
col3 table2.col3%TYPE
);
TYPE list_type IS TABLE OF list_rec;
tab_list list_type := list_type();
BEGIN
CASE
WHEN id IN ( 13,14,15,16,17,18,19,23,25,26,35,36,37,38,39 ) THEN
OPEN c_cursor FOR SELECT col1 ,table1.col2, table2.col3
FROM table1 , table2 where table1.col1= table2.col1;
END CASE;
FETCH c_cursor BULK COLLECT INTO tab_list ;
select * from TABLE(CAST(tab_list as list_type )) ;
END Procedure_test ;
This is facing an error
Error(193,48): PL/SQL: ORA-00902: invalid datatype for this line "select * from TABLE(CAST(tab_list as list_type ))".
Where am I going wrong ? I have checked various sites but I could not find any wrong syntax. Can you please help me ?