Parameterised Views

Background

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.

Notes :

  1. To aid comprehension, the example scenario has been kept as simple as possible. In reality the approaches could be much more complicated. 
  2. 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.
  3. 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 :
1. SYS_CONTEXT
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.

1. SYS_CONTEXT

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 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.

Final Words

Hopefully this covers some of the options available if you ever want to create a parameterised view of data!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s