0

I'm trying to creating trigger on Oracle 12c. I'm using this trigger to capture the last query syntax on table "TEST" after update to store it in "TEST_LOL" table. But there error say "Error(6,3): PL/SQL: SQL Statement ignored" and "Error(6,106): PL/SQL: ORA-00942: table or view does not exist".

This is my trigger code:

CREATE OR REPLACE TRIGGER TRIGGER1 
AFTER UPDATE ON TEST
DECLARE
    original_query varchar2(500);
BEGIN
    select sql_text into original_query 
    from v$sql 
    where first_load_time=(select max(first_load_time) from v$sql);
    INSERT INTO TEST_LOL (ACTION) VALUES (original_query);
END;

Apparently there some error on syntax select sql_text into original_query from v$sql where first_load_time=(select max(first_load_time) from v$sql); It runs perfectly when I run it without trigger. Example, when I run this query code :

"UPDATE TEST SET name='indra' WHERE id =1"

then I run the capture query code :

select sql_text from v$sql where first_load_time=(select max(first_load_time) from v$sql) ;

Then it perfectly return the last query :

"UPDATE TEST SET name='indra' WHERE id =1"

Is there something wrong I'm missing ? Please help. Thank You

APC
  • 144,005
  • 19
  • 170
  • 281
Putu Indra Gunawan
  • 1
  • 1
  • Is you access to `v$sql` granted directly or via a role? [See this answer for more](http://stackoverflow.com/a/6670099/266304). This is probably a duplicate of that question, [or this one](http://stackoverflow.com/q/4198052/266304). – Alex Poole Mar 02 '17 at 15:20
  • Even if you get that to work, this won't work reliably because `select max(first_load_time)` is not specific to the user that made the trigger. `v$sql` contains statement from all connections to the database, including internal statements run by Oracle itself. –  Mar 02 '17 at 15:46
  • Thank for the reply. Thanks for the advice. After granting access to table v$sql it work. But just like @a_horse_with_no_name said sometimes it give query the Oracle run itself. Any idea how to fix this ? – Putu Indra Gunawan Mar 03 '17 at 06:55

0 Answers0