Search notes:

Reading nested SQL error messages in a PL/SQL exception handler

create procedure ORA_12081_PRC as
begin

   execute immediate q'{

      create table ora_12081_tab as
      select *
      from all_objects
      where
         last_ddl_time = to_date(sysdate, 'dd.mm.yyyy')

   }';

exception when others then

   dbms_output.put_line('sqlerrm:  ' || sqlerrm);

   for d in 1..utl_call_stack.error_depth loop
       dbms_output.put_line('errormsg: ' || utl_call_stack.error_msg(d));
   end loop;

end;
/

alter session set nls_date_format = 'yyyy-mm-dd';
alter session force parallel query parallel 4;
begin
   ORA_12081_PRC;
end;
/

drop procedure ORA_12081_PRC;
Github repository oracle-patterns, path: /PL-SQL/block/exception-handler/sqlerrm-sqlcode/nested/run.sql

TODO

This example should be used to demonstrate that such errors produce a different value for backtrace_depth and error_depth in utl_call_stack.

Index

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1759612693, '216.73.216.149', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/PL-SQL/block/exception-handler/sqlerrm-sqlcode/nested(69): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78