Home » Developer & Programmer » Reports & Discoverer » error rep-1401 in formula column of report builder 2.1
error rep-1401 in formula column of report builder 2.1 [message #389360] Sun, 01 March 2009 09:33 Go to next message
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 #389361 is a reply to message #389360] Sun, 01 March 2009 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: error rep-1401 in formula column of report builder 2.1 [message #389362 is a reply to message #389360] Sun, 01 March 2009 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
for which that number does not exit i.e return value is null.

If it does not exist an exception is returned: NO_DATA_FOUND.
Trap it in an exception block.

Second code is not correct, read (abd follow the link)
Database PL/SQL User's Guide and Reference
Chapter 1 Overview of PL/SQL
Section Understanding PL/SQL Block Structure

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
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
Littlefoot
Messages: 20901
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.
Previous Topic: Labels Rotation
Next Topic: How to I create a nested matrix (grouped column) report in oracle report builder?
Goto Forum:
  


Current Time: Thu Dec 08 14:39:29 CST 2016

Total time taken to generate the page: 0.09983 seconds