As promised earlier, here is a simple demonstration of a Function that gets executed when a View is queried and does it's own DML. (Note : I have to use "pragma autonomous_transaction" for the DML).
The code :
The code :
SQL> drop table emp_view_log;
Table dropped.
SQL> drop table employees;
Table dropped.
SQL>
SQL> create table employees as select * from hr.employees;
Table created.
SQL>
SQL> create table emp_view_log
2 (log_dt_tm date,
3 session_id number,
4 session_user varchar2(30)
5 )
6 /
Table created.
SQL>
SQL> create or replace function log_emp_view_fn
2 return number
3 as
4 pragma autonomous_transaction;
5 begin
6 insert into emp_view_log
7 select sysdate, sys_context('USERENV','SID'), user from dual;
8 commit;
9 return null;
10 end;
11 /
Function created.
SQL>
SQL> create or replace view emp_view
2 as select employee_id, first_name, last_name, log_emp_view_fn
3 from employees
4 /
View created.
SQL>
What I have done is that I have created a table emp_view_log to log all executions of queries against the employees table. I have then created a function log_emp_view_fn to do the actual inserts into this log table. The view emp_view is then "published" to users.Users are presented a view that hides the SALARY column from the employees table. Yet, when they query this view, for every row they query, the log table captures the occurrence.
(Question : Why does the log table capture one row for every row queried from the view ?)
Let's see what happens when I query the emp_view view :
SQL> select * from emp_view where last_name = 'KING';
no rows selected
SQL> select * from emp_view_log;
no rows selected
SQL> select * from emp_view where last_name = 'Abel';
EMPLOYEE_ID FIRST_NAME LAST_NAME LOG_EMP_VIEW_FN
----------- -------------------- ------------------------- ---------------
174 Ellen Abel
SQL> select to_char(log_dt_tm,'DD-MON-RR HH24:MI:SS'), session_id, session_user
2 from emp_view_log;
TO_CHAR(LOG_DT_TM,'DD-MON-R SESSION_ID SESSION_USER
--------------------------- ---------- ------------------------------
29-JUN-13 08:11:28 37 HEMANT
SQL>
SQL>
SQL> rem wait a while ...
SQL> select * from emp_view where employee_id between 100 and 102;
EMPLOYEE_ID FIRST_NAME LAST_NAME LOG_EMP_VIEW_FN
----------- -------------------- ------------------------- ---------------
100 Steven King
101 Neena Kochhar
102 Lex De Haan
SQL> select to_char(log_dt_tm,'DD-MON-RR HH24:MI:SS'), session_id, session_user
2 from emp_view_log
3 order by log_dt_tm;
TO_CHAR(LOG_DT_TM,'DD-MON-R SESSION_ID SESSION_USER
--------------------------- ---------- ------------------------------
29-JUN-13 08:11:28 37 HEMANT
29-JUN-13 08:12:57 37 HEMANT
29-JUN-13 08:12:57 37 HEMANT
29-JUN-13 08:12:57 37 HEMANT
SQL>
The first query for employee 'KING' (instead of 'King') returned no rows so no rows were logged in the emp_view_log.The third query was for 3 rows from the emp_view view. So, emp_view_log created three entries.
.
.
.