Skip to Main Content
  • Questions
  • How can I suppress the other error messages from PLSQL errors

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Bhavesh .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: August 01, 2022 - 3:09 pm UTC

Version:

Viewed 10K+ times! This question is

You Asked

When we use raise_application_error procedure in
exception part, the procedure returns customized as well as other
error message.
How can I suppress the other error message?
For example...
=======================================
SQL> execute error_mesg(60);
BEGIN error_mesg(60); END;

*
ERROR at line 1:
ORA-20001: No Department exists!
ORA-06512: at "BHAVESH.ERROR_MESG", line 18
ORA-06512: at line 1
=======================================

is the message I get in my procedure bhavesh.error_mesg.

I don't want all other ORA_065512. All I want in my error message
is ORA-20001 which we defined using raise_application_error package.

Thanks for the help.
Regards,
Bhavesh




and Tom said...


In SQLPlus, which is just a simple command line tool for doing simple things, you cannot. That is the way all plsql error messages are reported. The error stack is always returned, there is no avoiding it.

If you call the procedure from a 3gl, you have more control of course -- down to being able to edit the error message.


One approach that may work could be:

ops$tkyte@8i> create or replace procedure error_msg
2 as
3 begin
4 raise_application_error( -20001, 'Bummer' );
5 end;
6 /

Procedure created.

ops$tkyte@8i> set serveroutput on
ops$tkyte@8i> set feedback off

ops$tkyte@8i> begin
2 error_msg;
3 exception
4 when others then
5 dbms_output.put_line( sqlerrm );
6 end;
7 /
ORA-20001: Bummer


So, if you run the block as above and catch the error and just print out the error message, not the error_stack, you can get what you want. It will NOT be flagged as an error to the calling routine though which is bad.



Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

error ora-105100

vinodhps, July 14, 2003 - 3:14 am UTC

Hi Tom,
In my forms mail form login screen iam getting this error message as ora-105100 i could not able to trace the message,
it was also not availabe in oerr or any where in google too..

do you have any idea about that tom

Thanking you

Tom Kyte
July 14, 2003 - 9:20 am UTC

time to call support I suppose.

Some control available through TRUE/FALSE

andrew, July 14, 2003 - 2:55 pm UTC

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production

SQL> DECLARE
  2     x   NUMBER;
  3  BEGIN
  4     x := 1 / 0;
  5  EXCEPTION
  6     WHEN OTHERS THEN
  7        raise_application_error (-20501, 'Whoops!', FALSE);
  8  END;
  9  /
DECLARE
*
ERROR at line 1:
ORA-20501: Whoops!
ORA-06512: at line 7


SQL> DECLARE
  2     x   NUMBER;
  3  BEGIN
  4     x := 1 / 0;
  5  EXCEPTION
  6     WHEN OTHERS THEN
  7        raise_application_error (-20501, 'Whoops!', TRUE);
  8  END;
  9  /
DECLARE
*
ERROR at line 1:
ORA-20501: Whoops!
ORA-06512: at line 7
ORA-01476: divisor is equal to zero 

Tom Kyte
July 15, 2003 - 1:00 am UTC

that doesn't get rid of the stuff the original poster wanted to get rid of.

"i don't want the other ora-06512....."

Pure SQL solution

Donat Callens, July 29, 2022 - 7:27 am UTC

It is possible to achieve this in pure SQL by cheating a little and resorting to compilation flags. You output the message and set the flag. Then you raise the error based on the flag, but you remove the output first. You can expand this code for as many different errors as needed.
SET serveroutput ON feedback OFF
DECLARE
        my_number NUMBER;
        numeric_error EXCEPTION;
        PRAGMA exception_init ( numeric_error, -06502 );
BEGIN
        my_number := 'a';
        --
        dbms_output.put_line('This line is line never reached');
EXCEPTION
        WHEN numeric_error THEN
                dbms_output.put_line('ORA-20001: Please use a number!');
                EXECUTE IMMEDIATE q'~ALTER SESSION SET PLSQL_CCFLAGS = 'error_20001_raised:TRUE'~';
END;
/
WHENEVER SQLERROR EXIT sql.sqlcode -- ensures the exit code is set
SET termout OFF -- disables the output
BEGIN
       $IF $$error_20001_raised $THEN
        raise_application_error(-20001, 'Please use a number'); 
       $END
        NULL;
END;
/

Chris Saxon
July 29, 2022 - 3:30 pm UTC

I'm not following; that's all PL/SQL - how is this pure SQL? And how can you use this approach for PL/SQL in compiled packages/procedures/functions?

wrong wording

Donat Callens, August 01, 2022 - 6:40 am UTC

I'm not following; that's all PL/SQL - how is this pure SQL? And how can you use this approach for PL/SQL in compiled packages/procedures/functions?

I'm terribly sorry, I meant to say SQL*Plus. not just SQL. It is for SQL scripts usage only, not for compiled code.
Chris Saxon
August 01, 2022 - 3:09 pm UTC

Ahhh, that makes more sense :)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library