Home » Developer & Programmer » Forms » Ora-1403 (10g, xp)
Ora-1403 [message #540062] Fri, 20 January 2012 01:31 Go to next message
Bilal Khan
Messages: 70
Registered: April 2010
Location: Pakistan
Member
Hello every body,
i create a form having progressive is filled. but for the first time when we dont have "EXP_DURING_REPORTING" value, i generate error message Ora-1403, i want that for the first time it should ignore the progressive value and after that it must take the value for next time.
code is as under.
declare
	a number;
	begin
	select progressive into a from budget_detail where trans_id=(select max(trans_id) from budget_detail where sub_code=:sub_code)OR TRANS_ID IS NULL; 
	:progressive:=:EXP_DURING_REPORTING+A;
	end;

Please check it out and inform me about it.

Regards
Re: Ora-1403 [message #540064 is a reply to message #540062] Fri, 20 January 2012 01:40 Go to previous messageGo to next message
ramoradba
Messages: 2452
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
when no data found make a as 0 or according to your requirement.
Read more about execption handling On Oracle Pl/sql user guide @tahiti.oracle.com
Re: Ora-1403 [message #540112 is a reply to message #540062] Fri, 20 January 2012 05:31 Go to previous messageGo to next message
azamkhan
Messages: 492
Registered: August 2005
Senior Member
Your code is fine. Just replace your following line:

:progressive:=:EXP_DURING_REPORTING+A;

with my following line:

:progressive := Nvl(:EXP_DURING_REPORTING, 0) + A;
Re: Ora-1403 [message #540124 is a reply to message #540112] Fri, 20 January 2012 06:21 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You must be kidding, right?

SELECT raised NO-DATA-FOUND!
Re: Ora-1403 [message #540127 is a reply to message #540124] Fri, 20 January 2012 06:27 Go to previous messageGo to next message
azamkhan
Messages: 492
Registered: August 2005
Senior Member
My dear friend Littlefoot. The problem is not in SELECT statment. The problem is in the variable used in the where clause ":EXP_DURING_REPORTING".

What I understood was that he said that this ":EXP_DURING_REPORTING" may has null value. But he want to ignore it if it is null.

Please correct me if I miss understood.
Re: Ora-1403 [message #540135 is a reply to message #540127] Fri, 20 January 2012 06:46 Go to previous message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I believe you did.

Once again: SELECT raised NO-DATA-FOUND, execution never reached
:progressive:= :EXP_DURING_REPORTING + A;
line. It doesn't matter that EXP_DURING_REPORTING is null - this statement wouldn't fail. The result would be NULL, but that's (as far as Oracle is concerned) is not an error.

Here's an example:
SQL> select ename, sal from emp order by ename;

ENAME             SAL
---------- ----------
ADAMS            1900
ALLEN            2400
BLAKE            3650
CLARK            3250
FORD             3800
JAMES            1750
JONES            3775
KING             5800
MARTIN           2050
MILLER           2100
SCOTT            3800
SMITH            1600
TURNER           2300
WARD             2050

14 rows selected.


PL/SQL code that fails (NO-DATA-FOUND!) because there's no 'Littlefoot' in a table:
SQL> declare
  2    exp_during_reporting number;
  3    retval number;
  4    l_sal  number;
  5  begin
  6    select sal
  7      into l_sal
  8      from emp
  9      where ename = 'Littlefoot';
 10
 11    retval := exp_during_reporting + l_sal;
 12
 13    dbms_output.put_line('Retval = ' || retval);
 14  end;
 15  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6


Next step: select an existing employee. Result is NULL (because EXP_DURING_REPORTING is NULL, by default), but code won't fail:
SQL> declare
  2    exp_during_reporting number;
  3    retval number;
  4    l_sal  number;
  5  begin
  6    select sal
  7      into l_sal
  8      from emp
  9      where ename = 'SCOTT';
 10
 11    retval := exp_during_reporting + l_sal;
 12
 13    dbms_output.put_line('Retval = ' || retval);
 14  end;
 15  /
Retval =

PL/SQL procedure successfully completed.


Finally, declare EXP_DURING_REPORTING with a default value (0 (zero)):
SQL> declare
  2    exp_during_reporting number := 0;
  3    retval number;
  4    l_sal  number;
  5  begin
  6    select sal
  7      into l_sal
  8      from emp
  9      where ename = 'SCOTT';
 10
 11    retval := exp_during_reporting + l_sal;
 12
 13    dbms_output.put_line('Retval = ' || retval);
 14  end;
 15  /
Retval = 3800

PL/SQL procedure successfully completed.


Got the idea?
Previous Topic: FRM-92101 there was failure in the form server during startup
Next Topic: Object Library Error
Goto Forum:
  


Current Time: Wed Oct 01 21:21:26 CDT 2014

Total time taken to generate the page: 0.08741 seconds