Unable to get the sql command in Audit table

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. Received on Wed Oct 17 2007 - 10:37:23 CDT