Read an Array or Bulk Collect as a table in an Oracle PLSQL

Question

How to load an Array or Bulk into a PLSQL and then read this as a table.

Example

DECLARE
  VA_ARRAY ....DEFINIÇÃO DO ARRAY
  VN_QTD NUMBER;
BEGIN
  -- TABELA01 É UMA TABELA FÍSICA EXISTENTE NO BANCO
  SELECT * BULK COLLECT INTO VA_ARRAY FROM TABELA01;
  --AQUI QUERY FAZER UM JOIN DE OUTRA TABELA COM O ARRAY GERADO
  -- TABELA02 É UMA OUTRA TABELA FÍSICA EXISTENTE NO BANCO
  SELECT COUNT(*) INTO VN_QTD
  FROM TABELA02 , (VA_ARRAY) COMO TABELA
  WHERE TABELA02.COLUNA01 = (VA_ARRAY).CAMPO ...;
END;

I could use a subselect sei , but in case the SQL in fact would be too heavy so I wanted to try to use the table in memory, the documentation of Oracle is full but bad of small examples , if someone knows a simple way I appreciate any help.

The example itself I do not think is important, but rather doubt , how to read an "array" as a table in an SQL in a block or PLSQL object ?

Answer 1

I think what you need to fill the array is this in DECLARE: type tab01_type is table of tabela01%rowtype; (NO INDEX BY INDICATION) then create a variable based on this type: v_tab01_array tab01_type;

And the Bulk Collect already works.

Answer 2

Look at this:

DECLARE
   howmany NUMBER;
   some_first employees.first_name%TYPE;
   some_last employees.last_name%TYPE;
   some_employee employees%ROWTYPE;
   TYPE first_typ IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
   TYPE last_typ IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
   first_names first_typ;
   last_names last_typ;
   CURSOR c1 IS SELECT first_name, last_name FROM employees;
   TYPE name_typ IS TABLE OF c1%ROWTYPE INDEX BY PLS_INTEGER;
   all_names name_typ;
   TYPE emp_typ IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
   all_employees emp_typ;
BEGIN
-- Query a single value and store it in a variable.
   SELECT COUNT(*) INTO howmany FROM user_tables;
   dbms_output.put_line('This schema owns ' || howmany || ' tables.');

-- Query multiple columns from one row, and store them in variables.
   SELECT first_name, last_name INTO some_first, some_last
      FROM employees WHERE ROWNUM < 2;
   dbms_output.put_line('Random employee: ' || some_first ||
      ' ' || some_last);

-- Query a single row and store it in a record.
   SELECT * INTO some_employee FROM employees WHERE ROWNUM < 2;

-- Query multiple columns from multiple rows, and store them in a collection
-- of records.
   SELECT first_name, last_name BULK COLLECT INTO all_names FROM EMPLOYEES;

-- Query multiple columns from multiple rows, and store them in separate
-- collections. (Generally less useful than a single collection of records.)
   SELECT first_name, last_name
      BULK COLLECT INTO first_names, last_names
      FROM EMPLOYEES;

-- Query an entire (small!) table and store the rows
-- in a collection of records. Now you can manipulate the data
-- in-memory without any more I/O.
   SELECT * BULK COLLECT INTO all_employees FROM employees;
END;
/

Answer 3

In this case I use temporary tables. Data entered, changed or deleted from them is only visible in your Oracle session. When the session dies EVERYTHING is deleted. To create a temporary table just use the TEMPORARY keyword in create table: CREATE TEMPORARY TABLE ....