Re: Unable to get the sql command in Audit table

On Oct 17, 10:37 am, nitesh <oracledba.nit..._at_gmail.com> wrote:
> Hi,
>
> I need to create a trigger, which should record any ddl changes in a
> schema. The output should be sent to an audit table in which it should
> show the sqlstatement, user and program. So i created an audit table
> and a trigger. In the audit table it is showing the user and program
> but not the sqlstatement. Here i am giving my approach..
>
> CREATE TABLE ddl_log (
>
> operation VARCHAR2(30),
>
> obj_owner VARCHAR2(30),
>
> object_name VARCHAR2(30),
>
> sql_text VARCHAR2(64),
>
> attempt_by VARCHAR2(30),
>
> attempt_dt DATE);
>
> CREATE OR REPLACE TRIGGER ddl_trigger
>
> BEFORE CREATE OR ALTER OR DROP
>
> ON SCHEMA
>
> DECLARE
>
> oper ddl_log.operation%TYPE;
>
> sql_text ora_name_list_t;
>
> i PLS_INTEGER;
>
> BEGIN
>
> SELECT ora_sysevent
>
> INTO oper
>
> FROM dual;
>
> i := sql_txt(sql_text);
>
> IF oper IN ('CREATE', 'DROP') THEN
>
> INSERT INTO ddl_log
>
> SELECT ora_sysevent, ora_dict_obj_owner,
>
> ora_dict_obj_name,NULL, USER, SYSDATE
>
> FROM dual;
>
> ELSIF oper = 'ALTER' THEN
>
> INSERT INTO ddl_log
>
> SELECT ora_sysevent, ora_dict_obj_owner,
>
> ora_dict_obj_name, sql_text, USER, SYSDATE
>
> FROM gv$sqltext
>
> WHERE UPPER(sql_text) LIKE 'ALTER%'
>
> AND UPPER(sql_text) LIKE '%NEW_TABLE%';
>
> END IF;
>
> END ddl_trigger;
>
> /
>
> please suggest.
>
> Thanks in advance.

In addition to Daniel's comment, I'd think you'd have a problem with this:

AND UPPER(sql_text) LIKE '%NEW_TABLE%';

will only be true if somewhere in sql_text you have the string 'NEW_TABLE', like

ALTER TABLE countries

   ADD (duty_pct     NUMBER(2,2),
         new_table varchar2(8),
        visa_needed  VARCHAR2(3));

What are the odds of actually having the string literal 'NEW_TABLE' in any ddl??? Received on Wed Oct 17 2007 - 15:18:24 CDT