inserting data using FOR ALL [message #497559] |
Sun, 06 March 2011 02:52 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ashish_sun123
Messages: 52 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68676 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 #497670 is a reply to message #497561] |
Sun, 06 March 2011 17:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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>
|
|
|