CODEWITHSUNDEEP
sqloracle-databaseloggingstored-proceduresplsql
Depeche
Depeche

Reputation: 21

Procedure execution table

I have this procedure:

create or replace procedure tst
begin
    execute immediate 'create table tb1 select 1 col from dual';
    execute immediate 'create table tb2 select 1 col from dual';

    insert into tb3 select 1 from dual;

    execute immediate 'truncate table tb3';
    --error table not specified
    execute immediate 'create table tb4 select 1 col from ';
    execute immediate 'truncate table tb4';
end;

I need to create a table and enter into it all the operations performed inside the procedure. Something similar to this:

"txt"                       "dt"                  "name procedure"
procedure started           20.09.2021 15:12:11   tst
table tb1 created           20.09.2021 15:12:11   tst
table tb2 created           20.09.2021 15:12:12   tst
insert into tb3             20.09.2021 15:12:14   tst
truncate tab3               20.09.2021 15:12:16   tst
error table not specified   20.09.2021 15:12:16   tst

Thanks!

Upvotes: 0

Views: 61

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

You can create such a table

CREATE TABLE log_ops( txt VARCHAR2(400), dt DATE, proc_name VARCHAR2(40) );

and procedure having a nested procedure in order to log the related steps such as

CREATE OR REPLACE PROCEDURE tst AS
  v_ddl VARCHAR2(150);
  v_prc VARCHAR2(40) := $$PLSQL_UNIT;
  PROCEDURE pr_ins(i_txt VARCHAR2) IS
     PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
     INSERT INTO log_ops VALUES(i_txt,SYSDATE,v_prc);
     COMMIT;
   END; 
BEGIN
  pr_ins('procedure started');
  v_ddl := 'CREATE TABLE tb1 SELECT 1 col FROM dual';
  EXECUTE IMMEDIATE v_ddl;
  pr_ins('table tb1 created');
  
  v_ddl := 'CREATE TABLE tb2 SELECT 1 col FROM dual';  
  EXECUTE IMMEDIATE v_ddl;
  pr_ins('table tb2 created');

  INSERT INTO tb3 SELECT 1 FROM dual;
  pr_ins('insert into tb3');

  v_ddl := 'TRUNCATE TABLE tb3';  
  EXECUTE IMMEDIATE v_ddl;
  pr_ins('truncate tab3');

  v_ddl := 'CREATE TABLE tb4 SELECT 1 col FROM...';  
  EXECUTE IMMEDIATE v_ddl;
  pr_ins('error table not specified');

  v_ddl := 'TRUNCATE TABLE tb4';  
  EXECUTE IMMEDIATE v_ddl;
  pr_ins('truncate tab4');
 EXCEPTION WHEN others THEN pr_ins('error : '||sqlerrm);  
END;

Upvotes: 2

Koen Lostrie
Koen Lostrie

Reputation: 18790

This is one way you could do this:

tb3 needs to exist before tst is compiled, else procedure tst will never compile.

create table tb3 as select 1 col from dual;
create table tb_log
( id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, txt VARCHAR2(100)
, proc VARCHAR2(100)
, create_date DATE
);

Now create a procedure tst. Note that each of the statements that can error out have to be put in their own block and the exception need to caught. Else it will stop executing after the first error.

create or replace procedure tst
as
  procedure logit(txt_i tb_log.txt%TYPE)
  IS
  BEGIN
    INSERT INTO tb_log(txt, proc, create_date) 
      VALUES (txt_i, $$plsql_unit, sysdate);
  END logit;
begin
    logit('procedure started');
    BEGIN
      execute immediate 'create table tb1 as select 1 col from dual';
      logit('table tb1 created');
    EXCEPTION WHEN OTHERS THEN
      logit('table tb1 created ERROR: '||SQLERRM);
    END;
    BEGIN
      execute immediate 'create table tb2 as select 1 col from dual';
      logit('table tb2 created');
    EXCEPTION WHEN OTHERS THEN
      logit('table tb2 created ERROR: '||SQLERRM);
    END;
    
    -- this will fail if 
    insert into tb3 select 1 from dual;
    logit('insert into tb3 select 1 from dual');

    execute immediate 'truncate table tb3';
    logit('truncate tb3');
    --error table not specified
    BEGIN
      execute immediate 'execute immediate create table tb4 as select 1 col from';
      logit('table tb4 created');
    EXCEPTION WHEN OTHERS THEN
      logit('table tb4 created ERROR: '||SQLERRM);
    END;    
    BEGIN
      execute immediate 'truncate table tb4';
      logit('truncate table tb4');
    EXCEPTION WHEN OTHERS THEN
      logit('truncate table tb4 ERROR: '||SQLERRM);
    END;      
end;
/

Run it and check the results:

BEGIN
  tst;
END;
/

select * from tb_log;

1   procedure started   TST 20-SEP-2021
2   table tb1 created   TST 20-SEP-2021
3   table tb2 created   TST 20-SEP-2021
4   insert into tb3 select 1 from dual  TST 20-SEP-2021
5   truncate tb3    TST 20-SEP-2021
6   table tb4 created ERROR: ORA-00900: invalid SQL statement   TST 20-SEP-2021
7   truncate table tb4 ERROR: ORA-00942: table or view does not exist   TST 20-SEP-2021

Note the syntax errors in your code: CREATE TABLE tb1 SELECT 1 col1 FROM DUAL will raise the exception ORA-00922: missing or invalid option. The correct syntax is CREATE TABLE tb1 AS SELECT 1 col1 FROM DUAL (note the keyword AS)

Upvotes: 1

Related Questions