| FORALL Bulk insert using variable [message #669706] |
Tue, 08 May 2018 22:02  |
 |
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 #669713 is a reply to message #669708] |
Wed, 09 May 2018 03:46  |
cookiemonster
Messages: 13966
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 #669767 is a reply to message #669765] |
Thu, 10 May 2018 19:54 |
 |
Barbara Boehmer
Messages: 9105
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
