Search My Oracle Blog

Custom Search

29 June, 2013

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.

.
.
.

1 comment:

v @ oracle plsql forum said...

Never thought that we can call a function in a view. Thanks for sharing this example

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016