Home » RDBMS Server » Performance Tuning » inserting data using FOR ALL (9i/10g)
inserting data using FOR ALL [message #497559] Sun, 06 March 2011 02:52 Go to next message
ashish_sun123
Messages: 45
Registered: November 2010
Location: Bangalore
Member
 CREATE OR REPLACE procedure fast_proc (p_rows out number)
  is
   TYPE object_id_tab IS TABLE OF all_objects.object_name%TYPE INDEX BY BINARY_INTEGER
   lt_object_id                 object_id_tab;
   CURSOR c IS
   SELECT object_name
   FROM all_objects;
   BEGIN
       OPEN c;
    loop
       FETCH c BULK COLLECT INTO lt_object_id  ;
        FORALL i IN lt_object_id.FIRST .. lt_object_id.LAST
       insert into t2(object_name) values lt_object_id(i);
       EXIT WHEN c%NOTFOUND;
       END LOOP;
       CLOSE c;
  p_rows:=lt_object_id.LAST;
 end;
 /
Warning: Procedure created with compilation errors.

Errors for PROCEDURE FAST_PROC:

LINE/COL ERROR
-------- ---------------------------------------------------------
13/7     PL/SQL: SQL Statement ignored
13/22    PL/SQL: ORA-03001: unimplemented feature


I am not able to do INSERT but I am able to do UPDATE/DELETE? What is this inbuilt functionality?


[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Sun, 06 March 2011 16:53] by Moderator

Report message to a moderator

Re: inserting data using FOR ALL [message #497560 is a reply to message #497559] Sun, 06 March 2011 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58913
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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.

Use SQL*Plus and copy and paste your session, the WHOLE session, including SELECT * FROM V$VERSION.

ORA-03001: unimplemented feature
 *Cause:  This feature is not implemented.
 *Action:  None.


Regards
Michel


[Updated on: Sun, 06 March 2011 02:58]

Report message to a moderator

Re: inserting data using FOR ALL [message #497561 is a reply to message #497560] Sun, 06 March 2011 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 58913
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, I really don't see the interest to do over a simple INSERT SELECT, maybe you can tell us.

Regards
Michel

[Updated on: Sun, 06 March 2011 03:01]

Report message to a moderator

Re: inserting data using FOR ALL [message #497670 is a reply to message #497561] Sun, 06 March 2011 17:35 Go to previous message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
The error is due to missing parentheses around lt_object_id(i) in your values clause of your insert statement. You are also missing a semicolon after binary_integer. Please see the minimally corrected code below.

SCOTT@orcl_11gR2> CREATE TABLE t2 AS
  2  SELECT object_name
  3  FROM   all_objects
  4  WHERE  1 = 2
  5  /

Table created.

SCOTT@orcl_11gR2> CREATE OR REPLACE procedure fast_proc
  2  	(p_rows OUT NUMBER)
  3  AS
  4  	TYPE object_id_tab IS TABLE OF all_objects.object_name%TYPE
  5  				       INDEX BY BINARY_INTEGER;
  6  	lt_object_id		       object_id_tab;
  7  	CURSOR c IS
  8  	SELECT object_name
  9  	FROM   all_objects;
 10  BEGIN
 11  	OPEN c;
 12  	LOOP
 13  	  FETCH c BULK COLLECT INTO lt_object_id;
 14  	  FORALL i IN lt_object_id.FIRST .. lt_object_id.LAST
 15  	    INSERT into t2 (object_name) VALUES (lt_object_id(i));
 16  	  EXIT WHEN c%NOTFOUND;
 17  	END LOOP;
 18  	CLOSE c;
 19  	p_rows := lt_object_id.LAST;
 20  END fast_proc;
 21  /

Procedure created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> VARIABLE g_rows NUMBER
SCOTT@orcl_11gR2> EXECUTE fast_proc (:g_rows)

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> PRINT g_rows

    G_ROWS
----------
     57485

SCOTT@orcl_11gR2> SELECT COUNT (*) FROM t2
  2  /

  COUNT(*)
----------
     57485

1 row selected.

SCOTT@orcl_11gR2>

Previous Topic: performance tuning
Next Topic: SQL Tuning
Goto Forum:
  


Current Time: Thu Aug 28 00:28:21 CDT 2014

Total time taken to generate the page: 0.19551 seconds