Home » SQL & PL/SQL » SQL & PL/SQL » FORALL with EXECUTE IMMEDIATE
FORALL with EXECUTE IMMEDIATE [message #154886] Wed, 11 January 2006 02:52 Go to next message
vijusbox
Messages: 2
Registered: January 2006
Location: Bangalore
Junior Member

I am trying the following
FORALL l_loop_cntr IN 1..l_count
EXECUTE IMMEDIATE ' INSERT INTO ' ||c_table || ' VALUES l_NE_BILL_REPORTS_table (:1) ' USING l_loop_cntr;

my table name is dynamic.

but it gives the error
Compilation errors for PROCEDURE PL_OWNER.TEST_BULK

Error: PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL
Line: 27
Text: EXECUTE IMMEDIATE ' INSERT INTO ' ||c_table || ' VALUES l_NE_BILL_REPORTS_table (:1) ' USING l_loop_cntr;

thanks
vijen
Re: FORALL with EXECUTE IMMEDIATE [message #154929 is a reply to message #154886] Wed, 11 January 2006 06:50 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Try to use execute immediate the entire forall block like this


SCOTT@ORACLE> drop table myemp;

Table dropped.

SCOTT@ORACLE> create table myemp as select * from emp where 1=2;

Table created.

SCOTT@ORACLE> select * from myemp;

no rows selected

SCOTT@ORACLE>  declare
  2   p_str long;
  3   d_table varchar2(30) := 'MYEMP';
  4   begin
  5   delete from myemp;
  6   p_str := 'Declare type l_table is table of emp%rowtype;' ;
  7   p_str := p_str||' p_table l_table := l_table();' ;
  8   p_str := p_str||' Begin select * bulk collect into p_table from emp;';
  9   p_str := p_str||' forall i in 1..p_table.count';
 10   p_str := p_str||' insert into '||d_table||' values p_table(i); end;';
 11   execute immediate p_str;
 12   end;
 13  /

PL/SQL procedure successfully completed.



SCOTT@ORACLE> select empno,ename from myemp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

SCOTT@ORACLE>




Re: FORALL with EXECUTE IMMEDIATE [message #443856 is a reply to message #154929] Wed, 17 February 2010 12:10 Go to previous messageGo to next message
zario
Messages: 1
Registered: February 2010
Location: São Paulo
Junior Member

Hi Guys !! Can anyone help ?? I want to make dinamic SQL to use for any table, but oracle ask for SQL type, and doesn't accept ROWTYPE. It works for DELETE proc but doesn't for INSERT proc. It is causing due to to insert needs to collect more than 1 column data, so I wanted to create a ROWTYPE.

19/74    PLS-00457: express⌡es tem de ter tipos SQL


/*

CREATE TABLE parent (
part_num  NUMBER,
part_name VARCHAR2(15));

CREATE TABLE child AS
SELECT *
FROM parent;


DECLARE
 j PLS_INTEGER := 1;
 k parent.part_name%TYPE := 'Transducer';
BEGIN
  FOR i IN 1 .. 2000000
  LOOP
    SELECT DECODE(k, 'Transducer', 'Rectifier',
    'Rectifier', 'Capacitor',
    'Capacitor', 'Knob',
    'Knob', 'Chassis',
    'Chassis', 'Transducer')
    INTO k
    FROM dual;

    INSERT INTO parent VALUES (j+i, k);
  END LOOP;
  COMMIT;
END;
/

select count(*) from child;

select count(*) from parent;

*/


CREATE OR REPLACE PACKAGE manage_data_2 IS 

  	TYPE t_cursor IS REF CURSOR;

	PROCEDURE p_insert_data
	( v_sql VARCHAR2, history_table VARCHAR2 );

	PROCEDURE p_delete_data
	( v_sql VARCHAR2, v_table VARCHAR2);
	
		
END;	   
/

CREATE OR REPLACE PACKAGE BODY manage_data_2 IS

	PROCEDURE p_insert_data (v_sql VARCHAR2, history_table VARCHAR2) IS

		TYPE array_data IS TABLE OF parent%ROWTYPE;
		a_insert_data array_data;
		
		i manage_data_2.t_cursor;

	BEGIN

		OPEN i FOR v_sql;
		LOOP
			FETCH i BULK COLLECT INTO a_insert_data LIMIT 10000 ;
			
			FORALL x IN 1..a_insert_data.COUNT 
			
			--INSERT INTO parent  VALUES a_insert_data(x);  ------> THIS INSERT WORKS
			EXECUTE IMMEDIATE 'INSERT INTO ' || history_table || ' VALUES ' USING a_insert_data(x); ------- > DOESN'T ACCEPT ROWTYPE
			
			
			EXIT WHEN i%NOTFOUND;
		END LOOP;

		COMMIT;
		CLOSE i;

	END;  

	
	PROCEDURE p_delete_data (v_sql varchar2, v_table varchar2) IS

		TYPE array_data IS VARRAY(10000) OF ROWID ;
		a_delete_data array_data;

		d manage_data_2.t_cursor;
		
	BEGIN

		OPEN d FOR v_sql;
		LOOP
			FETCH d BULK COLLECT INTO a_delete_data LIMIT 10000;

			FORALL x IN 1..a_delete_data.COUNT
			EXECUTE IMMEDIATE 'DELETE FROM ' || v_table || ' WHERE rowid = :1' USING a_delete_data(x); ----> WORKS FOR DELETES NORMALLY (Only column ROWID);

			EXIT WHEN d%NOTFOUND;
		END LOOP;

		COMMIT;
		CLOSE d;

	END;  
	
END;



/

-- exec manage_data_2.p_insert_data('SELECT part_num, part_name FROM parent', null);

-- exec manage_data_2.p_delete_data('SELECT rowid from parent', 'parent');


I got to make this works like this works putting all inside a var but looks like a band aid ...
CREATE OR REPLACE PROCEDURE manage_data (v_sql VARCHAR2, v_table VARCHAR2, v_type_dml VARCHAR2) IS
	
	p_str 			long;
	p_array_type 	VARCHAR2(255);
	p_dml 			VARCHAR2(255);
	
	BEGIN
		
		p_str := 'DECLARE ';
		p_str := p_str || 'TYPE t_cursor 	IS REF CURSOR; ';		
		p_str := p_str || 'i t_cursor; ';

		IF v_type_dml = 'I' THEN
			p_array_type :=  'TYPE array_data IS TABLE OF ' || v_table || '%ROWTYPE; ';
			p_dml :=  '			INSERT INTO ' || v_table || '  VALUES a_manage_data(x);  ';
			
			ELSIF v_type_dml = 'D' THEN
				p_array_type :=  'TYPE array_data IS VARRAY(10000) OF ROWID; ';
				p_dml :=  '			DELETE FROM ' || v_table || '  WHERE rowid = a_manage_data(x);  ';
		END IF;	
		
		p_str := p_str || p_array_type;
		p_str := p_str || 'a_manage_data array_data; ';

		p_str := p_str || 'BEGIN ';

		p_str := p_str || '		OPEN i FOR ' || v_sql || ';';
		p_str := p_str || '		LOOP ';
		p_str := p_str || '		FETCH i BULK COLLECT INTO a_manage_data LIMIT 10000;  ';
			
		p_str := p_str || '		FORALL x IN 1..a_manage_data.COUNT  ';
			
		p_str := p_str || 			p_dml;		
		p_str := p_str || '			COMMIT; ';	
		
		p_str := p_str || '			EXIT WHEN i%NOTFOUND; ';
		p_str := p_str || '		END LOOP; ';

		p_str := p_str || '		COMMIT; ';
		p_str := p_str || '		CLOSE i; ';
		p_str := p_str || 'END; ';
		
		EXECUTE IMMEDIATE p_str;
		
		p_str := null;
		
	END;
/

THX IN ADVANCE !!
zario




CM: Added code tags, please do so yourself next time - see the orafaq forum guide if you're not sure how.

[Updated on: Wed, 17 February 2010 12:14] by Moderator

Report message to a moderator

Re: FORALL with EXECUTE IMMEDIATE [message #443859 is a reply to message #443856] Wed, 17 February 2010 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do this.
Just have a look at FORALL syntax and you will see it is not possible.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Wed, 17 February 2010 12:16]

Report message to a moderator

Re: FORALL with EXECUTE IMMEDIATE [message #443860 is a reply to message #154886] Wed, 17 February 2010 12:17 Go to previous message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not sure why you trying to use bulk collect here. It would be more efficient and easier to do the inserts as a straight insert as select.
Previous Topic: function raise no error when call in select.
Next Topic: Not much memory left for Oracle Process
Goto Forum:
  


Current Time: Fri Sep 30 15:44:59 CDT 2016

Total time taken to generate the page: 0.22520 seconds