lucas jellema do not use the database out of context – on the responsibility of database clients
Do not use the database out
of context – on the responsibility
of database clients
Many mechanisms in the Oracle Database can take over the burden of complex challenges from middleware components and other database clients. Edition Based Redefinition, SQL Translation, Flashback Query, Consumer Resource Plans, Data Redaction, Auditing and Data Authorization (with Virtual Private Database or custom data filters) are some of these mechanisms that can perform tasks that are complex or expensive to perform.
All of these mechanisms depend on the appropriate context being set up for the database session in which they execute. This context defines aspects such as who (is the user for which the database is doing this work), when (is the moment in time that the database should assume), what (is the application or component – and the specific version - that is currently executing logic that requires work from the database) and why (what is the functional background for this activity).
To allow the database to produce meaningful log, audit trail and trace information that allow problem analysis of issues and interpretation and optimization of run time behavior, it is equally important that the execution context is set. Without it, efficient administration of the RDBMS is not possible.
This article discusses how database clients can set the database session context – with concrete examples for ADF BC and SOA Suite Database Adapter being provided. The article discusses how auditing and trace details leverage context details. It also describes how the context is available from within custom database objects – such as Views and PL/SQL units. The article describes some interesting use cases for leveraging the database session context.
Finally the article makes the argument that databases should refuse to process requests that do not have a proper context defined – and how DBAs should therefore not accept applications as database clients if they do not behave responsibly regarding the context.
Context elements are associated with a database session. Some of these are [only] set at login time, when the connection is created and the session is initiated – these include:
• PROXY_USER - Name of the database user who opened the current session on behalf of SESSION_USER.
• SERVICE_NAME - The name of the service to which a given session is connected – as specified in the
tnsnames.ora file; note that multiple services can connect to the same database instance.
• CLIENT_OS_USER - Operating system user name of the client process that initiated the database session
• SESSION_USER - the database user name by which the current user is authenticated.
• [CLIENT_]PROGRAM - Name of the program used for creating the database session
• [CLIENT_]MACHINE - Name of the database user who opened the current session on behalf of
Others can be set throughout a session with alter session statements, calls to specific supplied PL/SQL packages or manipulation through OCI or JDBC interfaces; these include:
• Current Schema - Name of the default schema being used in the session
• MODULE[_NAME] – the (free format) name of the client application’s module or program unit
• [MODULE_]ACTION- a free format indication of the feature or function in the client application from which
the current activity was triggered
• CLIENT_INFO – additional free format information about the (state of) client (program) for which the
session currently performs activities
• CLIENT_IDENTIFIER – identifier describing the light weight end user connected to the client application
• EDITION – the edition (in the context of Edition Based Redefinition) set for the current session
• NLS settings for date, currency and number format
• current system change number (and indirectly the current flashback point in time) – specifying against
which point in history queries should be executed
• current valid time setting – specifying the business date at which queried records must be valid
• SCHEDULER_JOB – flag (Y or N) indicating whether the session belongs to a background job
• the ECID – the Fusion Middleware execution context identifier that identifies a cross-WebLogic-and-
database-node chain of activities executed to service a single request
• the SQL_TRANSLATION_PROFILE_ID – the identifier of the SQL Translation Profile that is currently active in
• Resource Consumer Group – the name of the session's current resource consumer group
The session context can also contain user defined elements. These are typically stored in application contexts and manipulated through custom PL/SQL packages.
Retrieving Session Context Settings
Many of the predefined database context settings are available from the dynamic V$ views, such as
V$SESSION (see https://docs.oracle.com/database/121/REFRN/refrn30223.htm#REFRN30223).
As a simple example:
Some of these can also be retrieved from the USERENV application context – along with many other context attributes (details here). This is can be done in SQL as well as PL/SQL using the function SYS_CONTEXT as follows:
select sys_context('USERENV','<some attribute name>')
As of Oracle Database 12c, the Flashback mechanism has been extended with the capability to also track the history of the session context at the time of each transaction. With a call to DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL we specify for the current database session how much context details should be recorded (NONE, TYPICAL - only basic auditing attributes from the USERENV context, ALL - all contexts available to the user via the SYS_CONTEXT function). Context details that have been recorded for transactions that took place in sessions with the context level set to TYPICAL or ALL can be retrieved directly from table SYS_FBA_CONTEXT_AUD or using a call like this:
dbms_flashback_archive.get_sys_context (<transaction_id>, 'USERENV', 'SESSION_USER');
The value for transaction_id can be retrieved for example in a flashback versions query such as:
, versions_xid transaction_id
FROM <table> t
VERSIONS BETWEEN BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
that returns all versions of all records in a table and for each record the identifier of the transaction that brought about that version.
Some context attributes require a special approach to be retrieved – or do not seem to be available at all. As an example of the latter: I have not been able to retrieve the value set for the current valid time (with a call to DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME).
Retrieving the current flashback time – the point in history from which data should be queried – is done somewhat indirectly: first, the current system change number is retrieved using DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER. This returns an SCN that can subsequently be mapped to an approximate point in time:
SELECT SCN_TO_TIMESTAMP(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) FROM dual
Custom context attributes are set in custom application context. The values of these attributes is also retrieved using the SYS_CONTEXT function – either in SQL or PL/SQL – with statements such as:
l_my_special_context_attribute varchar2(100) :=
Configure Session Context Settings
Some attributes in the session context are set at login time, when the connection is established – depending on the client that acquires the connection. These values cannot change at a later moment in the session. Other attributes take a default value such flashback (now) and valid time (all version), sometimes derived from database parameters such as NLS settings and the edition. These defaults can be overridden in various ways – including alter session statements and calls to various supplied PL/SQL packages.
Some of the most relevant database session context attributes and the way how they are to be set from within the database session using PL/SQL are listed below:
Custom Database Session Context attributes are stored in custom application context objects. Such application context objects are created with an associated PL/SQL package that is used for manipulating the values in the application context.
To create such a context:
CREATE OR REPLACE CONTEXT MY_CONTEXT USING MY_CONTEXT_API;
And the PL/SQL package used for context control:
CREATE OR REPLACE PACKAGE MY_CONTEXT_API AS
PROCEDURE set_value (p_name IN VARCHAR2,
p_value IN VARCHAR2);
CREATE OR REPLACE PACKAGE BODY MY_CONTEXT_API AS
PROCEDURE set_value (p_name IN VARCHAR2,
p_value IN VARCHAR2) AS
DBMS_SESSION.set_context('MY_CONTEXT', p_name, p_value);
A simple call will now set a custom context attribute value:
And a similarly simple usage of SYS_CONTEXT will retrieve the value:
select SYS_CONTEXT('MY_CONTEXT', 'SPECIAL_ATTRIBUTE') from dual
There is a special type of application context – one that is associated with a client identifier and created with the ACCESSED GLOBALLY indication. Values in this type of context are not associated with specific database session – but are accessible across sessions. By setting context attributes in association with the session’s client identifier, we make it possible for all database sessions that perform work on behalf of that same client identifier to share values. This done using an extra parameter in the set_context procedure in DBMS_SESSION:
( namespace => 'MY_CONTEXT'
, attribute=> p_name
, value=> p_value
, client_id=> SYS_CONTEXT('userenv', 'client_identifier')
The next illustration visualizes the concept of an application context associated with a client identifier:
Database session 1 is used by the web session set up for the blue user; this is specified through the client_identifier (for example set to ‘blue’) that can be retrieved from the USERENV context. Earlier, values were set for this same user in another database session that at that moment also had the client_identifier set to ‘blue’. In database session 3, the client_identifier is set to ‘red’. When attribute values are retrieved from the application context, those that were set as ‘red’ are returned. When the green user will connect to the web application, a web session is established that requests a JDBC connection from the pool and sets the client_identifier to ‘green’. When subsequently values are set in the application context, they are stored as ‘green values’ and only database sessions with the client_identifier set to ‘green’ can retrieve these values.
Set Database Session Context Value from Middleware
When Fusion Middleware components call out to the database, they should make sure that the database session in which they will execute their queries, data manipulations and PL/SQL calls has the proper context set. This context should at the very least include client_identifier, module and action and may require edition, flashback and valid time. Additionally, because actions in Fusion Middleware typically take place in an execution context identified by the ECID – a unique string that is included in logging and in headers of the requests exchanged between WebLogic servers – it would be very convenient to have this ECID value available in the database session context for logging and tracing. As it happens, recent versions of WebLogic take care of setting this ECID value in the database session – where it is available in the ECID column of the
The next figure shows how the ECID value is established when an HTTP request first enters the WebLogic Domain – in this case to be processed by the Service Bus – and subsequently travels through the middleware chain and to the database where it is set in the database session context.
More details about this end-to-end ECID propagation are in this blog article.
Most ADF applications will have some form of database interaction and most of these application will use ADF BC (for Business Components) to do so. With ADF BC, developers do not have to worry about bare bones JDBC – the framework takes care of most details such as acquiring a JDBC database connection and executing SQL statements for select, update, insert or delete.
The ADF BC application module is the container through which all database interaction takes place. At the level of this application module is the database connection established and it is at this level that the application and session specific aspect of the database session context should be set. The method prepareSession() should be overridden in the ApplicationModuleImpl Class and in this method, the ADF developer has to ensure that context attributes such as module, edition and client_identifier are set. Note that prepareSession() is executed whenever a database connection and its associated database session are made available to a user session – for the first time or after someone else has or may have made use of it.
This blog article by Paco van der Linden introduces a great utility class that makes calling a PL/SQL procedure from ADF BC a breeze. A PL/SQL package should be created that with a single call takes all relevant attribute values and sets them in the database session context.
Some settings – such as the action and perhaps client_info and flashback time - are not determined at application or session level, but more dynamically throughout the lifetime of the application session, as the user is making steps and performing actions. By overriding method executeQueryForCollection() in a ViewObjectImpl class, we can implement pre-query logic to be executed right before the SQL query is executed against the database. This is the perfect place to set database session context values such as the action, the flashback query time, the valid time and custom application context attributes that may be used in database views accessed from the ViewObject. Again, a single call to a PL/SQL procedure should be enough to pass all relevant values to set in the session context.
Note that in case you need it, the ECID is programmatically available using DiagnosticContextHelper.getContextId().
Interaction from SOA Suite with the Oracle Database is typically performed through the database adapter. The database adapter can be used in inbound direction – polling the database for new or changed records – or in an outbound direction to perform DML, execute queries or invoke PL/SQL program units.
When polling for database records the SQL statements executed are composed by the database adapter, unless we use the LogicalDeletePollingStrategy. With this strategy, we can define two custom SQL statements to be executed for (1) retrieving the records to process and (2) to update the records that have been processed. Especially in the first SQL statement, we can include an inline view with a call to a PL/SQL function that sets database session context values and subsequently leverage them in the main query. Note that as of release 184.108.40.206 (and with backports to earlier releases), the Database Adapter can retrieve an ECID value from the database and use it to add the newly created instance to the chain already created for that ECID. When the Database Adapter service reads a row from the database, it retrieves the ECID value from the payload. When the component instance is created, it is associated with the retrieved ECID and the payload contains everything except the ECID element/value. See this article for details on ECID propagation.
For the outbound case I recommend invoking a PL/SQL program unit and have the SQL query or DML operations execute encapsulated inside a PL/SQL. The call to the PL/SQL program unit should include one additional input parameter that takes an object such as this:
create type session_context as object
(client_identifier varchar2 (100), module varchar2(48), action varchar2(32), ecid varchar2(50),flashback_time date, edition varchar2(128))
The mapping of the appropriate values to this input parameter can easily be done using a transformation or assign operations in Mediator or BPEL. On the database end, a generic PL/SQL procedure can be used – ideally added to the object body of session_context – to set the appropriate database session context attributes based on the properties of the object:
( p_session_context in session_context_t
if p_session_context is not null
DBMS_APPLICATION_INFO.SET_MODULE(p_session_context.module, p_session_context.action );
Adding an input parameter based on session_context_t to the signature of a PL/SQL procedure or function will have the database adapter generate the XSD representation of the type:
When a Mediator component is used to link to the database adapter binding – as is frequently the case –
values for the session_context can be set for example using assign values instructions, as shown in the next figure. Here we do not use authentication – so a fixed string is used for the Client Identifier. Additionally, the name of the SOA composite and the name of the invoked operation are put in the session context.
When the SOA Composite is executed, a new instance is created for which the ECID value can be inspected in the EM FMW Control.
Using this ECID value, we can query for example view V$SESSION. This shows how the context values are registered and exposed. Similarly, these values are stored in the audit trail, trace files and in the flashback data archive.
Note: this blog article describes in detail how to set the database session context through the database adapter.
Leveraging Database Session Context Settings
Analysis of the records that describe earlier database activity is much easier when relevant details about the session context at the time of the activities are available. To achieve that, we have to ensure that the database session context is set up correctly – including optional elements – when these activities are performed and must make sure that these settings are registered.
Elements of the context attributes set in the database session can be recorded – depending on the configuration - as part of the database audit trail, trace files, AWR (Automatic Workload Repository) and ASH (Active Session History) and Flashback Archives. Note that AWR and ASH have to be licensed through the Oracle Enterprise Manager Diagnostics Pack.
In addition to the role the session context plays in enriching the records of past activities, the database session context plays a very important role during these activities. As the next illustration depicts – the effect of queries and data manipulations can be influenced enormously as a result of the values in the session context. A wide array of mechanisms can play a part in the execution of SQL statements, such as the query ‘select * from employees’ that is illustrated here.
Some examples of using database session context
Here are some brief introductions to situations where the use of database session context can help overcome challenges or offer alternative options for implementing functionality.
ORA-04068 (existing state of packages has been discarded)
When state is associated with a PL/SQL package – through the use of global variables in the package – and the package is recompiled itself, because a new version of the package is introduced , the state of the package is lost for all sessions that had created such state. The well known and thoroughly annoying ORA-04068 error is a result. We can get rid of this error – if we put the state of our session where it belongs. And that is not in package globals stored in the UGA but instead in an application context associated with the client identifier.
package body MY_PACK
We should instead be using:
package body MY_PACK
return sys_context('MY_PACK_CONTEXT', 'g_important_global');
procedure set_g_important_global(p_value in varchar2)
DBMS_SESSION.set_context('MY_PACK_CONTEXT', 'g_important_global', p_value);
When now packages are recompiled, there is no state to be discarded because all state is held in the application context. This means that the ORA-04068 error will be a thing of the past.
This article describes in detail how to work with application context based state instead of using global package variables: https://technology.amis.nl/2012/08/07/reduce-occurrence-of-ora-04068-while-upgrading-plsql-packages-by-moving-global-variables-to-application-context/.
Implement Time Travelling in ADF applications
When flashback data archives have been associated with tables accessed by an [ADF] application, it is possible to travel back in time and have all data reported from some point in the past. To set the database session context to a specific flashback moment, we have to invoke the PL/SQL procedure DBMS_FLASHBACK.ENABLE_AT_TIME (query_time). We can do this from the ADF BC application module at any moment – using a client method – or for specific ViewObjects from the overridden executeQueryForCollection() method, leveraging a bind parameter. Alternatively, the FROM clause of custom ViewObject query can contain the AS OF clause that uses a bind parameter to [dynamically] determine which point in time to travel back to:
FROM <TABLE> as of timestamp (systimestamp + (0-1)*
with bind_flashbacktime a bind parameter of type oracle.jbo.domain.Number.
Hide query complexity in Database Views
Ideally, complex SQL that directly depends on the table design in our database should not be included in client application but instead be encapsulated in the database – in an API layer that consists of Views and Packages. Especially for views, we may want to bake in some filtering behavior. However, unlike PL/SQL procedures and functions, views do not have something like input parameters. How can the consumer of a view indicate what the filtering behavior of a view should be?
That is where the application context enter. Session attributes set in the application context can be referred to in the WHERE clause (as well as FROM, ORDER BY, GROUP BY and SELECT) of a database view. That means that if we ensure that the appropriate values are set in the application context, our view can use them to behave accordingly – without exposing complex SQL to the consumer.
The most explicit way to implement this in the client application is something like this in pseudo code:
call_plsql_program_with_parameters(param1, param2); /* values of param1 and param2 are stored in the database session application context by the PL/SQL program */
query_from_database_view; /* database view makes use of the values of param1 and param2 that are available to it from the application context */
It is also possible to combine in a single SQL statement the steps to set the parameters in the context and the query against the view. In pseudo code again, this looks like:
with this_first as
( select /*+ materialize */
MY_CONTEXT_API.getset_value('PARAM1', <value of param1>) dummy
from <view name>
cross join this_first
Where the view is defined like this:
create or replace view <view name>
from <table name> t
where t.columnX = SYS_CONTEXT('MY_CONTEXT', 'PARAM1')
In order for auditing and tracing of database operations to be [optimally] valuable, it is essential that the database session context has all relevant values set. It is the responsibility of database clients to provide most of the values for the session context attributes. DBAs should consider enforcing that external consumers provide appropriate context details. Note that some context elements – such as the ECID value in case of Fusion Middleware interaction – are set automatically. Database Vault (an extra option for the Oracle Database) rules can be used to enforce that context attributes – such as module, action and application_info – are set in order to perform any database activity at all. With Resource Plans and suitable directives, something similar can be achieved.
Session context is what drives a number of mechanisms in the Oracle Database. Only by providing the fitting values for specific session context attributes can these database mechanism execute their work on behalf of database clients in the right way. When mechanisms such as Flashback, Virtual Private Database, Data Redaction, SQL Translation, Edition Based Redefinition and Consumer Groups with Resource Plans are at play, applications accessing the database have to ensure that they set the context properly before executing their queries and DML statements.
The database application context offers interesting options for custom applications. Usage of context attributes can help solve the ORA-4068 problem with recompiled packages, supports state across operations in an environment with connection pooling and offers efficient interaction between client applications and the database.
OTECH MAGAZINE #7 spring 2015 copyright otech magazine 2015