Home » SQL & PL/SQL » SQL & PL/SQL » Issue with BULK COLLECT and NO_DATA_FOUND
Issue with BULK COLLECT and NO_DATA_FOUND [message #352792] Thu, 09 October 2008 11:07 Go to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
I have a table raks_1 with following structure

CREATE TABLE raks_1
(version_no   NUMBER,
 rowcount     NUMBER,
 volume       NUMBER
);


The table symom_staging_data_all is just a table having a few values and can be ignored

I am facing issue with the following code unit

declare
type r1 is record
  (l_ver   raks_1.version_no%type,
   l_cnt   raks_1.rowcount%type,
   l_vol   raks_1.volume%type
  );
type t1 is table of r1;
t_tmp t1;
e_exp exception;
begin
begin
select 1,10,20 bulk collect into t_tmp from symom_staging_data_all where 1=2;
exception
  when no_data_found then
    dbms_output.put_line('In exception 1');
    raise e_exp;
end;

for i in t_tmp.first..t_tmp.last loop
    dbms_output.put_line('Value of Row ' ||i||' : '|| t_tmp(i).l_vol);
end loop;
exception
  when e_exp then
    dbms_output.put_line('In exception 2');
end;


Here i want to the code to go into the WHEN NO_DATA_FOUND exception. This One
exception
  when no_data_found then
    dbms_output.put_line('In exception 1');
    raise e_exp;

and then subsequently into
exception
  when e_exp then
    dbms_output.put_line('In exception 2');


But instead it goes to the FOR loop and i get a error saying

ORA-06502: PL/SQL: numeric or value error


Re: Issue with BULK COLLECT and NO_DATA_FOUND [message #352794 is a reply to message #352792] Thu, 09 October 2008 11:17 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You are trying to refer a collection which is not initialised. If you read the pl/sql reference manual chapter 5 you will understand how to capture it. Moreover bulk collect and no_data_found don't go very well. Check this link for more information.

http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html

Regards

Raj
Previous Topic: Associative Arrays and nested tables
Next Topic: Q-quote mechanism
Goto Forum:
  


Current Time: Wed Dec 07 10:54:31 CST 2016

Total time taken to generate the page: 0.22207 seconds