Set values for bind parameters in a database view using application context – single round-trip context manipulation and querying

While writing an article on database session context for the OTech Magazine’s Spring issue, I tried out whether I can send a SQL statement from a database consumer such as the SOA Suite Database Adapter or an ADF BC ViewObject that passes in the values for some bind parameters that are not used in the SQL statement itself but instead in the definition of the database view that is being queried. This blog article shows that it can be done – as well as how to do it. I am not sure yet how to exactly put this approach to good use – but it seems that it can help with bringing complex SQL to be encapsulated in the database itself (in SQL and PL/SQL APIs) instead of in client applications.

This figure visualizes my intention: the SQL statement that my client application sends to the database not only contains the select * from <view> but also passes in the values of one or more bind parameters. These values are stored in a custom application context. When the query defined by the view is executed, it makes use of the values stored in the application context to return the data required by the application, without the application needing to know anything about the SQL that is executed and the underlying data model. In this article I will use bind parameters in the WHERE clause of the View definition. Note however that these values can just as easily be used in the FROM (Flashback), GROUP BY, ORDER BY and SELECT clauses.

Prepare the database

The preparation of the database in this example is pretty straightforward: in a database schema that has a table that contains airline records and the privileges to create packages, views and any [application] context, I create an application context and a PL/SQL package to manipulate the values in this context. Next I create a view that has a where clause that filters airlines based on an attribute in the application context.

In the next section I will execute a SQL query against this view. In a single SQL statement, I will push a value to the application context and have this value influence the query against the view.

Create the application context:

CREATE OR REPLACE CONTEXT MY_CONTEXT USING MY_CONTEXT_API;

Create the PL/SQL package:

CREATE OR REPLACE PACKAGE MY_CONTEXT_API AS
 
PROCEDURE set_value (p_name  IN VARCHAR2,
                     p_value IN VARCHAR2);

function getset_value (p_name  IN VARCHAR2,
                     p_value IN VARCHAR2)
return varchar2;                    

END MY_CONTEXT_API;

and the body:

CREATE OR REPLACE PACKAGE BODY MY_CONTEXT_API AS
 
PROCEDURE set_value (p_name  IN VARCHAR2,
                     p_value IN VARCHAR2) AS
BEGIN
  DBMS_SESSION.set_context(‘MY_CONTEXT’, p_name, p_value);
END;

function getset_value (p_name  IN VARCHAR2,
                     p_value IN VARCHAR2)
return varchar2
is
begin
  set_value (p_name  ,
                     p_value );
  return sys_context(‘MY_CONTEXT’, p_name);                    
end getset_value;

END MY_CONTEXT_API;

Create the View:

create or replace view airlines
as
select car.*
,      SYS_CONTEXT(‘MY_CONTEXT’, ‘AIRLINE’) context_airline
from   fli_airline_carriers car
where  car.iata_code = nvl (SYS_CONTEXT(‘MY_CONTEXT’, ‘AIRLINE’), car.iata_code)

Execute a query against the view

A simple SQL statement “select * from airlines” will simply return all airlines, because no value is set in the application context that is used to filter by.

By adding an inline view that we force the database to execute first (with the /*+ MATERIALIZE */ hint) we can have a “pre-query” statement executed just prior to the main query. In this “pre query” query, we can invoke a PL/SQL function, for example to pass values that the function can subsequently put in an application context:

with this_first as
( select /*+ materialize */ MY_CONTEXT_API.getset_value(‘AIRLINE’, ‘EK’) dummy from dual)
select ale.*
from   airlines ale cross join this_first

Executing this query:

With another airline (LH):

And with an unknown airline:

And with no airline filter at all:

Example with ADF BC View Object

This mechanism can easily be exploited in an ADF BC View Object.

In a new Fusion Web Application with a database connection to the schema used above, I create a new View Object:

and its SQL query:

Notice how this ViewObject has a single bind parameter – bind_airline – and a simple ‘select * from airlines’ as its core query.

When I run the ADF Model Tester, I can see the same effect of setting the bind parameter in the results of executing the query for the ViewObject as seen in the previous section.

For example:

and:

In each case, the (non) value entered for the bind parameter is used in the SQL statement that is passed to the database and there in turn into the application context MY_CONTEXT. In this way – the bind parameter influences the effective where clause of the query – even though the ViewObject definition does not even contain a WHERE clause.