FORALL and BULK COLLECT

[Indraprastha]

 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;

/

[Ganesh Raja]

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;
/

A Version would havebeen helpful ... :)

[Christian Antognini]

**** Post for FREE via your newsreader at post.usenet.com ****

Hi

The code you write contains different mistakes and, in my opinion, makes no
sense as well!
Why you don't use a single INSERT AS SELECT statement?

Chris

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;
/

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

[Jusung Yang]

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)
----------
32

SQL> select count(2) from t2;

COUNT(2)
----------
96

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

Post by Indraprastha
 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;
/