CODEWITHSUNDEEP
oracle-databaseplsql
Arcs
Arcs

Reputation: 67

How to insert data into a PL/SQL table type rather than PL/SQL table?

I have a table TDATAMAP which has around 10 million records, I want to fetch all the records into a PL/SQL table type variable, match it with some criteria and finally insert all the required records in a staging table. Please tell me if its possible to do it using PL/SQL table typle variable and BULK INSERT/COLLECT . I am also concerned about the performance of the code.

Upvotes: 3

Views: 39489

Answers (2)

APC
APC

Reputation: 146239

" I want to fetch all the records into a PL/SQL table type variable, match it with some criteria and finally insert all the required records in a staging table."

This may well not be the right approach to take. SQL is a set-based programming language. It has features which allow us to identify and work with just the set of records we want.

So, instead of a three stage process - extract, filter, insert - celebrate the joy of sets:

insert into your_staging_table 
select whatver
from your_10m_table
where something = your_matching_criteria;

There will be scenarios when this is not good enough. But this is the approach to start with.

Upvotes: 8

Tony Andrews
Tony Andrews

Reputation: 132580

You can, but you probably should not, load 10 million records into memory at once - as long as there is sufficient memory to hold that much. Normally BULK COLLECT is used with the LIMIT clause to process a finite number of rows at a time e.g. 1000.

From the documentation:

The BULK COLLECT clause lets you fetch entire columns from the result set, or the entire result set at once. The following example, retrieves columns from a cursor into a collection:

DECLARE
   TYPE NameList IS TABLE OF emp.ename%TYPE;
   names NameList;
   CURSOR c1 IS SELECT ename FROM emp WHERE job = 'CLERK';
BEGIN
   OPEN c1;
   FETCH c1 BULK COLLECT INTO names;
   ...
   CLOSE c1;
END;

The following example uses the LIMIT clause. With each iteration of the loop, the FETCH statement fetches 100 rows (or less) into index-by table acct_ids. The previous values are overwritten.

DECLARE
   TYPE NumList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   CURSOR c1 IS SELECT acct_id FROM accounts;
   acct_ids NumList;
   rows NATURAL := 100;  -- set limit
BEGIN
   OPEN c1;
   LOOP
      /* The following statement fetches 100 rows (or less). */
      FETCH c1 BULK COLLECT INTO acct_ids LIMIT rows;
      EXIT WHEN c1%NOTFOUND;
      ...
   END LOOP;
   CLOSE c1;
END;

Upvotes: 7

Related Questions