A Function executing DML in a View

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 :

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.

.
.
.