ORA-03001 while using bulk collect [message #224949] |
Fri, 16 March 2007 02:16 |
shanthkumaar
Messages: 156 Registered: February 2007 Location: india,chennai
|
Senior Member |
|
|
Hi experts,
i tried to create a procedure to learn the usage of bulk collect but i get the following error
oracle 9i version 9.2.0.1.0
procedure
SQL> create or replace procedure test_bulk_collect
2 is
3 begin
4 declare
5 v_var_execute varchar2(256);
6 v_value dbms_sql.varchar2_table;
7 begin
8 v_var_execute:='select emp_no bulk collect into v_value from emps';
9 execute immediate v_var_execute;
10 for i in v_value.first..v_value.last loop
11 dbms_output.put_line(v_value(i));
12 end loop;
13 end;
14 end test_bulk_collect;
15 /
Procedure created.
SQL> exec test_bulk_collect;
BEGIN test_bulk_collect; END;
*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "APPS.TEST_BULK_COLLECT", line 9
ORA-06512: at line 1
and when i searched for the error message i got this
ORA-03001 unimplemented feature:
Cause: At attempt was made to use a feature that is not currently implemented.
Action: Do not attempt to use this feature at this time.
Check the compatible parameter and the optimizer_features_enabled parameter for your instance and make sure they are both up to the 9i version you are running.
if iam not wrong bulk collect is supported in oracle 9i.
please guide me..
[Updated on: Fri, 16 March 2007 02:22] Report message to a moderator
|
|
|
|
|
|
Re: ORA-03001 while using bulk collect [message #678774 is a reply to message #678773] |
Thu, 09 January 2020 06:30 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Correct way if using dynamic sql:
SQL> create or replace
2 procedure test_bulk_collect
3 is
4 v_var_execute varchar2(256);
5 v_value dbms_sql.varchar2_table;
6 begin
7 v_var_execute:='select ename from emp';
8 execute immediate v_var_execute
9 bulk collect into v_value;
10 for i in v_value.first..v_value.last loop
11 dbms_output.put_line(v_value(i));
12 end loop;
13 end test_bulk_collect;
14 /
Procedure created.
SQL> set serveroutput on
SQL> exec test_bulk_collect
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|