Re: FORALL and BULK COLLECT

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 3 Sep 2004 11:34:17 -0700
Message-ID: <130ba93a.0409031034.482f589d@posting.google.com>

Several problems with the way your are using dynamic SQL and BULK bind.

1. Local variables are not visible within dynamic SQL. You use bind variables.
2. You can not bind arrays with execute immediate. You use package arrays.
3. You can, if you are using 9i R2, use BULK with recrod type.

Try this..

SQL> desc t1;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER
 C2                                                 VARCHAR2(10)

SQL> desc t2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER
 C2                                                 VARCHAR2(10)

SQL> select count(1) from t1;

COUNT(1)

SQL> select count(2) from t2;

COUNT(2)

SQL>
SQL> create or replace package test_pkg as   2 type t1_row_type is table of t1%RowType;   3 t1_rows t1_row_type;
  4 end;
  5 /

Package created.

SQL>
SQL> begin
2 execute immediate 'select * from '||'t1' bulk collect into test_pkg.t1_rows; 3
4 -- Array processing, trasformation here

  5  for i in 1..test_pkg.t1_rows.count loop
  6    test_pkg.t1_rows(i).c1:=test_pkg.t1_rows(i).c1+10;
  7    test_pkg.t1_rows(i).c2:=test_pkg.t1_rows(i).c2||'*';

8 end loop;
9

 10  execute immediate 'begin '||
 11                    'forall i in 1..:a '||
 12                    'insert into '||'t2 '||'values test_pkg.t1_rows(i);'||
 13                    'end;' using test_pkg.t1_rows.count;

14 end;
15 /

PL/SQL procedure successfully completed.

SQL>
SQL> select count(2) from t2;

COUNT(2)

128

SQL> rollback;

Rollback complete.

SQL> drop package test_pkg;

Package dropped.

Jusung Yang

indra_prastha2000_at_yahoo.com (Indraprastha) wrote in message news:<484d9119.0408312123.6cdd879f_at_posting.google.com>...
> I was using the below procedure to copy bulk records from tab1(first)
> to tab2(second table) . When i executed this an error like
>
> ORA-03001: unimplemented feature
> ORA-06512: at "BPE101.INS_BULK1", line 14
> ORA-06512: at line 9
>
> occured. can anyone solve this problem?
>
>
>
> CREATE OR REPLACE PROCEDURE INS_BULK1(TAB1 VARCHAR2,TAB2 VARCHAR2)
>
> IS
>
> TYPE NUMS IS TABLE OF NUMBER;
>
> TYPE ENTERPRISE1 IS TABLE OF VARCHAR2(300);
>
> TYPE SITE1 IS TABLE OF VARCHAR2(3000);
>
> TYPE ORG1 IS TABLE OF VARCHAR2(3000);
>
> SCENUM NUMS;
>
> ENT ENTERPRISE1;
>
> SITEVAR SITE1;
>
> ORGVAR ORG1;
>
> BEGIN
>
> EXECUTE IMMEDIATE 'SELECT SCENARIO_ID,ENTERPRISE,SITE,ORG BULK COLLECT
> INTO SCENUM,ENT,SITEVAR,ORGVAR FROM '||TAB1;
>
> FORALL i IN 1..100000
>
> EXECUTE IMMEDIATE 'INSERT INTO
> '||TAB2||'(SCENARIO_ID,ENTERPRISE,SITE,ORG) VALUES
> (SCENUM('||I||'),ent('||I||'),SITEVAR('||I||'),ORGVAR('I||'))';
>
> END;
>
> /
Received on Fri Sep 03 2004 - 13:34:17 CDT