| error rep-1401 in formula column of report builder 2.1 [message #389360] |
Sun, 01 March 2009 09:33  |
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  |
 |
Littlefoot
Messages: 21823
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;
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
