Home » SQL & PL/SQL » SQL & PL/SQL » Exception messages (Oracle 10.2)
Exception messages [message #361047] Mon, 24 November 2008 23:31 Go to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hi All,

I have a requirement that the oracle procedure should raise an exception with the user defined error messages whenever any validations are failed.

Here is the sample procedure that throws an exception when the input argument is null.

create or replace PROCEDURE testException( a in varchar2) as
    INPUT_ARGUMENT_NULL EXCEPTION;
BEGIN
    IF a is null then
        raise INPUT_ARGUMENT_NULL;
    END IF;
    dbms_output.put_line('No Exception raised');
EXCEPTION
    WHEN INPUT_ARGUMENT_NULL THEN
       raise_application_error(-20001,'INPUT ARGUMENT PASSED AS NULL');
END testException;


Calling the procedure...

SQL> EXECUTE TESTEXCEPTION(NULL);

begin TESTEXCEPTION(NULL); end;

ORA-20001: INPUT ARGUMENT PASSED AS NULL
ORA-06512: at "PTTDEV01.TESTEXCEPTION", line 10
ORA-06512: at line 1



But here along with the user defined message "INPUT ARGUMENT PASSED AS NULL", it is also showing messages of "ORA-06512: at "PTTDEV01.TESTEXCEPTION", line 10" & "ORA-06512: at line 1"

What I need is it should simply give error message as either "ORA-20001: INPUT ARGUMENT PASSED AS NULL" but not the rest of the lines.

Can someone please let me know how to achieve it?

Thanks in advance.
prashas_d
Re: Exception messages [message #361055 is a reply to message #361047] Mon, 24 November 2008 23:44 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Quote:
What I need is it should simply give error message as either "ORA-20001: INPUT ARGUMENT PASSED AS NULL" but not the rest of the lines.



Then simply use DBMS_OUTPUT for diplaying while handling the error Or log to one error table rather than raising through

Quote:
raise_application_error(-20001,'INPUT ARGUMENT PASSED AS NULL');


Smile
Rajuvan
Re: Exception messages [message #361082 is a reply to message #361047] Tue, 25 November 2008 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No way with SQL*Plus which is designed to display the full stack but with your appication you can do what you want.

And don't follow Rajuvan's advice. A PL/SQL procedure is NOT for displaying, it is for processing. Client calling the procedure makes the display.

Regards
Michel
Re: Exception messages [message #361088 is a reply to message #361047] Tue, 25 November 2008 00:34 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

OP can can follow my advice for time being as this is Test Smile

Quote:
testException


OP can follow second part of my advice even in production.

Quote:
use DBMS_OUTPUT for diplaying while handling the error Or log to one error table



Smile
Rajuvan.


[Updated on: Tue, 25 November 2008 00:34]

Report message to a moderator

Re: Exception messages [message #361097 is a reply to message #361088] Tue, 25 November 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't react to "log into a table" because I think it is not directly relevant to the question which is "oracle procedure should raise an exception" and "showing message".

@prashas_d,
I add to my previous post that the caller only get the last exception (the one you raise_application_error) in SQL functions like SQLCODE or SQLERRM if the caller is PL/SQL code or equivalent in your language.

Regards
Michel
Re: Exception messages [message #361192 is a reply to message #361097] Tue, 25 November 2008 05:44 Go to previous message
prashas_d
Messages: 66
Registered: February 2007
Member

Thanks for the responses!!

It is the third party GUI application(developed by client) which is suppose to call my procedure.

We have informed them to use sqlerrm to get only the actual error message.

prashas_d.
Previous Topic: count in refcursor
Next Topic: ORA-12015: can not create fast refresh materialized view from complex query
Goto Forum:
  


Current Time: Fri Dec 09 21:09:49 CST 2016

Total time taken to generate the page: 0.05989 seconds