OraFAQ Forum: Forms

Home » Developer & Programmer » Forms » Ora-1403 (10g, xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Ora-1403 [message #540062]

Fri, 20 January 2012 01:31

Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan

Senior 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

Report message to a moderator

Re: Ora-1403 [message #540064 is a reply to message #540062]

Fri, 20 January 2012 01:40

ramoradba
Messages: 2456
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

Report message to a moderator

Re: Ora-1403 [message #540112 is a reply to message #540062]

Fri, 20 January 2012 05:31

azamkhan
Messages: 557
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;

Report message to a moderator

Re: Ora-1403 [message #540124 is a reply to message #540112]

Fri, 20 January 2012 06:21

Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You must be kidding, right?

SELECT raised NO-DATA-FOUND!

Report message to a moderator

Re: Ora-1403 [message #540127 is a reply to message #540124]

Fri, 20 January 2012 06:27

azamkhan
Messages: 557
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.

Report message to a moderator

Re: Ora-1403 [message #540135 is a reply to message #540127]

Fri, 20 January 2012 06:46

Littlefoot
Messages: 21813
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?

Report message to a moderator

Previous Topic:	FRM-92101 there was failure in the form server during startup
Next Topic:	Object Library Error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 11 16:23:25 CDT 2024