Home » Developer & Programmer » Reports & Discoverer » REP-1401 fatal PL/SQL error
REP-1401 fatal PL/SQL error [message #286210] Thu, 06 December 2007 21:39 Go to next message
Bibie
Messages: 5
Registered: December 2007
Junior Member
hello, im using this formula n facing problem..please help me..

function BeforeReport return boolean is
	V_SYSDT DATE;
begin
BEGIN
		SELECT SYSDT INTO V_SYSDT
		FROM CF97SPCD
		WHERE CRLINE = 'IM';
		:REP_DATE := V_SYSDT;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
	NULL;
	END;
	
  /*IF :V_FROMDT < :REP_DATE AND :V_TODT >= :REP_DATE THEN
  	SRW.SET_MAXROW('Q_1',0);
  	SRW.SET_MAXROW('Q_2',0);
  	SRW.SET_MAXROW('Q_5',0);
  	SRW.SET_MAXROW('Q_6',0);
  	:V_TIME := 'M';
  ELS*/IF :V_FROMDT < :REP_DATE AND :V_TODT < :REP_DATE THEN
  	SRW.SET_MAXROW('Q_1',0);
  	SRW.SET_MAXROW('Q_2',0);
  	SRW.SET_MAXROW('Q_3',0);
  	SRW.SET_MAXROW('Q_4',0);
  	:V_TIME := 'H';
  ELSIF --:V_FROMDT >= :REP_DATE AND 
  	:V_TODT >= :REP_DATE THEN
  	SRW.SET_MAXROW('Q_3',0);
  	SRW.SET_MAXROW('Q_4',0);
  	SRW.SET_MAXROW('Q_5',0);
  	SRW.SET_MAXROW('Q_6',0);
  	:V_TIME := 'F';
  ELSE
  	SRW.SET_MAXROW('Q_1',0);
  	SRW.SET_MAXROW('Q_2',0);
  	SRW.SET_MAXROW('Q_3',0);
  	SRW.SET_MAXROW('Q_4',0);
  	SRW.SET_MAXROW('Q_5',0);
  	SRW.SET_MAXROW('Q_6',0);
  	:V_TIME := NULL;
  END IF;
  return (TRUE);
end;


output is 'exact fetch returns more than requested number of rows'

Report message to a moderator

Re: REP-1401 fatal PL/SQL error [message #286217 is a reply to message #286210] Thu, 06 December 2007 23:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of reporting for a quick answer, you should read and follow OraFAQ Forum Guide.
You then see this must not be done.
You also see the "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
You also know that you have to post your Oracle version (4 decimals).

Regards
Michel

Report message to a moderator

Re: REP-1401 fatal PL/SQL error [message #286240 is a reply to message #286210] Fri, 07 December 2007 00:18 Go to previous messageGo to next message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Obviously, you'll need to trap TOO_MANY_ROWS (along with NO_DATA_FOUND), or further restrict the SELECT statement so that it doesn't return more than a single record from the 'CF97SPCD' table where 'crline' column value equals 'IM'. If all 'IM' records have the same value of the 'sysdt' column, you might use MIN(sysdt) or MAX(sysdt) as it would certainly return only one record; however, it is you who should know whether it is an acceptable way to do it or not.

Report message to a moderator

Re: REP-1401 fatal PL/SQL error [message #286708 is a reply to message #286240] Sun, 09 December 2007 23:33 Go to previous messageGo to next message
Bibie
Messages: 5
Registered: December 2007
Junior Member
The code now has been rewrite and the report now can be run. But the matter is the data are not coming out. Here is the code:
function BeforeReport return boolean is
 V_SYSDT DATE;
 cursor datas is
 select sysdt from cf97spcd where crline = 'IM';
 rec datas%rowtype;
begin
 open datas;
  fetch datas into V_SYSDT;
  if datas%notfound then
   null;
  end if;
  :REP_DATE := V_SYSDT;
 
IF :V_FROMDT < :REP_DATE AND :V_TODT < :REP_DATE THEN
   SRW.SET_MAXROW('Q_1',0);
   SRW.SET_MAXROW('Q_2',0);
   SRW.SET_MAXROW('Q_3',0);
   SRW.SET_MAXROW('Q_4',0);
   :V_TIME := 'H';
  ELSIF --:V_FROMDT >= :REP_DATE AND 
   :V_TODT >= :REP_DATE THEN
   SRW.SET_MAXROW('Q_3',0);
   SRW.SET_MAXROW('Q_4',0);
   SRW.SET_MAXROW('Q_5',0);
   SRW.SET_MAXROW('Q_6',0);
   :V_TIME := 'F';
  ELSE
   SRW.SET_MAXROW('Q_1',0);
   SRW.SET_MAXROW('Q_2',0);
   SRW.SET_MAXROW('Q_3',0);
   SRW.SET_MAXROW('Q_4',0);
   SRW.SET_MAXROW('Q_5',0);
   SRW.SET_MAXROW('Q_6',0);
   :V_TIME := NULL;
  END IF;
  return (TRUE);
  close datas;
end;


What is the problem now?Tq for ur attention.
[EDITED by LF: added [code] tags]

[Updated on: Mon, 10 December 2007 00:11] by Moderator

Report message to a moderator

Re: REP-1401 fatal PL/SQL error [message #286774 is a reply to message #286708] Mon, 10 December 2007 01:52 Go to previous message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You have already been suggested to read the OraFAQ Forum Guide. Please, do that before posting your next message. Pay attention to properly formatting your code (using the [code] tags) and use of IM speak (which is not welcome on this forum).

As of your problem: how much of PL/SQL programming do you know? You have opened a cursor and fetched only first record. True, you have solved the TOO-MANY-ROWS error, but I'm quite certain that result is not correct. If cursor returns more than one record (and your does), you should LOOP through it. "data%notfound" doesn't do much here as it will be false all the time. Also, RETURN exits this PL/SQL block so "close" statement isn't executed.

Furthermore, how much of SRW built-in package do you know? Did you ever bother to read what SRW.SET_MAXROW does?
Bibie

But the matter is the data are not coming out
How do you expect anything to appear if you explicitly ordered the report to return max 0 records for a query?

Report message to a moderator

Previous Topic: How to send output of report to a control file
Next Topic: report layout
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Jul 03 05:51:01 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.