How do I record the oracle error in a pl/sql script? I have been to the oracle error handling documentation and I see the built in exceptions, but what if I do not know what the exception is? How can I log this in an exception block?
I want to do something like the below.
exception
when others then DBMS_OUTPUT.PUT_LINE(the error)
It's easier to create error logging database trigger with conditional logging, for example my actual error_logging trigger: https://github.com/xtender/xt_scripts/blob/master/error_logging/on_database.sql
create table ERROR_LOG
(
id NUMBER,
username VARCHAR2(30),
errcode INTEGER,
seq INTEGER,
tmstmp TIMESTAMP(6),
msg VARCHAR2(4000),
sql_text CLOB
)
/
create sequence err_seq
/
create or replace trigger trg_error_logging
after servererror
on database
disable
declare
v_id number := err_seq.nextval();
v_tmstmp timestamp:= systimestamp;
n int;
sql_text dbms_standard.ora_name_list_t;
v_sql_text clob;
begin
-- only if plsql_debug is set to TRUE:
for r in (select * from v$parameter p where p.name='plsql_debug' and upper(p.value)='TRUE') loop
v_sql_text:=null;
n := ora_sql_txt(sql_text);
for i in 1..n loop
v_sql_text := v_sql_text || sql_text(i);
end loop;
for i in 1.. ora_server_error_depth
loop
if i=1 then
insert into error_log(id,seq,tmstmp,username,errcode,msg,sql_text)
values( v_id, i, v_tmstmp, user, ora_server_error(i), ora_server_error_msg(i), v_sql_text);
else
insert into error_log(id,seq,tmstmp,username,errcode,msg)
values( v_id, i, v_tmstmp, user, ora_server_error(i), ora_server_error_msg(i) );
end if;
end loop;
commit;
end loop;
END;
/
select object_name,object_type,status from user_objects o where object_name='TRG_ERROR_LOGGING'
/
alter trigger trg_error_logging enable
/As you can see it logs all errors into the table ERROR_LOG, but only if session parameter plsql_debug is set to true. Obviously, you can change it to own parameters or conditions.
when othersis usually a bug. Let your client or application handle the exception, as it might not see (or know what to do with)dbms_outputtext.