Home » SQL & PL/SQL » SQL & PL/SQL » FORALL Bulk insert using variable (Oracle 12.1.0, Sun OS 5.11)
FORALL Bulk insert using variable [message #669706] Tue, 08 May 2018 22:02 Go to next message
Renovator
Messages: 1
Registered: May 2018
Junior Member
Hello,

I'm using a bulk insert to insert into a table. I want to use a variable in the insert column, while I'm using the execute_immediate it is throwing me the following error

Error report -
ORA-06550: line 12, column 63:
PLS-00440: FORALL bulk IN-bind variables cannot be used here
ORA-06550: line 12, column 1:
PL/SQL: Statement ignored
ORA-06550: line 12, column 1:
PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:


please guide me in this.



DECLARE
V_TABLE_NAME VARCHAR2(30):='ADD_UFI_TMP';
CURSOR INS_OPS IS SELECT * FROM INSERT_TMP;
TYPE INS_ARRAY IS TABLE OF INS_OPS%ROWTYPE;
INS_OPS_ARRAY INS_ARRAY;
BEGIN
OPEN INS_OPS;
LOOP
FETCH INS_OPS BULK COLLECT INTO INS_OPS_ARRAY LIMIT 1000;
FORALL I IN INS_OPS_ARRAY.FIRST..INS_OPS_ARRAY.LAST
--EXECUTE IMMEDIATE ('INSERT INTO '|| V_TABLE_NAME ||' VALUES '||INS_OPS_ARRAY(I)||');
EXECUTE IMMEDIATE 'INSERT INTO '|| V_TABLE_NAME ||' VALUES '||INS_OPS_ARRAY(I);
--EXECUTE IMMEDIATE 'INSERT INTO '|| V_TABLE_NAME ||' VALUES INS_OPS_ARRAY(I)' USING INS_OPS_ARRAY(I);
--INSERT INTO V_TABLE_NAME VALUES INS_OPS_ARRAY(I);
COMMIT;
EXIT WHEN INS_OPS%NOTFOUND;
END LOOP;
CLOSE INS_OPS;
END;
/
Re: FORALL Bulk insert using variable [message #669708 is a reply to message #669706] Tue, 08 May 2018 23:13 Go to previous messageGo to next message
BlackSwan
Messages: 26648
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

There really is a difference between SQL & PL/SQL.
Plains SQL can't use PL/SQL datatypes.
If you actually RTFM, you may notice that VALUES is always followed by "(" & ")"


EXECUTE IMMEDIATE
Into a single VARCHAR2 variable construct the whole SQL statement.
Use DBMS_OUTPUT to print the string before issuing execute immediate
Cut & Paste the statement into SQL*Plus to see exactly what is wrong & where.
Debug the statement using SQL*PLUS & then correct your SP.
Repeat as many time as necessary.
Re: FORALL Bulk insert using variable [message #669713 is a reply to message #669708] Wed, 09 May 2018 03:46 Go to previous messageGo to next message
cookiemonster
Messages: 13777
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't use an array to represent all the values in a dynamic statement like that.
Probably needs to be something like:
EXECUTE IMMEDIATE 'INSERT INTO '|| V_TABLE_NAME ||' VALUES (:1, :2, :3, <all others>)' USING INS_OPS_ARRAY(I).col1, INS_OPS_ARRAY(I).col2, INS_OPS_ARRAY(I).col3 ... <all others>;
Re: FORALL Bulk insert using variable [message #669765 is a reply to message #669713] Thu, 10 May 2018 19:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
The following is an example of what cookiemonster suggested.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE add_ufi_tmp AS SELECT * FROM dept WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE insert_tmp AS SELECT * FROM dept
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> DECLARE
  2    V_TABLE_NAME VARCHAR2(30):='ADD_UFI_TMP';
  3    CURSOR INS_OPS IS SELECT * FROM INSERT_TMP;
  4    TYPE INS_ARRAY IS TABLE OF INS_OPS%ROWTYPE;
  5    INS_OPS_ARRAY INS_ARRAY;
  6  BEGIN
  7    OPEN INS_OPS;
  8    LOOP
  9  	 FETCH INS_OPS BULK COLLECT INTO INS_OPS_ARRAY LIMIT 1000;
 10  	 FORALL I IN INS_OPS_ARRAY.FIRST..INS_OPS_ARRAY.LAST
 11  	   EXECUTE IMMEDIATE 'INSERT INTO '|| V_TABLE_NAME ||' VALUES (:1, :2, :3)'
 12  	     USING INS_OPS_ARRAY(I).deptno, INS_OPS_ARRAY(I).dname, INS_OPS_ARRAY(I).loc;
 13  	 COMMIT;
 14  	 EXIT WHEN INS_OPS%NOTFOUND;
 15    END LOOP;
 16    CLOSE INS_OPS;
 17  END;
 18  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM add_ufi_tmp
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.


Re: FORALL Bulk insert using variable [message #669766 is a reply to message #669765] Thu, 10 May 2018 19:44 Go to previous messageGo to next message
BlackSwan
Messages: 26648
Registered: January 2009
Location: SoCal
Senior Member
is OP a write one & return never poster?
Re: FORALL Bulk insert using variable [message #669767 is a reply to message #669765] Thu, 10 May 2018 19:54 Go to previous message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Another method:

SCOTT@orcl_12.1.0.2.0> CREATE TABLE add_ufi_tmp AS SELECT * FROM dept WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE insert_tmp AS SELECT * FROM dept
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> DECLARE
  2    V_TABLE_NAME VARCHAR2(30):='ADD_UFI_TMP';
  3  BEGIN
  4    EXECUTE IMMEDIATE
  5  	 'DECLARE
  6  	    CURSOR INS_OPS IS SELECT * FROM INSERT_TMP;
  7  	    TYPE INS_ARRAY IS TABLE OF INS_OPS%ROWTYPE;
  8  	    INS_OPS_ARRAY INS_ARRAY;
  9  	  BEGIN
 10  	    OPEN INS_OPS;
 11  	    LOOP
 12  	      FETCH INS_OPS BULK COLLECT INTO INS_OPS_ARRAY LIMIT 1000;
 13  	      FORALL I IN INS_OPS_ARRAY.FIRST..INS_OPS_ARRAY.LAST
 14  		INSERT INTO ' || V_TABLE_NAME || ' VALUES INS_OPS_ARRAY(I);
 15  	      COMMIT;
 16  	      EXIT WHEN INS_OPS%NOTFOUND;
 17  	    END LOOP;
 18  	    CLOSE INS_OPS;
 19  	  END;';
 20  END;
 21  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM add_ufi_tmp
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.
Previous Topic: Main Stored procedure to execute multiple procedures
Next Topic: Outer Join Question
Goto Forum:
  


Current Time: Sun Jan 19 22:41:48 CST 2020