Home » SQL & PL/SQL » SQL & PL/SQL » Oracle10g (Exception Handilng for all parameters)
Oracle10g [message #398317] Thu, 16 April 2009 00:41 Go to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi To all,

Following is the function code with exception handling.
CREATE OR REPLACE FUNCTION Test 
     (p_gpname     global_parameter.parametername%TYPE, 
      p_companyid  global_parameter.companyid%TYPE) 
RETURN VARCHAR2 
AS 
  lv_gpname           global_parameter.parametername%TYPE; 
  e_input_validation  EXCEPTION; 
BEGIN 
  IF p_gpname IS NULL 
      OR p_gpname = '' 
      OR p_companyid IS NULL 
      OR p_companyid = 0 THEN 
    RAISE e_input_validation; 
  END IF; 
   
  SELECT gp.parametername 
  INTO   lv_gpname 
  FROM   global_parameter gp 
  WHERE  gp.parametername = p_gpname 
         AND gp.companyid = p_companyid; 
   
  RETURN ('$' 
          ||lv_gpname 
          ||'$'); 
EXCEPTION 
  WHEN no_data_found THEN 
    RETURN ('No Data Found In The Table.'); 
  WHEN e_input_validation THEN 
    RETURN ('ALL Parameters Must Be Entered..'); 
  WHEN OTHERS THEN 
    RETURN (SQLCODE 
            ||SQLERRM); 
END; 

-------------
I have to handle Excpetion if one of the parmeter value is not enterd. how to handle this type of exception

i used the following queries to run the above function.

select test(null,null)from dual;--it raise the exception

select test()from dual;--here it gives me error message but i have to handel exceptio how to handel this

[Mod-Edit: Frank added [code]-tags to improve readability]

[Updated on: Thu, 16 April 2009 00:53] by Moderator

Report message to a moderator

Re: Oracle10g [message #398318 is a reply to message #398317] Thu, 16 April 2009 00:42 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.


Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Re: Oracle10g [message #398319 is a reply to message #398317] Thu, 16 April 2009 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put a default value on your parameters and check it.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Give a meaningful title to your topics.

Regards
Michel
Re: Oracle10g [message #398326 is a reply to message #398317] Thu, 16 April 2009 00:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You can't do that in this way.
test() is not recognized by PL/SQL as being a call to your function, but to a function called test with no parameters.
There is no way you can catch this in your function.
Either adjust your calling app (the best way) or put your function in a package and overload it.
Re: Oracle10g [message #398328 is a reply to message #398318] Thu, 16 April 2009 01:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
BlackSwan wrote on Thu, 16 April 2009 07:42
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.


Post DDL for tables.
Post DML for test data.

Post expected/desired results.



Maybe you can help us here..
Maybe you can post what DDL or DML would have helped you helping the OP
Re: Oracle10g [message #398436 is a reply to message #398328] Thu, 16 April 2009 04:53 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Ok.I used Sql Formatter to format the code.But still it is not comming in the formated manner.

Ny problem is suppose function has 3 inputs at the time executing the function we entered the values for only 2 parameters at that time i have to raise the exception.
Re: Oracle10g [message #398442 is a reply to message #398436] Thu, 16 April 2009 05:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Use the DEFAULT clause on your function parameters to allow the function to be called even if not all of the parameters are specified, and then raise an exception from inside the function if one or more of the parameters have the default value.
Re: Oracle10g [message #398448 is a reply to message #398436] Thu, 16 April 2009 05:28 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sr_orcl wrote on Thu, 16 April 2009 11:53
Ok.I used Sql Formatter to format the code.But still it is not comming in the formated manner.

Ny problem is suppose function has 3 inputs at the time executing the function we entered the values for only 2 parameters at that time i have to raise the exception.

1/
Quote:
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
...use code tags ...
Use the "Preview Message" button to verify.


2/
Quote:
Put a default value on your parameters and check it [inside the function].


Regards
Michel

[Updated on: Thu, 16 April 2009 05:28]

Report message to a moderator

Previous Topic: Cursor ignoring a record in query result
Next Topic: Querying objects
Goto Forum:
  


Current Time: Mon Dec 05 08:58:22 CST 2016

Total time taken to generate the page: 0.05088 seconds