Home » SQL & PL/SQL » SQL & PL/SQL » Raise Exception
Raise Exception [message #191894] Fri, 08 September 2006 09:11 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
In my PLSQL package I have a a
for j in (select......)
loop

end loop;

exception
end;

I am able to raise exceptions successfully within the loop whenever the select statement fetches some rows and control is passed onto within the loop.

I want to raise a NO_DATA_FOUND exception whenver the select statemt retrieves no rows. But it directly goes to the end of the program without raising any exceptions. How dO i trap this?

Regards,
Re: Raise Exception [message #191900 is a reply to message #191894] Fri, 08 September 2006 09:22 Go to previous messageGo to next message
ducasio
Messages: 38
Registered: November 2003
Member
can you send the piece of code that doesn't work?
Re: Raise Exception [message #191902 is a reply to message #191900] Fri, 08 September 2006 09:26 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
This is the part of the code..

procedure START_ARCHIVE
is
n_START NUMBER := dbms_utility.get_time;
n_CURRYEAR NUMBER := substr(SYSDATE,8,9);
n_CNT NUMBER;
e_RAISE_NO_TABLE EXCEPTION;
n_TABYEAR CHAR(2);
n_SRC1CNT NUMBER;
n_SRC2CNT NUMBER;
v_SELSTMT VARCHAR(1000);

begin
for j in (select distinct(substr(TIMSTAMP,8,9)) n_YEAR from SEOSDATA where trunc(TIMSTAMP,'YYYY') < trunc(SYSDATE,'YYYY'))
loop

begin

select
count(*)
into
n_CNT
from
DUAL
where exists
(select
TABLE_NAME
from
USER_TABLES
where
TABLE_NAME='SEOSDATA'||j.n_YEAR);


if (n_CNT = 0) then
n_TABYEAR := j.n_YEAR;
raise e_RAISE_NO_TABLE;
end if;


-- Insertion of Data according to Year in Backup Table.
execute immediate 'insert into SEOSDATA'||j.n_YEAR||
' select * from SEOSDATA
where to_char(substr(TIMSTAMP,8,9))='||j.n_YEAR||'
and substr(TIMSTAMP,8,9) < substr(SYSDATE,8,9)';
end;
end loop;

dbms_output.put_line('The archiving of SEOSDATA table completed in '||round((dbms_utility.get_time-n_START)/100,2)||' secs');


exception
when e_RAISE_NO_TABLE then
raise_application_error(-20001,'The table SEOSDATA'||n_TABYEAR||' does not exist!!! Create Manually and grant appr rights');
when NO_DATA_FOUND then
dbms_output.put_line('No rows are available');

end;
Re: Raise Exception [message #191908 is a reply to message #191894] Fri, 08 September 2006 09:57 Go to previous message
ducasio
Messages: 38
Registered: November 2003
Member
NO_DATA_FOUND is for select statement. You had a loop, so in this case you must put a counter inside and test the counter outside.

bye,
ducasio
Previous Topic: Procedure
Next Topic: merge with concatenation update
Goto Forum:
  


Current Time: Mon Dec 05 13:04:42 CST 2016

Total time taken to generate the page: 0.13761 seconds