BULK COLLECT and FORALL I have a task to tune a little bit of SQL. It's very ETL like, but the target is not (yet) a star schema, it's pretty much a table flattened out for reporting purposes.

As I reviewed it, I noticed it went row by row, with a COMMIT inside the LOOP. That's gotta go.

Can I do this in one SQL statement? No, there's other processing that needs to be done (UPDATE two other tables before and after). Hmmm...could I just return the appropriate records into a collection? I'll have to look at that to see if it's possible.

For now though, I am going to try and use BULK COLLECT with the LIMIT clause and FORALL for the processes that occur before and after.

Let's create some data:

CREATE TABLE t( x NUMBER, y NUMBER );

INSERT INTO t( x, y )
SELECT 
  TRUNC( dbms_random.value( 1, 99999999 ) ), 
  TRUNC( dbms_random.value( 1, 100000 ) )
FROM dual
  CONNECT BY level < 1001;

OK, now let's create an anonymous block, BULK COLLECTing the data from T into a PL/SQL table and then populated another table with that data:

DECLARE
  TYPE t_record IS TABLE OF T%ROWTYPE;
  l_table T_RECORD;
  CURSOR c
  IS
  SELECT x, y
  FROM t;
BEGIN
  OPEN c;

  LOOP
    FETCH c
    BULK COLLECT INTO l_table
    LIMIT 100;

    FORALL i IN 1..l_table.COUNT
      INSERT INTO s ( x, y )
      VALUES ( l_table(i).x, l_table(i).y );

    EXIT WHEN C%NOTFOUND;
  END LOOP;
  CLOSE c;
END;
/

And then I run it and I get the following:

ERROR at line 18:
ORA-06550: line 18, column 16:
PLS-00436: implementation restriction: cannot reference 
fields of BULK In-BIND table of records
ORA-06550: line 18, column 16:
PLS-00382: expression is of wrong type
ORA-06550: line 18, column 30:
PLS-00436: implementation restriction: cannot reference 
fields of BULK In-BIND table of records
ORA-06550: line 18, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 18, column 16:
PL/SQL: ORA-22806: not an object or REF
ORA-06550: line 17, column 7:
PL/SQL: SQL Statement ignored

A quick google search and I end up here .

So I can bulk bind, but I have to INSERT into the table as a whole. I can't be selective.

I updated my code to this:

DECLARE
  TYPE t_record IS TABLE OF T%ROWTYPE;
  l_table T_RECORD;
  CURSOR c
  IS
  SELECT x, y
  FROM t;
BEGIN
  OPEN c;

  LOOP
    FETCH c
    BULK COLLECT INTO l_table
    LIMIT 100;

    FORALL i IN 1..l_table.COUNT
      INSERT INTO s 
      VALUES l_table(i);

    EXIT WHEN C%NOTFOUND;
  END LOOP;
  CLOSE c;
END;
/

I run it and it completes successfully. This is all on XE, so I wonder (hope) that 11g will allow me to do what I want (I'll be working on an 11g RAC system). I scroll down the list of google results and I find this one which then takes me to AskTom. The first post demonstrates that my first attempt will work on 11g.

BULK COLLECT and FORALL are great tools if you can't do it in a single SQL statement and if you want to avoid the row by row processing.

Labels: development, howto, oracle, plsql, sql

  ¶ 6/05/2008 10:14:00 PM