oracle - Exclude Extra Error Information from RAISE_APPLICATION_ERROR -

i have raised error message in pl/sql trigger , works fine except returns more error specify application. in addition custom error information line error happened on , trigger.

for example

 ora-20111: there custom error here  ora-06512: @ "{schema_name}.{trigger_name}", line 2  ora-04088: error during execution of trigger {schema_name}.{trigger_name}<br>  .operation canceled. 

what want is:

ora-20111: there custom error here 

how remove information error message before returned application? testing code below...

create or replace trigger custom_error before insert or update on {schema_name}.{table_name} each row begin     raise_application_error(-20111, 'there custom error here'); end;  

you use raise_application_error.

for example,

sql> declare   2    custom_err exception;   3    pragma exception_init( custom_err, -20099 );   4  begin   5    raise_application_error( -20099, 'this custom error' );   6  exception   7  when custom_err   8    dbms_output.put_line( sqlerrm );   9  end;  10  / ora-20099: custom error  pl/sql procedure completed.  sql> 

pragma exception_init give custom error number, vary in range -20001 -20999

however, need take care of already-defined exceptions being raised.

for example,

sql> declare   2    custom_err exception;   3    pragma exception_init( custom_err, -20099 );   4    v number;   5  begin   6    select empno v emp empno = 1234;   7    raise_application_error( -20099, 'this custom error' );   8  exception   9  when custom_err  10    dbms_output.put_line( sqlerrm );  11  end;  12  / declare * error @ line 1: ora-01403: no data found ora-06512: @ line 6   sql> 

there no_data_found exception thrown. suppress that, however, leave error stack additional messages.

sql> declare   2    custom_err exception;   3    pragma exception_init( custom_err, -20099 );   4    v number;   5  begin   6    select empno v emp empno = 1234;   7    raise_application_error( -20099, 'this custom error' );   8  exception   9  when no_data_found  10     raise_application_error( -20099, 'this custom error' );  11  when custom_err  12    dbms_output.put_line( sqlerrm );  13  end;  14  / declare * error @ line 1: ora-20099: custom error ora-06512: @ line 10   sql> 

i want log errors verbose possible. raise_application_error custom message displayed on application, however, log errors in program.

please have at:

• dbms_utility.format_error_stack
• dbms_utility.format_error_backtrace