CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloper
Potatooo
Potatooo

Reputation: 57

How do I insert multiple rows into temporary tables?

I am using variable to store multiple rows and I want to insert it into temporary table. This query returns multiple rows

  BEGIN 
    SELECT id INTO 
    temp_var 
    FROM TABLE_1 a,
         TABLE_2 b
    where a.id =b.id; 
     EXCEPTION
      WHEN NO_DATA_FOUND THEN
        temp_var := NULL;
      END;

I want to insert variable values into temporary table?

Upvotes: 0

Views: 1273

Answers (1)

APC
APC

Reputation: 146269

Assumption: when you say "temporary table" you mean the SQL Server usage, which is a PL/SQL collection in Oracle.

Here a variable temp_var is a nested table with a composite structure which matches the projection of table_1. 

declare
   type table1_nt is table of table_1%rowtype; 
   temp_var table1_nt;
begin
   select *
   bulk collection into temp_var
   from table_1;
   ....
end;

This gives you the contents of table_1 in an array which you can work with in PL/SQL. Find out more.

Given your revised requirement, it's easy enough to work with a single attribute instead:

declare
   type id_nt is table of varchar2(20);  -- table_1.id%type
   temp_var id_nt;
begin
   select a.id
   bulk collection into temp_var
   from table_1 a,
       join table_2 b
          on a.id =b.id; ;
   ....
end;

Upvotes: 1

Related Questions