Home » SQL & PL/SQL » SQL & PL/SQL » ORA-03001 while using bulk collect
ORA-03001 while using bulk collect [message #224949] Fri, 16 March 2007 02:16 Go to next message
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 #224954 is a reply to message #224949] Fri, 16 March 2007 02:29 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Why are you using dynamic SQL? Bulk operations are supported in PL/SQL, they are no SQL feature. execute immediate will invoke the SQL engine. That's why you had your error. Here's a version without dynamic sql:
create or replace procedure test_bulk_collect
is
  v_value dbms_sql.varchar2_table;
begin
  select to_char(employee_id) 
  bulk collect into v_value 
  from employees 
  where rownum < 21;

  for i in v_value.first..v_value.last loop
    dbms_output.put_line(v_value(i));
  end loop;
end test_bulk_collect;
/

exec test_bulk_collect

drop procedure test_bulk_collect
/


And if you really think that you need to build the SQL string on runtime you can opt for a ref cursor:
create or replace procedure test_bulk_collect
is
  v_value dbms_sql.varchar2_table;
  TYPE curtype is ref cursor;
  v_refcur        curtype;
begin
  OPEN  v_refcur FOR 'select to_char(employee_id) x from employees where rownum < 21';
  FETCH v_refcur BULK COLLECT INTO v_value;
  CLOSE v_refcur;

  for i in v_value.first..v_value.last loop
    dbms_output.put_line(v_value(i));
  end loop;
end test_bulk_collect;
/
sho err

set serverout on
exec test_bulk_collect
drop procedure test_bulk_collect
/


Note: In most cases there is NO NEED for dynamic SQL. Really.

MHE
Re: ORA-03001 while using bulk collect [message #224957 is a reply to message #224954] Fri, 16 March 2007 02:40 Go to previous message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi Maaher,
thanks a lot for clearing my doubts.
it worked fine..

And i will follow your words of refcursor if using it during the run time.

Thanks again..

regards,
shanth
Cool

[Updated on: Fri, 16 March 2007 02:42]

Report message to a moderator

Previous Topic: PRAGMA AUTONOMOUS_TRANSACTION
Next Topic: OUTER JOINS
Goto Forum:
  


Current Time: Sat Dec 10 13:07:16 CST 2016

Total time taken to generate the page: 0.10576 seconds