Parameterized Views in Oracle? No problem! With SQL macros!

Views have always been an efficient tool for encapsulating complex logic, creating defined access structures and so on. But there is one thing views cannot do: accept parameters. And this is unfortunately a big disadvantage in terms of flexibility. As a result, there are a number of workarounds, none of them without their drawbacks.

I will use a little bit complicated example but the one that have a real practical application in the domain of data warehousing and ETL. It’s about historizing all changes according to slowly changing dimensions type II principle. My colleague Philipp Savlisberg has posted a historized version of the famous SCOTT schema to look at joining temporal intervals. He also provides a script to create this schema and data.

To simplify the queries, two types of the views are often placed on top of the versioned tables: the one to query a version of the data at any point in time and the other to query all versions within a time interval.

Let’s look at the data for one employee (EMPNO=7788):

SQL> SELECT empvid, empno, valid_from, valid_to, ename, sal 
FROM empv 
WHERE empno = 7788

    EMPVID      EMPNO VALID_FROM VALID_TO  ENAME           SAL
---------- ---------- ---------- --------- -------- ----------
         8       7788 19-APR-87  31-DEC-89 SCOTT          3000
        22       7788 01-JAN-90  31-MAR-91 Scott          3000
        36       7788 01-APR-91  31-DEC-99 Scott          3300

3 rows selected.

Listing 1: keeping all changes in a historized EMP table

Would be great, if we could get a view with the following query:

SELECT * 
FROM   empv 
WHERE  p_eff_date BETWEEN valid_from  AND valid_to

Listing 2: the desired query for the view

From my point of view, the best known workaround is that a view is defined using SYS_CONTEXT calls instead of the desired parameters. You have to create a user context and set the variables your view should use before querying the view. That alone is quite inconvenient. But another drawback is that you are always using bind variables then. Yes, I said that! Bind variables are good most of the time but not always. I’m doing a lot of ETL and I would often prefer literals over binds.

-- create package and context 
...
-- create view
CREATE VIEW emp_hist_v AS 
SELECT * 
FROM   empv
WHERE  SYS_CONTEXT('MY_VIEW_CONTEXT','EFF_DATE')
       BETWEEN valid_from AND valid_to;
       
-- Set the context variable
...        

-- Query is now possible 
SELECT * FROM emp_hist_view;

Listing 3: simplified description of SYS_CONTEXT workaround

Another one is to use a package and package variables as parameters. Define the setter and getter methods and use the getter methods instead of parameters in your view. Well, the drawbacks are the same: you have to set the variables first and you cannot switch to literals too.

CREATE OR REPLACE PACKAGE emp_hist_pkg IS

g_eff_date date;

PROCEDURE set_eff_date(p_date IN DATE);
FUNCTION get_eff_date RETURN DATE DETERMINISTIC PARALLEL_ENABLE;

END;
/ 

CREATE VIEW emp_hist_v AS 
SELECT * 
FROM   empv
WHERE  emp_hist_pkg.get_eff_date BETWEEN valid_from AND valid_to;

--  set parameter
BEGIN 
   emp_hist_pkg.set_eff_date(DATE '1990-01-01');
END;   

-- Query is now possible 
SELECT * FROM emp_hist_view;

Listing 4: simplified description of a workaround with package variables

Yet another popular option is not having a view at all, but rather to query a pipelined function which acts as view and can accept parameter. But again, mixing SQL and PL/SQL means you have to keep context switches in mind.

Okay, you might ask, what’s the problem here? Just query the view with the desired parameters as WHERE predicates and they will hopefully be pushed down into the view query. It may be or maybe not, depending of how complex your view is. And, first of all, you have to expose those columns from the view to be able to filter on them. What if your view has a scalar subquery and that’s where you want to introduce a parameter? How simple would it be just to return another column from a scalar subquery? Not at all! Maybe you have to rewrite the whole query because of that.

SQL Macros

Let’s again look at the query, we would like to implemet as a view:

SELECT * 
FROM   empv 
WHERE  p_eff_date BETWEEN valid_from  AND valid_to

Listing 5: the desired query for the view

Well, we know, with a plain view it is just not possible. How to solve the task with a SQL macro? First, we need a table macro (default), because we will use it in the FROM clause. Then just take the query from above and put it into a SQL macro function:

CREATE OR REPLACE FUNCTION emp_view (p_eff_date date) 
RETURN VARCHAR2 SQL_MACRO IS
v_query varchar2(500);
BEGIN
  v_query := q'!SELECT * 
                FROM empv 
                WHERE  p_eff_date between valid_from  AND valid_to!';
  RETURN v_query;
