-
Exception Handling
Why is it that when I CATCH this EXCEPTION it ends my PROC, instead of continuing on to the next statement?
BEGIN
SELECT seats_held
INTO l_fcc_held
FROM flt_cabin_capacity
WHERE cabin = c_cabin.cabin
AND physical_flight_id = l_physical_flight_id
AND actual_depart_date = l_departure_date;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = 100 THEN --NO_DATA_FOUND
l_fcc_held := 0;
END IF;
END;
-
You're chosing to handle all exceptions, no matter what they are.
Would this work for you:
Code:
select Nvl(sum(seats_held),0) ...
That will always return a row, even if there are no rows that match the query ...
Code:
SQL> create table x (col1 number);
Table created.
SQL> select sum(col1) from x;
SUM(COL1)
----------
SQL> select Nvl(sum(col1),0) from x;
NVL(SUM(COL1),0)
----------------
0
-
Or, you could do this:
Code:
BEGIN
BEGIN
SELECT seats_held INTO l_fcc_held
FROM flt_cabin_capacity
WHERE cabin = c_cabin.cabin
AND physical_flight_id = l_physical_flight_id
AND actual_depart_date = l_departure_date;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_fcc_held := 0;
END;
-- Procedure Continues here...
END;
/
Or this:
Code:
BEGIN
SELECT seats_held INTO l_fcc_held
FROM flt_cabin_capacity
WHERE cabin = c_cabin.cabin
AND physical_flight_id = l_physical_flight_id
AND actual_depart_date = l_departure_date;
IF SQL%NOTFOUND
THEN
l_fcc_held := 0;
END IF:
-- Procedure Continues here...
END;
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Originally Posted by LKBrwn_DBA
Or this:
Code:
BEGIN
SELECT seats_held INTO l_fcc_held
FROM flt_cabin_capacity
WHERE cabin = c_cabin.cabin
AND physical_flight_id = l_physical_flight_id
AND actual_depart_date = l_departure_date;
IF SQL%NOTFOUND
THEN
l_fcc_held := 0;
END IF:
-- Procedure Continues here...
END;
No, that one won't do. That SQL%NOTFOUND part does not handle any exception, so NO_DATA_FOUND will be raised as unhandled exception by the server.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Hi.
I guess he meant this type of thing, but got lost in the moment:
Code:
PROCEDURE .......
CURSOR c IS
SELECT seats_held
FROM flt_cabin_capacity
WHERE cabin = c_cabin.cabin
AND physical_flight_id = l_physical_flight_id
AND actual_depart_date = l_departure_date;
BEGIN
OPEN c;
FETCH c INTO l_fcc_held;
IF c%NOTFOUND THEN
l_fcc_held := 0;
END IF:
CLOSE c;
-- Procedure Continues here...
END;
Cheers
Tim...
-
Thanks Tim.
Before reading your reply I ended up doing the following which is simliar to what you did
c_GenericCursor REFCUR.T_CURSOR;
BEGIN
OPEN c_GenericCursor FOR
SELECT seats_booked
INTO l_fcc_booked
FROM flt_cabin_capacity
WHERE cabin = c_cabin.cabin
AND physical_flight_id = l_physical_flight_id
AND actual_depart_date = l_departure_date;
IF c_GenericCursor%ROWCOUNT = 0 THEN
l_fcc_booked :=0;
END IF;
END;
The way I was doing it with the Begin-Statment-Exception-Statement-End should have worked, but since is was in a Loop-End Loop statement, the In-Line Exception Handling didn't work.
Is there are RESUME NEXT statement in Orcale, where is if there is an exception, handle it, then Resume to the next statement instead of ending the proc? I could not find anything and this is my first time using Oracle.
-
Originally Posted by sloesch
Is there are RESUME NEXT statement in Orcale, where is if there is an exception, handle it, then Resume to the next statement instead of ending the proc? I could not find anything and this is my first time using Oracle.
you achieve this by nesting the BEGIN-END statements ...
Code:
create procedure ...
Is
Declare
...
Begin
...
Begin
...
Exception
...
End
...
End;
/
-
I would stick with the SELECT INTO personally, and handle NO_DATA_FOUND etc as suggested above. The explicit ref cursor approach is just more complication.
-
So slimdave if I understand you right, for in-line exception handling I should do a END with out the (;) when I am doing something like this:
Create Procedure ...
Is
Declare
...
Begin
...
LOOP
...
FOR ... IN ...
LOOP
Begin
...
Exception
WHEN NO_DATA_FOUND THEN
... :=0;
End
...
End Loop;
...
END LOOP;
...
COMMIT;
Exception
WHEN OTHERS THEN
...
ROLLBACK;
End;
If my first Exception is TRUE handle it otherwise fall out and goto the Main Exception Raise Error and end PROC.
Last edited by sloesch; 09-30-2005 at 09:08 AM.
-
You need the semi-colon, but otherwise yes.
Here's a doc reference: http://download-west.oracle.com/docs...ems4.htm#32792
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|