Occasionally I see posts on Oracle sites from users asking how to create a view based on parameter(s). Now, many would ask why would you’d need this, especially when you can simply select from the view and apply a where clause? Well, there are a few situations which fall outside of that approach, the commonest it probably that the view contains a function that needs parameters or the view requires some “state” in its operation (state means there is memory of the past). Anyway, assuming you have such a need let’s look at some of the approaches at our disposal.
- To aid comprehension, the example scenario has been kept as simple as possible. In reality the approaches could be much more complicated.
- I’m qualifying the term “view” here to be a logical table, that is, data appearing to be tabular, even if it doesn’t come from a physical view object.
- The examples all provide a solution to the same problem, so I won’t show the same results that are output. I’ll use ellipses (…) instead.
There’s Several Ways to Skin a Cat
You can create a “view” based on parameters in several ways. I won’t cover every way possible (there are some more technically advanced and obscure approaches), but I’ll try to cover the main ones, which are :
2. Materialized View
3. Package variable
4. Pipelined Table Function
5. Ref cursor function
The approaches described produce data which fall into different “scoping” categories.
- Global Level : All sessions will see the same data.
- Session Level : Each session’s data is specific to that session.
Oracle provides a mechanism to store values against a namespace and parameter name through contexts. You can define your own namespace and parameters or you can use the default namespace USERENV and the CLIENT_INFO parameter, which has information pertaining to your session. Let’s explore a really simple example using the HR sample database in Oracle.
Let’s create a view which only shows employees hired in a given year.
create or replace view employees_hired_in_year as select * from employees where hire_date >= trunc(to_date(userenv('client_info'), 'yyyy'), 'year') and hire_date < add_months(trunc(to_date(userenv('client_info'), 'yyyy'), 'year'), 12); begin dbms_application_info.set_client_info('2007'); -- Set our required year end; select * from employees_hired_in_year; EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID ----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- ------------- 104 Bruce Ernst BERNST 590.423.4568 21-MAY-07 IT_PROG 6000 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-07 IT_PROG 4200 103 60 113 Luis Popp LPOPP 515.124.4567 07-DEC-07 FI_ACCOUNT 6900 108 100 119 Karen Colmenares KCOLMENA 515.127.4566 10-AUG-07 PU_CLERK 2500 114 30 124 Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-07 ST_MAN 5800 100 50 127 James Landry JLANDRY 650.124.1334 14-JAN-07 ST_CLERK 2400 120 50 132 TJ Olson TJOLSON 650.124.8234 10-APR-07 ST_CLERK 2100 121 50 135 Ki Gee KGEE 650.127.1734 12-DEC-07 ST_CLERK 2400 122 50 148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-OCT-07 SA_MAN 11000 .3 100 80 155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-NOV-07 SA_REP 7000 .15 145 80 163 Danielle Greene DGREENE 011.44.1346.229268 19-MAR-07 SA_REP 9500 .15 147 80 171 William Smith WSMITH 011.44.1343.629268 23-FEB-07 SA_REP 7400 .15 148 80 172 Elizabeth Bates EBATES 011.44.1343.529268 24-MAR-07 SA_REP 7300 .15 148 80 178 Kimberely Grant KGRANT 011.44.1644.429263 24-MAY-07 SA_REP 7000 .15 149 182 Martha Sullivan MSULLIVA 650.507.9878 21-JUN-07 SH_CLERK 2500 120 50 187 Anthony Cabrio ACABRIO 650.509.4876 07-FEB-07 SH_CLERK 3000 121 50 191 Randall Perkins RPERKINS 650.505.4876 19-DEC-07 SH_CLERK 2500 122 50 195 Vance Jones VJONES 650.501.4876 17-MAR-07 SH_CLERK 2800 123 50 198 Donald OConnell DOCONNEL 650.507.9833 21-JUN-07 SH_CLERK 2600 124 50 19 rows selected. -- We can check if that's the correct result, query how many employees per hire_date year select extract(year from hire_date) hire_year, count(*) from employees group by extract(year from hire_date) order by 1; HIRE_YEAR COUNT(*) ---------- ---------- 2001 1 2002 7 2003 6 2004 10 2005 29 2006 24 2007 19 2008 11 8 rows selected. -- 2007 = 19 results expected, so correct!
to_date(userenv(‘client_info’), ‘yyyy’) = convert the client_info context to a date – which will be today’s day an month but the year we passed in.
trunc(…, ‘year’) = truncate that date to the start of the year – 1st January.
add_months(…, 12) = add a year to that date
Note : If you try to pass something non-sensical to the context you’ll receive an error when you select from the view, such as the following
begin dbms_application_info.set_client_info('sillyvalue'); end; select * from employees_hired_in_year; ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Scope : Session Level or Global Level
The USERENV namespace is private to each session meaning the scope is Session level, however you can create user defined contexts that are accessed globally, making it a Global Level solution.
2. MATERIALIZED VIEW
This approach uses a Materialized View (MView) to materialise the data once the parameter has been set. It isn’t as flexible as some of the other approaches as it relies on refreshing the MView whenever you need to change the parameter, but I’ve included it for completeness.
Firstly login as SYS and grant the create materialized view privilege to where you’re creating the MView.
grant create materialized view to HR;
Then log back into the HR schema and let’s create the view and test it.
create materialized view employees_hired_in_year build deferred -- populate on the first requested refresh. refresh complete -- completely refresh on demand as -- when we're told to do so select * from employees where hire_date >= trunc(to_date(userenv('client_info'), 'yyyy'), 'year') and hire_date < add_months(trunc(to_date(userenv('client_info'), 'yyyy'), 'year'), 12); begin DBMS_Application_Info.Set_Client_Info('2007'); DBMS_MView.Refresh('EMPLOYEES_HIRED_IN_YEAR'); end; select * from employees_hired_in_year; .... 19 rows selected.
Whenever we want to change the parameter we have to refresh, like this…
begin DBMS_Application_Info.Set_Client_Info('2006'); DBMS_MView.Refresh('EMPLOYEES_HIRED_IN_YEAR'); end;
Scope : Global level.
All sessions will see the MViews data.
3. PACKAGE VARIABLE
Packages can be used to hold state through their package variables, so can be used to give us a parameterised view.
Note : Generally and ideally, package variables would be private, and accessed through package procedures / functions but for simplicity we’ll get / set them directly.
create or replace package P_Test is HireYear integer; end; / create or replace view employees_hired_in_year as select * from employees where hire_date >= trunc(to_date(P_Test.HireYear, 'yyyy'), 'year') and hire_date < add_months(trunc(to_date(P_Test.HireYear, 'yyyy'), 'year'), 12); begin P_Test.HireYear := 2007; end; select * from employees_hired_in_year; ... 19 rows selected.
Scope : Session level.
Each session has it’s own package state, so the view will be session specific.
4. PIPELINED TABLE FUNCTIONS
Table functions return data from collections in a way that appears to be from a table. They benefit over a simple table collection functions in the fact they can pipe data back to the caller as the data is available, without having to fully load the collection first. This means they use less PGA memory, have less bottlenecks and appear to operate faster. They are generally very useful for Extraction Transformation Load (ETL) operations.
Pipelined table functions require collections to be created and known to the data dictionary, this is either through system collection object types or package collection types. Package collection types create hidden collections behind the scenes.
create or replace package P_Test is cursor curEmployees(pDateFrom date, pDateTo date) is select * from employees e where hire_date >= pDateFrom and hire_date < pDateTo; type TEmployeesList is table of curEmployees%ROWTYPE; function EmployeesData(pHireYear integer) return TEmployeesList pipelined; end; / create or replace package body P_Test is function EmployeesData(pHireYear integer) return TEmployeesList pipelined is begin for rec in curEmployees(to_date('0101'||to_char(pHireYear), 'ddmmyyyy'), to_date('0101'||to_char(pHireYear + 1), 'ddmmyyyy')) loop pipe row(rec); end loop; return; end; end; / select * from table(P_Test.EmployeesData(2007)); ... 19 rows selected.
Scope : Session level.
5. REF CURSOR FUNCTION
Ref cursors are a “reference to a cursor”. Where a reference is a kind of pointer and a cursor is a select statement for getting a result set. Ref cursors don’t contain data, they just point to it and that means the ref cursor needs to be established (opened) and processed to get to their data. Many clients (e.g. SQL * Plus, Java, SQL Developer etc.) have inbuilt handling for ref cursors, iterating through them and returning the data they are linked to.
Let’s create a ref cursor function and use SQL * Plus to read the results.
create or replace function EmployeesData(pHireYear integer) return sys_refcursor is vResult sys_refcursor; begin open vResult for select * from employees e where hire_date >= to_date('0101'||to_char(pHireYear), 'ddmmyyyy') and hire_date var emp_rc refcursor SQL> exec :emp_rc := EmployeesData(2007); SQL> print emp_rc; -- This will print the data and close the ref cursor ... 19 rows selected.
Note : 12c and above provides DBMS_SQL.Return_Result which wraps up the last three SQL lines above to achieve the same result of printing the data.
Scope : Session level.
Each call creates a unique ref cursor, so is private to the session that calls it.
Hopefully this covers some of the options available if you ever want to create a parameterised view of data!