-- Create table
create table T_DDL_LOG
(
  object_owner          VARCHAR2(150) not null,
  object_name           VARCHAR2(150) not null,
  object_type           VARCHAR2(150) not null,
  date_log              DATE not null,
  is_get_ddl_error_flag NUMBER not null,
  old_object_ddl        CLOB,
  ddl_command           CLOB
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 25M
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table T_DDL_LOG
  is 'Таблица изменений DDL объектов';
-- Add comments to the columns 
comment on column T_DDL_LOG.object_owner
  is 'Владелец объекта';
comment on column T_DDL_LOG.object_name
  is 'Имя объекта';
comment on column T_DDL_LOG.object_type
  is 'Тип объекта';
comment on column T_DDL_LOG.date_log
  is 'Дата создания/изменения записи';
comment on column T_DDL_LOG.is_get_ddl_error_flag
  is '1 - если попытка получения DDL объекта вызвала ошибку, в этом случае в  OLD_OBJECT_DDL записано сообщение об ошибке (например объекта не существовало), 0 - все ок';
comment on column T_DDL_LOG.old_object_ddl
  is 'DDL объекта ДО изменения';
comment on column T_DDL_LOG.ddl_command
  is 'команда DDL, вызвавшая изменение объекта';

CREATE OR REPLACE TRIGGER "TR_DDL_LOG"
  before DDL on ORGANIZ.SCHEMA
declare
  PRAGMA AUTONOMOUS_TRANSACTION;
  vl_old_object_ddl         CLOB;
  vl_ddl_command            CLOB;
  vl_is_get_ddl_error_flag  t_ddl_log.is_get_ddl_error_flag%TYPE := 0;
  vl_ora_dict_obj_name varchar2(200);
  vl_ora_dict_obj_owner varchar2(200);
  vl_ora_dict_obj_type varchar2(200);
  n NUMBER;
  sql_text ora_name_list_t;

BEGIN
  IF ora_dict_obj_name NOT IN ('TR_DDL_LOG', 'T_DDL_LOG') THEN
    BEGIN
      SELECT dbms_metadata.get_ddl(decode(ora_dict_obj_type, 'PACKAGE BODY', 'PACKAGE', ora_dict_obj_type), ora_dict_obj_name, ora_dict_obj_owner)
        INTO vl_old_object_ddl
        FROM dual;
    EXCEPTION
      WHEN OTHERS THEN BEGIN
        vl_is_get_ddl_error_flag := 1;
        vl_old_object_ddl := SQLERRM;
      END;
    END;

    n := ora_sql_txt(sql_text);
    FOR i IN 1..n LOOP
      vl_ddl_command := vl_ddl_command || sql_text(i);
    END LOOP;

    vl_ora_dict_obj_name := nvl(ora_dict_obj_name,'UNDEFINED');
    vl_ora_dict_obj_owner := nvl(ora_dict_obj_owner,'UNDEFINED');
  vl_ora_dict_obj_type := nvl(ora_dict_obj_type,'UNDEFINED');

    INSERT INTO t_ddl_log
      (object_owner, object_name, object_type, date_log, is_get_ddl_error_flag, old_object_ddl, ddl_command)
    VALUES
      (vl_ora_dict_obj_owner, vl_ora_dict_obj_name, vl_ora_dict_obj_type, SYSDATE, vl_is_get_ddl_error_flag, vl_old_object_ddl, vl_ddl_command);

    COMMIT;
  END IF;
end TR_DDL_LOG;