SQL> DECLARE
   2   sql_text ora_name_list_t;
   3   n number;
   4   BEGIN
   5   n := ora_sql_txt(sql_text);
   6   END;
   7   /
DECLARE
*
ERROR at line  1 :
ORA- 03113 : end-of-file on communication channel

SQL> create or replace trigger before_truncate
   2   before truncate on database
   3   declare
   4   sql_text ora_name_list_t;
   5   n number;
   6   BEGIN
   7   n := ora_sql_txt(sql_text);
   8   FOR i IN  1 ..n LOOP
   9     dbms_output.put_line(sql_text(i));
  10   END LOOP;
  11   END;
  12   /

Trigger created.

SQL> connect scott
Enter password: *****
Connected.
SQL> set serveroutput on
SQL> truncate table empx;

Table truncated.

SQL> exec null;
truncate table empx

PL/SQL procedure successfully completed.

SQL> 

SQL> create or replace trigger before_insert_empx
   2   before insert
   3   on empx
   4   for each row
   5   declare
   6   sql_text ora_name_list_t;
   7   n number;
   8   BEGIN
   9   n := ora_sql_txt(sql_text);
  10   FOR i IN  1 ..n LOOP
  11     dbms_output.put_line(sql_text(i));
  12   END LOOP;
  13   END;
  14   /

Trigger created.

SQL> insert into empx select * from emp;
insert into empx select * from emp
insert into empx select * from emp
insert into empx select * from emp
insert into empx select * from emp
insert into empx select * from emp
insert into empx select * from emp
insert into empx select * from emp
insert into empx select * from emp
insert into empx select * from emp
insert into empx select * from emp
insert into empx select * from emp
insert into empx select * from emp
insert into empx select * from emp
insert into empx select * from emp

 14  rows created.

SQL> 

SQL> connect system
Enter password: *******
Connected.
SQL> CREATE OR REPLACE
   2     PACKAGE GLOBAL
   3       IS
   4            ERROR_STMT VARCHAR2( 4000 );
   5   END;
   6   /

Package created.

SQL> GRANT EXECUTE ON GLOBAL TO PUBLIC
   2   /

Grant succeeded.

SQL> CREATE PUBLIC SYNONYM GLOBAL FOR GLOBAL
   2   /

Synonym created.

SQL> connect scott
Enter password: 

SQL> CREATE TABLE ERROR_LOG(
   2                          PROGRAM  VARCHAR2( 30 ),
   3                          USERNAME VARCHAR2( 30 ),
   4                          STMT     VARCHAR2( 4000 )
   5                         )
   6   /

Table created.

SQL> GRANT INSERT,SELECT ON ERROR_LOG TO PUBLIC
   2   /

Grant succeeded.

SQL> CREATE PUBLIC SYNONYM ERROR_LOG FOR ERROR_LOG;

Synonym created.

SQL> connect scott
Enter password: *****
Connected.

SQL> CREATE OR REPLACE
   2     TRIGGER BEFORE_INSERT_EMP
   3       BEFORE INSERT
   4       ON EMP
   5       DECLARE
   6           SQL_TEXT ORA_NAME_LIST_T;
   7           N NUMBER;
   8       BEGIN
   9           GLOBAL.ERROR_STMT := NULL;
  10           N := ORA_SQL_TXT(SQL_TEXT);
  11           FOR I IN  1 ..N LOOP
  12             GLOBAL.ERROR_STMT := GLOBAL.ERROR_STMT || SQL_TEXT(I);
  13           END LOOP;
  14   END;
  15   /

Trigger created.

SQL> CREATE OR REPLACE
   2     PROCEDURE P1(X NUMBER)
   3     IS
   4     BEGIN
   5         IF X =  1 
   6           THEN
   7             INSERT INTO EMP SELECT * FROM EMP;
   8           ELSE
   9             INSERT INTO EMP SELECT * FROM EMP WHERE ROWNUM =  1 ;
  10         END IF;
  11   END;
  12   /

Procedure created.

SQL> SELECT * FROM ERROR_LOG
   2   /

no rows selected

SQL> BEGIN
   2       P1( 1 );
   3     EXCEPTION
   4       WHEN OTHERS THEN
   5         INSERT INTO ERROR_LOG VALUES('P1',USER,GLOBAL.ERROR_STMT);
   6   END;
   7   /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM ERROR_LOG
   2   /

PROGRAM                        USERNAME
 ------------------------------ ------------------------------
 
STMT
 --------------------------------------------------------------------------------
 
P1                             SCOTT
INSERT INTO EMP SELECT * FROM EMP


SQL> BEGIN
   2       P1( 2 );
   3     EXCEPTION
   4       WHEN OTHERS THEN
   5         INSERT INTO ERROR_LOG VALUES('P1',USER,GLOBAL.ERROR_STMT);
   6   END;
   7   /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM ERROR_LOG
   2   /

PROGRAM                        USERNAME
 ------------------------------ ------------------------------
 
STMT
 --------------------------------------------------------------------------------
 
P1                             SCOTT
INSERT INTO EMP SELECT * FROM EMP

P1                             SCOTT
INSERT INTO EMP SELECT * FROM EMP WHERE ROWNUM =  1 


SQL> 

SQL> merge into t using (select 'ABC' txt from dual) s on (1 = 1)
  2  when matched then update set t.txt = s.txt;
CURBOUND*2*69====> update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
CURBOUND*3*71====>  SELECT VALUE$ FROM SYS.PROPS$ WHERE NAME = 'OGG_TRIGGER_OPTIMIZATION'
CURBOUND*8*76====> merge into t using (select 'ABC' txt from dual) s on (1 = 1) when matched then update set t.txt = s.txt

5 rows merged.

SQL>