Home » SQL & PL/SQL » SQL & PL/SQL » doubt regarding exception handling
doubt regarding exception handling [message #280017] Mon, 12 November 2007 05:42 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
I have an important doubt regarding exception handling

supposing i declare a variable in my package spec
as follows:''

p_no_data_found EXCEPTION;

now in my package body, for a function, i say the following


 BEGIN
        SELECT user_data_xml,Status INTO o_userDataXML,o_Status
        FROM   tablemy
        WHERE  Ecus_user_id = n_UserID
        AND    Interface_id = n_InterfaceID;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            NULL; -- data may not be there always
    END;

 RETURN 0;
EXCEPTION
    WHEN p_missing_parameters THEN
        RAISE_APPLICATION_ERROR(-20001, 'Missing essential parameters in '||l_Prg_Name);
    WHEN p_no_data_found then
        RAISE_APPLICATION_ERROR(-20002,'No data found for '||n_userid||' and '||n_interfaceid);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20000, 'DB Error: '||SQLERRM||' IN '||l_Prg_Name);


meaning, though i am not raising it, i am handling it..
will it be a problem?
Re: doubt regarding exception handling [message #280020 is a reply to message #280017] Mon, 12 November 2007 05:53 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

there shouldn't be any problem.but if you are not using it then what's the use of declaring such an exception.how will the user know what's happening in your code.


regards,
Re: doubt regarding exception handling [message #280021 is a reply to message #280017] Mon, 12 November 2007 05:57 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
oh thats because i am using that variable in other procs
in the same package, so shall I safely remove that
p_no_data_found in the exception section?
Re: doubt regarding exception handling [message #280025 is a reply to message #280021] Mon, 12 November 2007 06:02 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


oracle already has a NO_DATA_FOUND exception defined.so use that instead of your defined exception(p_no_data_found).


regards,
Re: doubt regarding exception handling [message #280028 is a reply to message #280017] Mon, 12 November 2007 06:05 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
no i am not using no_data_found directly, cause in our
code base, we are following this standard of raising a variable
and then using that variable...

so for other procs in the same package, i am handling like

when p_no_data_found then
blah blah

so now do u think i should remove p_no_data_found in the above
mentioned case? as i have already handled in the body
saying that if no_data_found then NULL


Re: doubt regarding exception handling [message #280035 is a reply to message #280028] Mon, 12 November 2007 06:12 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
yes.
Quote:

code base, we are following this standard of raising a variable and then using that variable...


look out for pragma exception_init.

regards,
Re: doubt regarding exception handling [message #280051 is a reply to message #280028] Mon, 12 November 2007 06:48 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
There should not be an outer exception handler in this procedure. The only thing it does right now is obfuscate what went wrong and where.

Previous Topic: Help with Query
Next Topic: Should a table have result of calculated fields?
Goto Forum:
  


Current Time: Sat Dec 10 08:59:37 CST 2016

Total time taken to generate the page: 0.09166 seconds