Hello,
Is there a way to use the TABLE operator against a locally defined PL/SQL types in a dynamic sql. I am working on Oracle 12.2
CREATE OR REPLACE PACKAGE test_api AS
TYPE t_tab IS TABLE OF number(12,0) INDEX BY BINARY_INTEGER;
END;
/
DECLARE
stt test_api.t_tab;
v_cnt PLS_INTEGER := 0;
BEGIN
WITH datarows AS (
SELECT level AS lvl
FROM dual CONNECT BY
level < 101
) SELECT lvl BULK COLLECT INTO
stt
FROM datarows;
--execute immediate 'select count(*) from table(stt)' into v_cnt;
SELECT COUNT(*) INTO
v_cnt
FROM TABLE ( stt );
dbms_output.put_line('v_cnt :: ' || v_cnt);
END;
/
The above code works fine. However, if I comment the SELECT COUNT(*) and un-comment the execute immediate it rightly throws an error because it would not find "stt".
DECLARE
stt test_api.t_tab;
v_cnt PLS_INTEGER := 0;
BEGIN
WITH datarows AS (
SELECT level AS lvl
FROM dual CONNECT BY
level < 101
) SELECT lvl BULK COLLECT INTO
stt
FROM datarows;
execute immediate 'select count(*) from table(stt)' into v_cnt;
/*
SELECT COUNT(*) INTO
v_cnt
FROM TABLE ( stt );
*/
dbms_output.put_line('v_cnt :: ' || v_cnt);
END;
/
Error report -
ORA-00904: "STT": invalid identifier
ORA-06512: at line 13
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Is there a way to make this work? or am I being too unrealistic . We have many stored procedures being migrated from Sybase where #tables are used inside dynamic sql. We plan to replace some of them using PL/SQL table types; but unless we can make it work inside dynamic SQL, the only option is to use GTT.
Thanks,
Vikram R