Trigger to catch anonomys block pl/sql

[buckey]

I needs some help from some guru's. I have a schema level trigger that captures sql text. In a nut shell, it captures DDL changes. However, i need to capture the text contained in the anonomous pl/sql block.

As of now, i am using ora_sysevent to capture text based on weather it is an alter statment, and ora_obj_type to check the object type. However, i don't know how to check for anonomys block, let alone spell anonomys.

Please help!

Thanks in advance.

[davide++]

Hi all.

You should check V$SQLAREA that contains the text of commands executed on database.
Because PL/SQL anonymus block start with "DECLARE", you sholud do a query like



SELECT SQL_TEXT
FROM V$SQLAREA
WHERE SQL_TEXT LIKE 'DECLARE%'



I hope this will help you.

[buckey]

I am not sure about the triggering part. When a sysevent occurs such as create, alter, whatever then i capture that ddl. So what event will i check for to use the query you have posted? What will trigger this to capture the text?

Thanks,