I've got a procedure where I need to cache some data, for performance reasons, for downstream operations.
The TYPE
definitions work
The BULK COLLECT INTO
works
The SELECT
does not work
PROCEDURE MYPROC((PARAMS))AS
TYPE REC_TYPE IS RECORD (
COLUMN_1 (TABLEA.COLUMN_A)%TYPE,
COLUMN_2 (TABLEA.COLUMN_B)%TYPE
);
TYPE TAB_TYPE IS TABLE OF REC_TYPE;
TABLE_1 TAB_TYPE;
BEGIN
SELECT COLUMN_A, COLUMN_B
BULK COLLECT INTO TABLE_1
FROM TABLE_A;
SELECT * FROM TABLE_1;
END MYPROC;
Yields:
Error(#,#): PL/SQL: ORA-00942: table or view does not exist
I've also tried wrapping it in a table function like I do with my single-column types elsewhere, but that did not work either
SELECT * FROM TABLE(TABLE_1);
Error(#,#): PL/SQL: ORA-22905: cannot access rows from a non-nested table item
Your problem is actually a PLS-00642 error, rather than ORA-22905. Essentially you can't use local collection types in SQL statements. The solution therefore, is to define your types at the schema level. When defining types in this way, we cannot use the %TYPE syntax, and instead must explicitly define the column (Getting PLS-00201 error while creating a type in oracle) i.e.
create or replace type rec_type as object (
COLUMN_1 integer,
COLUMN_2 varchar2(128)
);
create or replace type tab_type as table of rec_type;
You then need to explicitly convert the values into the relevant type in order to perform the bulk collect as mentioned here: ORA-00947 Not enough values while declaring type globally.
Your procedure would therefore look something like this:
PROCEDURE MYPROC((PARAMS))AS
TABLE_1 TAB_TYPE;
lCount integer;
BEGIN
SELECT REC_TYPE(COLUMN_A, COLUMN_B)
BULK COLLECT INTO TABLE_1
FROM TABLE_A;
SELECT COUNT(*) INTO lCount FROM TABLE(TABLE_1);
END MYPROC;