Executing dynamic sql using forall [message #674824] |
Mon, 18 February 2019 15:06 |
|
gauravgautam135
Messages: 33 Registered: December 2013
|
Member |
|
|
I have this requirement of executing dynamic sql stored in associative array through FORALL.
Associative arrays will hold Insert scripts that needs to be executed in FORALL statement either using BULK-IN BIND or without it.
See sample code below:
Table Code:
CREATE TABLE sample_tab (col1 VARCHAR2(100), col2 VARCHAR2(200));
Anonymous Block:
DECLARE
TYPE tab_sql
IS
TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
t_tab_sql tab_sql;
BEGIN
FOR i IN 1 .. 5
LOOP
t_tab_sql(i) := 'INSERT INTO sample_tab (col1, col2) VALUES (''test'', '||i||')';
END LOOP;
FORALL indx IN 1 .. t_tab_sql.count
EXECUTE IMMEDIATE ':1' USING t_tab_sql(indx);
--INSERT INTO sample_tab VALUES ('test1', t_tab_sql(indx));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001, SQLERRM);
END;
/
When I run above code, it says invalid statement. However, when used the commented code "Insert into sample_tab" instead of Execute Immediate to check if my scripts are generating wrong, those seems to be fine.
I cannot use direct DML INSERT INTO in FORALL statement as table name for inserts are also dynamically generating.
Thanks for your help.
|
|
|
|
|
|
|
|