Re: Problems in DDL trigger usage.

"A. Sriram" <asrcmurthy_at_gmail.com> wrote in message news:1117436191.492958.134060_at_g49g2000cwa.googlegroups.com...
> Hi ALL,
>
> I have a problem in the usage of DDL trigger. I created a DDL trigger
> on a user schema on "AFTER CREATE" event to catch the creation script
> of the all database objects using the system variables ora_sysevent,
> ora_dict_obj_name, ora_dict_obj_type,ora_dict_obj_owner. And I am
> storing all the collected information in a audit table.
>
> For ora_dict_obj_type = 'TABLE', I used all_tab_columns and all_tables
> to generate the create script.
>
> For ora_dict_obj_type = 'VIEW', I used all_views to generate the create
> script.
>
> For ora_dict_obj_type = 'TRIGGER', I used all_triggers to generate the
> create script.
>
> For ora_dict_obj_type IN ('PROCEDURE','PACKAGE','FUNCITON','PACKAGE
> BODY'), I used all_source to generate the create script.
>
> But the problem is creation script is getting generated only when the
> ora_dict_obj_type = 'TABLE' and for all the other objects,
> no_data_found execption is being raised.
> i.e. if a view is crated,
> ALL_VEIWS is not getting the text for the below query:
> select text
> into l_extra
> from all_views
> where view_name = ora_dict_obj_name
> and owner=user;
> But same query is giving correct output when executed at sql prompt.
>
> Can anyone please suggest why for the object_type like VIEW,
> TRIGGER...etc, the sql text is not populated.
>
> Thanks in Advance.
>
> Srirama Chandra Murthy A
>

have you considered just using ora_sql_text?

From the Application Developer's Guide - Fundamentals:

ora_sql_txt (sql_text out ora_name_list_t)

returns BINARY_INTEGER

Returns the SQL text of the triggering statement in the OUT parameter. If the statement is long, it is broken up into multiple PL/SQL table elements. The function return value specifies how many elements are in the PL/SQL table.

++ mcs Received on Mon May 30 2005 - 16:35:19 CEST