dbg_example

dbg_example.sql

create table T_BOP_DSCR_STD_dbg as
   select 
      systimestamp tmp
     ,cast(null as varchar2(100)) bind_code
     ,s.sid, s.serial#
     ,t.*
   from T_BOP_DSCR_STD t, v$session s
   where 1=0;
create table T_BOP_DSCR_STD_dbg_trans
   as select 
         systimestamp tmp
        ,s.sid
        ,s.serial#
        ,t.* 
      from v$transaction t, v$session s
   where 1=0;

create or replace function fGetBopId_dbg
  (cBOP varchar2) return number result_cache relies_on (T_BOP_DSCR_STD) deterministic  
  as
    idBOP int;
  procedure dbg is
     pragma autonomous_transaction;
     my_sid int:=userenv('sid');
  begin
     insert into T_BOP_DSCR_STD_dbg
       select 
         systimestamp tmp
         ,cBOP
         ,s.sid, s.serial#
         ,t.*
       from T_BOP_DSCR_STD t, v$session s
       where s.sid=dbg.my_sid;
       
     insert into T_BOP_DSCR_STD_dbg_trans
       select 
          systimestamp tmp
         ,s.sid
         ,s.serial#
         ,t.* 
       from v$transaction t, v$session s
       where t.ses_addr = s.saddr and s.sid = my_sid;
     commit;
     execute immediate q'[alter session set tracefile_identifier='fGetBopId_dbg']';
     execute immediate q'[alter session set events 'immediate trace name MODIFIED_PARAMETERS']';
     execute immediate q'[alter session set events 'immediate trace name errorstack level 3']';
     execute immediate q'[alter session set tracefile_identifier='new']';
  end dbg;
  
  begin
    select/*+findme*/ t.ID into idBOP from T_BOP_DSCR_STD t where t.CODE = fGetBopId_dbg.cBOP;
    return idBOP;
  exception when NO_DATA_FOUND then
    dbg();
    --raise_application_error(-20000,LocalFrmt('Бизнес процесс "%0:s" не найден', vargs(cBOP), 'PKG', 'C_PKGBS_BOP'));
    raise;
  end;
/

dont_forget.sql

grant select on v_$session to &your_user;
grant select on v_$transaction to &your_user;
grant alter session to &your_user;