create or replace view numbers ("NUM") as select column_value from table(p.numbers) create or replace type number_tbl as table of number create or replace package p is function numbers return number_tbl; procedure insert_number (p_num in number); procedure delete_number (p_num in number); end; CREATE OR REPLACE TRIGGER HANDLE_INSERT_NUMBERS_TRG INSTEAD OF INSERT ON numbers FOR EACH row BEGIN p.insert_number( :new.num); END; CREATE OR REPLACE TRIGGER HANDLE_DELETE_NUMBERS_TRG INSTEAD OF DELETE ON numbers FOR EACH row BEGIN p.delete_number( :new.num); END; create or replace package body p is g_numbers number_tbl; procedure t(text in varchar2) is pragma autonomous_transaction; begin insert into log (txt) values (text); commit; end; function numbers return number_tbl is begin return g_numbers; end numbers; procedure insert_number(p_num in number) is begin g_numbers.extend; g_numbers( g_numbers.last):= p_num; end insert_number; procedure delete_number (p_num in number) is i integer; begin i := g_numbers.FIRST; WHILE i IS NOT NULL LOOP if g_numbers(i) = p_num then g_numbers.delete(i); exit; end if; i := g_numbers.NEXT(i); END LOOP; end delete_number; begin g_numbers := number_tbl(1,2,3,4,5,6,7,8,9,10); end; select * from numbers where num < 7 rem SYS create or replace function get_sql(p_query in varchar2) return sys_refcursor is l_query sys_refcursor ; begin open l_query for select vsq.sql_text from v$sql vsq join v$open_cursor vo on (vsq.address = vo.address) where vo.sid = sys_context('USERENV','SID') and upper(vsq.sql_text) like '%'||upper(p_query)||'%' and instr(vsq.sql_text, 'VSQ.SQL_TEXT') =0 order by LAST_SQL_ACTIVE_TIME desc ; return l_query; end; grant execute on get_sql to scott rem scott create or replace package body p is g_numbers number_tbl; procedure t(text in varchar2) is pragma autonomous_transaction; begin insert into log (txt) values (text); commit; end; function numbers return number_tbl is l_sql varchar2(5000); l_sql_query sys_refcursor:= sys.get_sql(user); begin loop fetch l_sql_query into l_sql; exit when l_sql_query%notfound; -- l_sql now contains the query executed against the NUMBERS view -- now is the time to make use of whatever the WHERE clause contains t(l_sql); exit; end loop; close l_sql_query; return g_numbers; end numbers; procedure insert_number(p_num in number) is begin g_numbers.extend; g_numbers( g_numbers.last):= p_num; end insert_number; procedure delete_number (p_num in number) is i integer; begin i := g_numbers.FIRST; WHILE i IS NOT NULL LOOP if g_numbers(i) = p_num then g_numbers.delete(i); exit; end if; i := g_numbers.NEXT(i); END LOOP; end delete_number; begin g_numbers := number_tbl(1,2,3,4,5,6,7,8,9,10); end;