| ORA 01403 [message #124348] |
Fri, 17 June 2005 11:33  |
sweetgeegee27
Messages: 107
Registered: June 2005
|
Senior Member |
|
|
I am able to run some reports with the following code. However, others return an ORA 01403 message with a fatal pl/sql error message. Can you tell me what may be wrong?
function CF_get_assessFormula return Number is
v_temp number := null;
begin
IF :b_arrears_flag != 'Y' THEN
select assess_no
into v_temp
from assessment
where tax_period_no = :b_tax_period_no
and assess_new_assess_no is null
and tax_payer_no = :b_taxpayeR_no
and tax_type_no = :b_tax_type_no
and rownum = 1;
END IF;
return (v_temp);
end;
[Updated on: Fri, 17 June 2005 11:34] Report message to a moderator |
|
|
|
| Re: ORA 01403 [message #124351 is a reply to message #124348] |
Fri, 17 June 2005 11:42  |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
ORA 01403 means "no data found".
In you PL/SQL it means what SQL query
select assess_no
from assessment
where tax_period_no = :b_tax_period_no
and assess_new_assess_no is null
and tax_payer_no = :b_taxpayeR_no
and tax_type_no = :b_tax_type_no
and rownum = 1;
can't find any record and can't assign assess_no to
v_temp variable.
You should anticipate this possibility in your code
and process it using the exception handler:
IF :b_arrears_flag != 'Y' THEN
begin
select assess_no
into v_temp
from assessment
where tax_period_no = :b_tax_period_no
and assess_new_assess_no is null
and tax_payer_no = :b_taxpayeR_no
and tax_type_no = :b_tax_type_no
and rownum = 1;
exception
when no_data_found then
v_temp := null; -- Or what you want
when others then
raise;
end;
END IF;
Rgds.
|
|
|
|
| Re: ORA 01403 [message #124353 is a reply to message #124351] |
Fri, 17 June 2005 12:03 |
sweetgeegee27
Messages: 107
Registered: June 2005
|
Senior Member |
|
|
Thank you so much dmitry. Although I had to change around a few things in the code, it did work. Thanks again. Here is the code below for your information:
function CF_get_assessFormula return Number is
v_temp number := null;
begin
IF :b_arrears_flag != 'Y' THEN
select assess_no
into v_temp
from assessment
where tax_period_no = :b_tax_period_no
and assess_new_assess_no is null
and tax_payer_no = :b_taxpayeR_no
and tax_type_no = :b_tax_type_no
and rownum = 1;
END IF;
return (v_temp);
RETURN NULL; exception
when Others then
return(null);
raise;
end;
|
|
|
|
| Re: ORA 01403 [message #124354 is a reply to message #124353] |
Fri, 17 June 2005 12:15 |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
Geegee,
I'm afraid I'm confused of this construction:
...
return (v_temp);
RETURN NULL;
exception
when Others then
return(null);
raise;
end;
because return(v_temp) (if everything is OK) means what
"RETURN NULL" will never be reached, and
return(null) also means what raise will never be reached too.
...
return (v_temp);
exception
when Others then
return(null);
end;
would be quite enought.
But consider if you return null for any exception,
you can fall into a trap then you will not be able to identify problems within a function. The best practice is to handle known
excpetions separately from others and re-raise others using
RAISE instruction.
But of course the last word is up to you.
Rgds.
|
|
|
|
| Re: ORA 01403 [message #124355 is a reply to message #124354] |
Fri, 17 June 2005 12:30 |
sweetgeegee27
Messages: 107
Registered: June 2005
|
Senior Member |
|
|
You're right. Thanks again. Here is the final script.
function CF_get_assessFormula return Number is
v_temp number := null;
begin
IF :b_arrears_flag != 'Y' THEN
select assess_no
into v_temp
from assessment
where tax_period_no = :b_tax_period_no
and assess_new_assess_no is null
and tax_payer_no = :b_taxpayeR_no
and tax_type_no = :b_tax_type_no
and rownum = 1;
END IF;
return (v_temp);
exception
when Others then
return(null);
end;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
