I am trying to capture all the queries being executed on a table in the database. These queries will be stored in a table and will be later executed on a different database to keep both the databases synchronized. In the trigger we are using the below select statement to capture the query :
select sq.sql_text from v$open_cursor oc,v$sql sq where sid = sys_context('USERENV','SID') order
by sq.last_active_time desc ;
But, if the queries are being executed as prepared statements then the above query being used is capturing the sql statements as:
UPDATE <TABLE_NAME> SET COL1_NAME = :1 WHERE COL2_NAME =:2 AND COL3_NAME = :3
I am unable to get the values of the above fields in my query. Is there any other way that I can achieve this and get the complete query ?