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

Question

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

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

How do I 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 I know, but in case the SQL would indeed be very heavy so I wanted to try to use the table in memory, the Oracle documentation is full but bad of small examples, if anyone knows a simple way I appreciate any help .

The example itself does not seem to be important, but the question is, how to read an array as a table in a SQL in a PLSQL block or object?

sql oracle oracle11g array pl-sql

asked by anonymous 25.05.2016 / 18:31

2 answers

Global variables do not update in view with AngularJS How do I change the color of the progressive bar when an error occurs?

score 2 · Answer 1

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

And the bulk collect already works.

score 1 · Answer 2

See 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;
/