Schema F_54
Oracle Version 11.2.0.4.0
As Of 2022/09/19 13:58:07
Prior Trigger | Trigger TRG_ADM_UPGRADE_LOG_DDL | Next Trigger |
CREATE OR REPLACE TRIGGER trg_adm_upgrade_log_ddl after create or drop or rename on schema declare -- @version 0.2 2021-03-17 -- PRAGMA AUTONOMOUS_TRANSACTION; -- v_sql_text ora_name_list_t; v_liczba_linii pls_integer; v_oper_sql adm_upgrade_log.oper_sql%type := null; v_sqlcode number; v_sqlerrm varchar2(4000); begin if ora_dict_obj_type in ('FUNCTION', 'JAVA CLASS', 'SNAPSHOT', 'MATERIALIZED VIEW', 'PACKAGE' , 'PROCEDURE', 'SEQUENCE', 'TABLE', 'TYPE', 'VIEW') then begin v_liczba_linii := ora_sql_txt(v_sql_text); v_oper_sql := substr(v_sql_text(1), 1, 4000); exception when others then v_oper_sql := 'ERROR przy pobieraniu SQLa: ' || substr(SQLERRM, 1, 3000); end; -- insert into adm_upgrade_log(dict_obj_name, sysevent, dict_obj_type, dict_obj_owner, oper_sql, osuser, ip_address) values (ora_dict_obj_name, ora_sysevent, ora_dict_obj_type, ora_dict_obj_owner, v_oper_sql, sys_context( 'userenv', 'os_user'), sys_context( 'USERENV', 'IP_ADDRESS')) ; commit; end if; exception when others then v_sqlcode := sqlcode; v_sqlerrm := sqlerrm; -- wszystko poniżej jest po to, żeby trigger nie zależał właściwie od niczego ze schematu poza tabelą error_log insert all into error_log(data_wyst, modul, funkcja, miejsce_wyst, kod_bledu, opis_bledu, lvl, suser, addr) values(data_wyst, modul, funkcja, dbms_session.unique_session_id ||' | PARAMETRY', v_sqlcode , substrb('USER='||user ||chr(10)||'ORA_DICT_OBJ_OWNER='||ora_dict_obj_owner ||chr(10)||'ORA_DICT_OBJ_NAME='||ora_dict_obj_name ||chr(10)||'ORA_DICT_OBJ_TYPE='||ora_dict_obj_type ||chr(10)||'ORA_SYSEVENT='||ora_sysevent , 1, 500) , lvl, user, addr) into error_log(data_wyst, modul, funkcja, miejsce_wyst, kod_bledu, opis_bledu, lvl, suser, addr) values(data_wyst, modul, funkcja, dbms_session.unique_session_id ||' | ERROR_STACK', v_sqlcode , substrb(DBMS_UTILITY.FORMAT_ERROR_STACK(), 1, 500) , lvl, user, addr) into error_log(data_wyst, modul, funkcja, miejsce_wyst, kod_bledu, opis_bledu, lvl, suser, addr) values(data_wyst, modul, funkcja, dbms_session.unique_session_id ||' | BACKTRACE', v_sqlcode , substrb(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(), 1, 500) , lvl, user, addr) select sysdate as data_wyst , 'TRG_ADM_UPGRADE_LOG_DDL' as modul , 'TRG_ADM_UPGRADE_LOG_DDL' as funkcja , 2 lvl , sys_context('USERENV','ip_address') addr from dual; commit; dbms_output.put_line('Błąd przy próbie logowania DDL: ' || v_sqlerrm); END; / |
Object Owner | Object Name | Object Type |
---|---|---|
F_54 | Table | |
F_54 | ERROR_LOG | Table |
SYS | DBMS_STANDARD | Package |
SYS | DBMS_UTILITY | Package |
SYS | DBMS_SESSION | Package |
SYS | SYSEVENT | Function |
PUBLIC | DBMS_OUTPUT | Synonym |
SYS | DICTIONARY_OBJ_TYPE | Function |
SYS | DICTIONARY_OBJ_OWNER | Function |
SYS | DICTIONARY_OBJ_NAME | Function |
PUBLIC | ORA_SYSEVENT | Synonym |
PUBLIC | ORA_SQL_TXT | Synonym |
PUBLIC | ORA_DICT_OBJ_TYPE | Synonym |
PUBLIC | ORA_DICT_OBJ_OWNER | Synonym |
F_54 | ADM_UPGRADE_LOG | Table |
SYS | STANDARD | Package |
PUBLIC | DUAL | Synonym |
PUBLIC | DBMS_SESSION | Synonym |
PUBLIC | DBMS_UTILITY | Synonym |
PUBLIC | ORA_DICT_OBJ_NAME | Synonym |
Referencing objects |