CODEWITHSUNDEEP
sqloracle-databaseplsql
Kotodid
Kotodid

Reputation: 1179

specifics of raise_application_error in trigger

I have strange beahvior of raise_application_error function in insert trigger comparing to plsql procedure. sqlerrm field get only error message in plsql error, but error text with stack trace from trigger. What is the reason?
(Oracle 10.2.0.4)

Here is test example.

First case: executing procedure from plsql: 

declare
  procedure p_e as
  begin
    raise_application_error(-20110, 'error from procedure');
  end;
 begin
  p_e;
exception
  when others then
    dbms_output.put_line(sqlerrm);
    dbms_output.put_line('========================================');
    dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

and see 

ORA-20110: error from procedure
========================================
ORA-06512: at line 4
ORA-06512: at line 7

Ok. This is what I expected.

Second case:
simple table

create table TT
(
  r NUMBER
);

and trigger

create or replace trigger t_tt
  before insert on tt  
  for each row
begin
  raise_application_error(-20110, 'error from trigger');
end t_tt;

Executing:

begin
  insert into TT
  values
    (0);
exception
  when others then
    dbms_output.put_line(sqlerrm);
    dbms_output.put_line('========================================');
    dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

end have such text:

ORA-20110: error from trigger
ORA-06512: at "GFU_PARUS.T_TT", line 2
ORA-04088: error during execution of trigger 'GFU_PARUS.T_TT'
========================================
ORA-06512: at "GFU_PARUS.T_TT", line 2
ORA-06512: at line 2

Upvotes: 1

Views: 1115

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

SQLERRM inside an exception handler will return the error code and nested messages of the error stack (up to 512 bytes).

So in both cases of your question, SQLERRM returns the error stack: the first case being limited to a single error.

Oracle recommends to use DBMS_UTILITY.FORMAT_ERROR_STACK if you want to retrieve the full error stack.

Upvotes: 2

Related Questions