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: 7065
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 messageGo to next 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

Re: ORA-03001 while using bulk collect [message #678773 is a reply to message #224954] Thu, 09 January 2020 04:26 Go to previous messageGo to next message
asanthoshi@gmail.com
Messages: 1
Registered: January 2020
Junior Member
Thank you very much, its working
Re: ORA-03001 while using bulk collect [message #678774 is a reply to message #678773] Thu, 09 January 2020 06:30 Go to previous message
Solomon Yakobson
Messages: 3269
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.
Previous Topic: ORA-01031: insufficient privileges | Database level trigger
Next Topic: DML operation on a table
Goto Forum:
  


Current Time: Thu Mar 28 08:47:55 CDT 2024