Exception handling inside a function...

[sujark]

Hi,

I have a Stored Procedure like this:

Procedure VES_PROCS.ASDA
(p_cmpny_id IN char,
p_ret_code OUT number);
IS
BEGIN
Select
VA.Vndr_No as VendorNo ,
SGV.Vndr_Nm as VendorName ,
VA.Ref_No
Get_Terms( 'S ' , va.ref_no) as Terms,
from Vndr_Att VA , SG_Vendor SGV
where VA.SGV_No = SGV.SGV_No
and VA.cmpny_id = 'S '
order by vndr_no

P_ret_Code := 0;

EXCEPTION
WHEN Others THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
P_ret_code := 99;
END;


Get_Terms is a function that Concatenates the Terms into a string from another table:

Function GET_TERMS
( a_cmpny_id vndr_att.cmpny_id%type,
a_ref_no vndr_att.ref_no%type)
RETURN varchar2 IS

v_Terms_Str varchar2(30000);

BEGIN
FOR TermRec IN (SELECT T.Term_nm
FROM VES.Vendor_Terms VT,
VES.Terms T
WHERE (VT.Cmpny_ID = a_Cmpny_ID
AND VT.REF_NO = a_REF_NO
AND VT.Term_ID = T.Term_ID)
ORDER BY T.Term_Nm) LOOP
if v_Terms_str is null then
v_Terms_str := TermRec.Term_Nm ;
else
v_Terms_Str := v_Terms_Str || ', ' || TermRec.Term_Nm ;
end if;
END LOOP;

RETURN v_Terms_Str;

EXCEPTION
WHEN OTHERS THEN
Raise_Application_error(-20393,’Error Occurred in the Function’);
END;

I’m calling the above Stored Procedure from a VB Application.

If the function fails, is there a way to trap that error in the Stored Procedure that calls this function?

Right now,the Stored Procedure does not trap the error created by the function and still holds a 0 in the OUTPUT Paramter p_ret_code (may be because the function is embedded in a SELECT Statement). But after the stored procedure has been executed, when the control goes back to the VB application , it errors out in the application.

If the function fails, how to handle the errorin the stored procedure?

Your help will be greatly appreciated.

Thanks.

 

[sem]

I suppose that your problem is in using DBMS_OUTPUT.PUT_LINE in error handler. Since you call your procedure from VB, nobody reads or clears the buffer and its overflow produces an error. Regards, Dima

 

[sujark]

Thanks for the reply Dima. I removed the DBMS_OUTPUT Statement. Still doesn't work. When the function errors out, doesn't go to the exception area of the Stored Procedure and P_ret_code contains 0.

Thanks.

 

[Shantha]

The exception block should be written inside the for loop after your if condition. Once the exception occurs it will go to the exception handler and do the defined process in the handler and exits the function.

Hope this helps. Talent is what you possess;
genius is what possesses you

 

[Chao]

First of all, there are 2 main categories of errors in Oracle. They are system errors and user generated error. System errors are errors generated by the system, such as no_data_found. User generated errors are raised by an application when an error condition is met. This type of error is an error within the logic of the application. For instance, if the result of the function will return a null string and this is an error condition then before the function exits, it must checks for this condition. If the error condition is met, codes should be put in place to raise the exception.

In your example, no error is encounted, since returning a null string is not consider as an error in Oracle, hence, it will not generate an exception. If this is an error condition for the application then you must check for them and later indicate to the system that there is an "application" error.

To raise an exception for user defined errors you will have to do 3 things. First in the declaration section define the name of your exception label. Check for the error condition and when the error condition is met, use RAISE to raise an exception within the function. Then define what the system should do when this exception is raised. See the sample below.
i.e
IS
E_Empty_String EXCEPTION;
...
BEGIN
...
IF V_Return_String IS NULL Then
RAISE E_Empty_String;
END IF;
EXCEPTION
WHEN E_EMpty_String THEN
Raise_Application_Error (-20999, 'Error Message');
END;

 

[sem]

Your VES_PROCS.ASDA procedure contains syntax errors, so can not be executed at all. Regards, Dima