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

0
Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

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.

image

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.

image

 

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:

image

 

With another airline (LH):

image

And with an unknown airline:

image

 

And with no airline filter at all:

image

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:

SNAGHTML770abbf

and its SQL query:

image

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:

image

and:

image

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.

Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press books: Oracle SOA Suite 11g Handbook and Oracle SOA Suite 12c Handbook. Frequent presenter on community events and conferences such as JavaOne, Oracle Code and Oracle OpenWorld.

Leave a Reply