How do I select from Bulk Collected Table of Records Type

Question

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

Answer 1

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;