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.