END;
/

SQL> SELECT empvid, empno, valid_from, valid_to, ename, sal  
FROM   emp_view(DATE '1990-02-01') 
WHERE  empno = 7788

    EMPVID      EMPNO VALID_FROM VALID_TO  ENAME           SAL
---------- ---------- ---------- --------- -------- ----------
        22       7788 01-JAN-90  31-MAR-91 Scott          3000
1 row selected.

DECLARE
  l_clob CLOB;
BEGIN
  DBMS_UTILITY.expand_sql_text (
    input_sql_text  => q'!SELECT * 
FROM   emp_view(DATE '1990-02-01') 
WHERE  empno = 7788!',
    output_sql_text => l_clob  );
  DBMS_OUTPUT.put_line(l_clob);
END;
/

SELECT "A1"."EMPVID" "EMPVID","A1"."EMPNO" "EMPNO","A1"."ENAME" "ENAME","A1"."JOBNO" "JOBNO","A1"."MGR" "MGR","A1"."HIREDATE" "HIREDATE","A1"."SAL" "SAL","A1"."COMM" "COMM","A1"."DEPTNO" "DEPTNO","A1"."VALID_FROM" "VALID_FROM","A1"."VALID_TO" "VALID_TO" 
FROM  (SELECT "A2"."EMPVID" "EMPVID","A2"."EMPNO" "EMPNO","A2"."ENAME" "ENAME","A2"."JOBNO" "JOBNO","A2"."MGR" "MGR","A2"."HIREDATE" "HIREDATE","A2"."SAL" "SAL","A2"."COMM" "COMM","A2"."DEPTNO" "DEPTNO","A2"."VALID_FROM" "VALID_FROM","A2"."VALID_TO" "VALID_TO" 
FROM  (SELECT "A3"."EMPVID" "EMPVID","A3"."EMPNO" "EMPNO","A3"."ENAME" "ENAME","A3"."JOBNO" "JOBNO","A3"."MGR" "MGR","A3"."HIREDATE" "HIREDATE","A3"."SAL" "SAL","A3"."COMM" "COMM","A3"."DEPTNO" "DEPTNO","A3"."VALID_FROM" "VALID_FROM","A3"."VALID_TO" "VALID_TO" 
FROM "ONFTEST"."EMPV" "A3" 
WHERE TO_DATE(' 1990-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')>="A3"."VALID_FROM" AND 
TO_DATE(' 1990-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<="A3"."VALID_TO") "A2") "A1" WHERE "A1"."EMPNO"=7788

Listing 6: create and query SQL macro

That’s it! It looks like we were quering a function, just like in a workaround with pipelined functions, but the difference is huge. The function will run only at parse time and two substitutions are made then. First, the parameter reference will be substituted in the return string of a function. Second, the return string of the function (now with a literal instead of a parameter) will be substitued in the query text instead of a function call. Using DBMS_UTILITY.expand_sql_text, we can see the query actually running. No context switch at all!

But the requirement is actually generic. We need views for all versioned tables. Do we have to create a SQL macro function for every table? No! Being able to pass a table parameter into a SQL macro function, we can just create very simple interface for querieng those tables. With function overloading capabilities of PL/SQL we can even use the “same” function for both scenarios, point in time and interval. See implementation of the package TIME_MACHINE in Listing 7. I’ve borrowed this “time machnine” analogy from my colleague Dani Schnider, but his presentation is only available in german.

CREATE OR REPLACE PACKAGE time_machine IS
-- point in time
FUNCTION vw (p_tab DBMS_TF.Table_t,  p_eff_date date) 
RETURN VARCHAR2 SQL_MACRO;
--interval
FUNCTION vw (p_tab DBMS_TF.Table_t,  p_eff_date date, p_exp_date date) 
RETURN VARCHAR2 SQL_MACRO;

END time_machine;

CREATE OR REPLACE PACKAGE BODY time_machine IS

FUNCTION vw (p_tab DBMS_TF.Table_t,  p_eff_date date) 
RETURN VARCHAR2 SQL_MACRO
IS
v_query varchar2(500);
BEGIN
  v_query := q'!SELECT * 
                FROM   p_tab 
                WHERE  p_eff_date BETWEEN valid_from  AND valid_to!';
  RETURN v_query;
END;

FUNCTION vw (p_tab DBMS_TF.Table_t,  p_eff_date date, p_exp_date date) 
RETURN VARCHAR2 SQL_MACRO IS
v_query varchar2(500);
BEGIN
  v_query := q'!SELECT * 
                FROM   p_tab 
                WHERE  valid_from <= p_exp_date 
                AND    valid_to >= p_eff_date!';
  RETURN v_query;
END;

END time_machine;
/

Listing 7: creating the time machine interface

This is how we can use the “time machine” to get both point in time as well as interval data from EMPV and DEPTV tables:

SQL> SELECT empvid, empno, valid_from, valid_to, ename, sal 
FROM   time_machine.vw(empv, DATE '1990-02-01') 
WHERE  empno = 7788

    EMPVID      EMPNO VALID_FROM VALID_TO  ENAME           SAL
---------- ---------- ---------- --------- -------- ----------
        22       7788 01-JAN-90  31-MAR-91 Scott          3000
1 row selected.

SQL> SELECT empvid, empno, valid_from, valid_to, ename, sal 
FROM   time_machine.vw(empv, DATE '1990-01-01', DATE '2020-01-01') 
WHERE  empno = 7788

    EMPVID      EMPNO VALID_FROM VALID_TO  ENAME           SAL
---------- ---------- ---------- --------- -------- ----------
        22       7788 01-JAN-90  31-MAR-91 Scott          3000
        36       7788 01-APR-91  31-DEC-99 Scott          3300

2 rows selected.

SQL> SELECT * 
FROM   time_machine.vw(deptv, DATE '1990-02-01')

   DEPTVID     DEPTNO DNAME          LOC           VALID_FROM VALID_TO 
---------- ---------- -------------- ------------- ---------- ---------
         1         10 ACCOUNTING     NEW YORK      01-JAN-80  28-FEB-90
         2         20 RESEARCH       DALLAS        01-JAN-80  28-FEB-90
         3         30 SALES          CHICAGO       01-JAN-80  28-FEB-90
         4         40 OPERATIONS     BOSTON        01-JAN-80  28-FEB-90

4 rows selected.

SQL> SELECT * 
FROM   time_machine.vw(deptv, DATE '1980-02-01', DATE '2020-01-01')
WHERE DEPTNO = 10

   DEPTVID     DEPTNO DNAME          LOC           VALID_FROM VALID_TO 
---------- ---------- -------------- ------------- ---------- ---------
         1         10 ACCOUNTING     NEW YORK      01-JAN-80  28-FEB-90
         5         10 Accounting     NEW YORK      01-MAR-90  31-MAR-90
         9         10 Accounting     New York      01-APR-90  31-DEC-99

3 rows selected.

Listing 8: querying using the new generic interface

And another nice thing: you can use binds, if you want. Because it’s not possible to declare bind variable of type DATE in SQL*Plus, I will demonstrate it with a PL/SQL block. We are using a bind variable here and the explain plan is showing that binds were used in a filter expression. We just have full control here: use binds in SQM call and you’ll get binds in your query, use literals and you’ll get literals.

SQL> -- Show I can use binds
SQL> declare
v_cnt pls_integer;
v_date date := DATE '1990-02-01';
begin
  select count(*)
  into   v_cnt 
  from   time_machine.vw(deptv, v_date);
end;
 PL/SQL procedure successfully completed.

...
SQL> select * from dbms_xplan.display_cursor(sql_id=>'8qr9z6n9gfm2k')

PLAN_TABLE_OUTPUT                                                               
-------------------------------------
SQL_ID  8qr9z6n9gfm2k, child number 0                                           
-------------------------------------                                           
SELECT COUNT(*) FROM TIME_MACHINE.VW(DEPTV, :B1 )                               
                                                                                
Plan hash value: 1461709607                                                     
                                                                                
----------------------------------------------------------------------------    
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |    
----------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |       |       |       |     3 (100)|          |    
|   1 |  SORT AGGREGATE    |       |     1 |    16 |            |          |    
|*  2 |   TABLE ACCESS FULL| DEPTV |    12 |   192 |     3   (0)| 00:00:01 |    
----------------------------------------------------------------------------    
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   2 - filter(("DEPTV"."VALID_FROM"<=:B1 AND "DEPTV"."VALID_TO">=:B1))          
                                                                                

19 rows selected.

Listing 9: using bind variables with SQL macro functions

Summary

I am really impressed with what we have been able to achieve here with SQL macros! On the one hand, one can disagree that we don’t actually have a parameterized view here. Yes, we don’t have a view at all. But on the other hand, we can use the SQM-function exactly the same way. We have built one function that can handle ALL tables, instead of having a lot of views. And moreover, we have not hidden any information from query optimizer. It can see literals or do bind peeking and use histograms if available.

Related Posts

5 thoughts on “Parameterized Views in Oracle? No problem! With SQL macros!

  1. Matthias Rogel

    what about the following trick to create parametrized views where we set the parameter(s) in the where-clause.
    (modified version of listing 4, difference: no need to set parameter via pl/sql before querying the view)

    CREATE OR REPLACE PACKAGE emp_hist_pkg IS
     
    g_eff_date date;
     
    FUNCTION set_eff_date(p_date IN DATE) return int;
    FUNCTION get_eff_date RETURN DATE;
     
    END;
    / 
    
    CREATE OR REPLACE PACKAGE BODY emp_hist_pkg IS
     
    FUNCTION set_eff_date(p_date IN DATE) return int is
    begin
       g_eff_date := p_date;
       
       return 0;
    end set_eff_date;
    
    FUNCTION get_eff_date RETURN DATE is
     return g_eff_date;
    END;
    / 
     
    CREATE VIEW emp_hist_v AS
    SELECT * 
    FROM   empv
    WHERE  emp_hist_pkg.get_eff_date BETWEEN valid_from AND valid_to;
     
    --  no need to set parameter, just
    SELECT * FROM emp_hist_view 
    where 0 = emp_hist_pkg.set_eff_date(DATE '1990-01-01')
    and   empno = 7788;
    
    Reply
    1. admin Post author

      Nice trick, Matthias!
      Thanks for sharing! Setting the parameter in the same query is better, although it has to be done in quite unusual way.
      What I continue to see as a drawback is that you still have no control over using literals or binds.
      And setting both functions to DETERMINISTIC and PARALLEL_ENABLE is quite important, I think.

      Regards
      Andrej

      Reply
    2. Narendra

      Interesting trick…Matthias
      As we don’t have control over which predicate gets evaluated first, won’t this kind of usage is likely to produce unreliable results? Or am I missing something obvious?

      Reply
  2. Shirazi

    Hello there,
    Thanks for sharing this helps me in a lot of our queries. But I wanted to ask question and i.e. if I wanted to use this method and I have 2 dates one is From and other is To how can I do that? Following is the updated Package;

    CREATE OR REPLACE PACKAGE OFUSION.VW_DATE IS

    G_DATE DATE;

    FUNCTION SET_DATE(P_DATE IN DATE) RETURN INT;
    FUNCTION GET_DATE RETURN DATE;

    END;

    CREATE OR REPLACE PACKAGE BODY OFUSION.VW_DATE IS

    FUNCTION SET_DATE(P_DATE IN DATE) RETURN INT IS
    BEGIN G_DATE := P_DATE;
    RETURN 0;
    END SET_DATE;

    –FUNCTION GET_DATE RETURN DATE IS RETURN G_DATE;

    FUNCTION GET_DATE RETURN DATE IS
    BEGIN
    RETURN G_DATE;
    END;

    END;

    Reply
    1. Shirazi

      I have done it from below Package, in case anyone needs.

      CREATE OR REPLACE PACKAGE OFUSION.VW_DATE2 IS

      G_FROM_DATE DATE;
      G_TO_DATE DATE;

      FUNCTION SET_FROM_DATE(P_DATE IN DATE) RETURN INT;
      FUNCTION SET_TO_DATE(P_DATE IN DATE) RETURN INT;
      FUNCTION GET_FROM_DATE RETURN DATE;
      FUNCTION GET_TO_DATE RETURN DATE;

      END;

      CREATE OR REPLACE NONEDITIONABLE PACKAGE BODY OFUSION.VW_DATE2 IS

      FUNCTION SET_FROM_DATE (P_DATE IN DATE) RETURN INT IS
      BEGIN G_FROM_DATE := P_DATE;
      RETURN 0;
      END SET_FROM_DATE;

      FUNCTION GET_FROM_DATE RETURN DATE IS
      BEGIN
      RETURN G_FROM_DATE;
      END;

      FUNCTION SET_TO_DATE (P_DATE IN DATE) RETURN INT IS
      BEGIN G_TO_DATE := P_DATE;
      RETURN 0;
      END SET_TO_DATE;

      FUNCTION GET_TO_DATE RETURN DATE IS
      BEGIN
      RETURN G_TO_DATE;
      END;

      END;

      Reply

Leave a Reply to Matthias Rogel Cancel reply

Your email address will not be published. Required fields are marked *