error rep-1401 in formula column of report builder 2.1 [message #389360] |
Sun, 01 March 2009 09:33 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shahidmahmood2069
Messages: 11 Registered: February 2009
|
Junior Member |
|
|
I am trying to read number from table for a speciific date for which that number does not exit i.e return value is null. I am unable to handle it.
My code look like this
declare
vnum number;
begin
select pnum into vnum from XYZ where pdate=:P1_date
if pnum is null
return 0;
else return pnum;
end if;
end;
I also tried
declare
vnum number;
begin
select pnum into vnum from XYZ where pdate=:P1_date
exception when no_data_found then
return 0;
else return pnum;
end if;
end;
|
|
|
|
|
Re: error rep-1401 in formula column of report builder 2.1 [message #389379 is a reply to message #389360] |
Sun, 01 March 2009 12:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Oracle | REP-1401: '<program unit name>': Fatal PL/SQL error occurred.
Cause: This is an abnormal condition.
Action: For information, see Abnormal Conditions.
|
Is there, perhaps, any ORA-xxxxx message that follows this REP-1401? If so, which one?
The first PL/SQL block you've posted is correctly written. True, it doesn't handle any exception.
The second one, on the other hand, is syntactically incorrect - you can't have an ELSE - END IF without an IF. Besides, having IF-THEN-ELSE in an exception section as you've put it is wrong. If SELECT returns something, the code will not enter exception handling section and you'll end up with an error saying that function must return a value (while yours doesn't).
If the problem is in lack of data (as your sample code as well as Michel's guess suggest), something like this might give you an idea:DECLARE
vnum NUMBER;
BEGIN
SELECT pnum INTO vnum FROM XYZ WHERE pdate = :P1_date
RETURN (pnum);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN (0);
END;
On the other hand, you could try to do the same without EXCEPTION:DECLARE
vnum NUMBER;
BEGIN
SELECT NVL(MAX(pnum), 0) INTO vnum FROM XYZ WHERE pdate = :P1_date
RETURN (pnum);
END; Note that this is (most probably) WRONG way to do that - you'd better handle it correctly so that everyone knows what's going on. Because, NVL(MAX) works, but isn't as obvious as the first solution. Besides, it will obfuscate possible TOO-MANY-ROWS error so you might get wrong result and wonder what the heck is going on.
Therefore, remember the second option and use it while quickly testing certain statements, without paying too much attention to possible exceptions. Don't use it in production.
|
|
|