CODEWITHSUNDEEP
oracle-databaseplsqlextend
SWDeveloper
SWDeveloper

Reputation: 339

how to append values to a plsql table

I have to get ID values from 3 different queries (Q1, Q2 and Q3 below) and append them to a plsql table so that I can use it later on using table(). But I am unable to "append" to the table. Can you please let me know how to go about this? I am in Oracle 12G. I dont want to union all the queries as in my production scenario, I need to pull from 15 different queries and dont want to write a huge one. And everytime, I "put" into this plsql table, the earlier data gets wiped and doesnt see to append. Any idea how to achieve this using plsql table ?

CREATE OR replace PACKAGE pkg AS
  TYPE customer_id_table_type IS TABLE OF all_customers.customer_id%TYPE;
  PROCEDURE process_customers;
END pkg;
/

CREATE OR replace PACKAGE BODY pkg AS

 PROCEDURE process_customers IS
  customer_id_table customer_id_table_type := customer_id_table_type();
  BEGIN 

  -- Q1
  SELECT customer_id BULK COLLECT
  INTO   customer_id_table
  FROM   old_customers cust
  WHERE  cust.last_update_date BETWEEN SYSDATE - 100 AND    SYSDATE;

  -- Q2
  SELECT customer_id BULK COLLECT
  INTO   customer_id_table
  FROM   new_customers cust
  WHERE  cust.last_update_date BETWEEN SYSDATE - 100 AND    SYSDATE;

  -- Q3
  SELECT customer_id BULK COLLECT
  INTO   customer_id_table
  FROM   archive_customers cust
  WHERE  cust.last_update_date BETWEEN SYSDATE - 100 AND    SYSDATE;

  FOR rec IN
  (
         SELECT customer_info
         FROM   all_customers acc
         WHERE  customer_id IN  (SELECT * FROM TABLE(customer_id_table)))
  LOOP
    dbms_output.put_line('customer info: '|| rec.customer_info);
  END LOOP;
 END process_customers;
END pkg;
/

Upvotes: 1

Views: 1175

Answers (1)

eaolson
eaolson

Reputation: 15092

So there's no BULK COLLECT APPEND or anything, but you can combine multiple nested tables with MULTISET UNION. So you could populate multiple nested tables and then combine them. I'm not sure what the performance would be compared to a single UNION SQL query, but I expect it would be much better than anything working in a loop.

So you'd do something like this:

SELECT customer_id BULK COLLECT
  INTO   customer_id_table_1
  FROM   old_customers cust
  WHERE  cust.last_update_date BETWEEN SYSDATE - 100 AND    SYSDATE;

SELECT customer_id BULK COLLECT
  INTO   customer_id_table_2
  FROM   new_customers cust
  WHERE  cust.last_update_date BETWEEN SYSDATE - 100 AND    SYSDATE;

customer_id_table_1 := customer_id_table_1 MULTISET UNION customer_id_table_2;

Upvotes: 2

Related Questions