Home » SQL & PL/SQL » SQL & PL/SQL » Executing dynamic sql using forall (12c)
Executing dynamic sql using forall [message #674824] Mon, 18 February 2019 15:06 Go to next message
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.

Re: Executing dynamic sql using forall [message #674825 is a reply to message #674824] Mon, 18 February 2019 15:24 Go to previous messageGo to next message
BlackSwan
Messages: 26565
Registered: January 2009
Location: SoCal
Senior Member
Congratulations on implementing Worst Practice coding.

SQL statements or clause should NEVER be stored in table columns as "data".

You need to understand that SQL language is separate & distinct from PL/SQL and each is processed by its own "engine"

EXECUTE IMMEDIATE of an INSERT statement has no knowledge about PL/SQL datatypes; like "t_tab_sql".

The EXCEPTION handler is brain dead silly.
It should be removed, deleted & NEVER implemented again.
While it does RAISE an error, you have no idea which line produced the error.
Without the EXCEPTION handler, the same ROLLBACK occurs; plus you'll know the exact line/statement producing the error.

Re: Executing dynamic sql using forall [message #674826 is a reply to message #674825] Mon, 18 February 2019 15:41 Go to previous messageGo to next message
gauravgautam135
Messages: 33
Registered: December 2013
Member
Ok.

So I need to make sure that array holds the values rather than statement and try to put inserts directly into forall statements using the array for values.

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) := i;
  END LOOP;
  FORALL indx IN 1 .. t_tab_sql.count
  INSERT INTO sample_tab (col1, col2) values ('test', 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;
/
Re: Executing dynamic sql using forall [message #674827 is a reply to message #674826] Mon, 18 February 2019 15:48 Go to previous messageGo to next message
BlackSwan
Messages: 26565
Registered: January 2009
Location: SoCal
Senior Member
DECLARE
VAL1 VARCHAR2(7) := 'test';
BEGIN
  FOR i IN 1 .. 5
  LOOP
    INSERT INTO sample_tab (col1, col2) values (VAL1, i);
  END LOOP;
  COMMIT;
END;
/

[Updated on: Mon, 18 February 2019 17:23]

Report message to a moderator

Re: Executing dynamic sql using forall [message #674831 is a reply to message #674827] Mon, 18 February 2019 19:16 Go to previous messageGo to next message
BlackSwan
Messages: 26565
Registered: January 2009
Location: SoCal
Senior Member
>So I need to make sure that array holds the values rather than statement and try to put inserts directly into forall statements using the array for values.

Why are you obsessed with pumping values into an array, only to directly turn around and move those same values into a table row?
What is gained by adding the additional & needless overhead for abusing a memory based array?

Why is a simple single INSERT statement not acceptable?
Re: Executing dynamic sql using forall [message #674839 is a reply to message #674826] Tue, 19 February 2019 03:23 Go to previous message
John Watson
Messages: 7953
Registered: January 2010
Location: Global Village
Senior Member
Are you certain that you want to use pl/sql at all?
insert into sample_tab select 'test',rownum from dual connect by level <=5;
Previous Topic: Displaying rows as columns and grouping data
Next Topic: Using Window Function in a query Block - Count and Sum
Goto Forum:
  


Current Time: Fri Jul 19 09:29:51 CDT 2019