custom in memory variables defined using SYS_CONTEXT in SQL and PL/SQL


This is interesting trick you probably won’t use that often, I used it few times in views in cases where I could not use parameter or because of aggregated complex views where I could not use WHERE clause. I did not find this anywhere on internet, I just had a moment of clarity.

Oracle Database has very useful function SYS_CONTEXT, which can be used as a source of all kinds of information about current session – from database username, OS username, IP address, … you name it. The primary use is for custom audit and security functions, but you can of course use it for whatever you wish.

for example this query returns client’s IP address

SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM DUAL;

What is really interesting on this function that it returns any parameter from any namespace (even custom namespace) and this is where the fun starts. You can create a parameter and use it in your queries and views, then by changing the value of this parameter you change the result returned by the query or view without modifying the query.

first let’s create some sample data

CREATE TABLE JIRI.ALL_USERS AS
SELECT USERNAME
  FROM SYS.DBA_USERS;

then let’s create a view with custom namespace and parameter

CREATE VIEW JIRI.SELECTED_USERS AS
SELECT COUNT(*) MY_COUNTER
  FROM JIRI.ALL_USERS
 WHERE USERNAME = SYS_CONTEXT ('MY_NAMESPACE', 'MY_PARAMETER');

Let me pause a bit before I assign custom value to my_parameter. As I mentioned above, Oracle created this for security purposes, usually custom application authentication thus assigning the value has one small jump. Basically you have to create stored procedure first, set the value in stored procedure and grant this procedure right to set the value. In another words you cannnot assign this parameter without being granted to.

 

CREATE OR REPLACE PROCEDURE SET_MY_PARAMETER (v_input_parameter varchar2) AS
BEGIN
  DBMS_SESSION.SET_CONTEXT ('MY_NAMESPACE', 'MY_PARAMETER', v_input_parameter);
END;

CREATE CONTEXT MY_NAMESPACE USING SET_MY_PARAMETER;

 

now let’s test it out

exec SET_MY_PARAMETER(v_input_parameter => 'JIRI');
select * from JIRI.SELECTED_USERS;

-- returns 1 

exec SET_MY_PARAMETER(v_input_parameter => 'DUMMY USERS WHICH DOES NOT EXIST');
select * from JIRI.SELECTED_USERS;

-- returns 0

 

As you can see it is very easy to create custom parameters and simply use then across multiple queries or views in one session in both PL/SQL and SQL.

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

%d bloggers like this: