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.<\/p>\n\n\n\n\n\n\n\n
<\/p>\n\n\n\n
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<\/a> has posted a historized version of the famous SCOTT schema to look at joining temporal intervals<\/a>. He also provides a script<\/a> to create this schema and data.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n Let’s look at the data for one employee (EMPNO=7788): <\/p>\n\n\n Listing 1: keeping all changes in a historized EMP table<\/em><\/strong><\/p>\n\n\n\n Would be great, if we could get a view with the following query: <\/p>\n\n\n Listing 2: the desired query for the view <\/em><\/strong><\/p>\n\n\n\n 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. <\/p>\n\n\n Listing 3: simplified description of SYS_CONTEXT workaround<\/em><\/strong><\/p>\n\n\n\n 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.<\/p>\n\n\n Listing 4: simplified description of a workaround with package variables<\/em><\/strong><\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n Let’s again look at the query, we would like to implemet as a view: <\/p>\n\n\n Listing 5: <\/em><\/strong> the desired query for the view<\/em><\/strong> <\/p>\n\n\n\nSQL> SELECT empvid, empno, valid_from, valid_to, ename, sal \nFROM empv \nWHERE empno = 7788\n\n EMPVID EMPNO VALID_FROM VALID_TO ENAME SAL\n---------- ---------- ---------- --------- -------- ----------\n 8 7788 19-APR-87 31-DEC-89 SCOTT 3000\n 22 7788 01-JAN-90 31-MAR-91 Scott 3000\n 36 7788 01-APR-91 31-DEC-99 Scott 3300\n\n3 rows selected.\n<\/pre><\/div>\n\n\n
\nSELECT * \nFROM empv \nWHERE p_eff_date BETWEEN valid_from AND valid_to\n<\/pre><\/div>\n\n\n
\n-- create package and context \n...\n-- create view\nCREATE VIEW emp_hist_v AS \nSELECT * \nFROM empv\nWHERE SYS_CONTEXT('MY_VIEW_CONTEXT','EFF_DATE')\n BETWEEN valid_from AND valid_to;\n \n-- Set the context variable\n... \n\n-- Query is now possible \nSELECT * FROM emp_hist_view;\n<\/pre><\/div>\n\n\n\nCREATE OR REPLACE PACKAGE emp_hist_pkg IS\n\ng_eff_date date;\n\nPROCEDURE set_eff_date(p_date IN DATE);\nFUNCTION get_eff_date RETURN DATE DETERMINISTIC PARALLEL_ENABLE;\n\nEND;\n\/ \n\nCREATE VIEW emp_hist_v AS \nSELECT * \nFROM empv\nWHERE emp_hist_pkg.get_eff_date BETWEEN valid_from AND valid_to;\n\n-- set parameter\nBEGIN \n emp_hist_pkg.set_eff_date(DATE '1990-01-01');\nEND; \n\n-- Query is now possible \nSELECT * FROM emp_hist_view;\n\n<\/pre><\/div>\n\n\n
SQL Macros <\/h4>\n\n\n\n
\nSELECT * \nFROM empv \nWHERE p_eff_date BETWEEN valid_from AND valid_to\n<\/pre><\/div>\n\n\n