TRIGGER for DDL

[Loganathan Perumal]

I have attached the trigger details in short and if you find any errors please let me know

set serveroutput on;

CREATE OR REPLACE TRIGGER ddl_trigger

BEFORE CREATE

ON DATABASE

DECLARE

    sql_text   DBMS_STANDARD.ora_name_list_t;

    v_stmt VARCHAR2(2000);

    n   PLS_INTEGER;

BEGIN

    IF (ora_dict_obj_type = 'ALL' ) AND (ora_sysevent = 'CREATE','ALTER','DELETE') THEN 

        n := ora_sql_txt(sql_text);

        FOR i IN 1..n LOOP

            v_stmt := v_stmt || sql_text(i);

        END LOOP;

        dbms_output.put_line('------------Query Used---------------');

        dbms_output.put_line(v_stmt);

    END IF;

END ddl_trigger;

/

show errors;

--

regards,

LOGANATHAN.P