Γνῶθι σεαυτόν

Sometimes it might be interesting to understand, what's the SQL statement which is currently executed.
In an Oracle instance, for any other session it's quite simple by accessing some v$ views. But from "within" the statement, it's not straight forward. Nevertheless it's possible, by a creative combination of some features.

Here is the example.

First let's create a proper user:

CREATE USER know IDENTIFIED BY "thyself";

GRANT connect, resource    TO know;

GRANT UNLIMITED TABLESPACE TO know;

GRANT    CREATE VIEW       TO know;

GRANT imp_full_database    TO know;

That's slightly more than really needed - so if you evern require this funcitonality, please be careful with required permissions!
In this example, we do not even need a table.
But a Package to keep some variables:

CREATE OR REPLACE PACKAGE other_color_injector AS
-- package OCI
    text_keeper   VARCHAR2(4000 CHAR) := '--';
    current_color VARCHAR2(100 CHAR)  := 'white';
    FUNCTION rls_hook (
        p_schema  IN  VARCHAR2,
        p_object  IN  VARCHAR2
    ) RETURN VARCHAR2;

END other_color_injector;
/

CREATE OR REPLACE PACKAGE BODY other_color_injector AS

    FUNCTION rls_hook (
        p_schema  IN  VARCHAR2,
        p_object  IN  VARCHAR2
    ) RETURN VARCHAR2 AS
        PRAGMA autonomous_transaction;
        chk_color VARCHAR2(100 CHAR);
    BEGIN
        text_keeper := sys_context('userenv', 'CURRENT_SQL');

        -- first word in /* comment 
        chk_color := regexp_substr(regexp_substr(text_keeper, '/\*.*\*'), '[[:alpha:]]+');
        dbms_output.put_line(' SQL Text:' || text_keeper || '---');
        IF chk_color IS NOT NULL THEN
            current_color := chk_color;
        END IF;
        RETURN NULL;
    END;

END other_color_injector;
/

And a function which gives us some feedback about the SQL in which it's called:

CREATE OR REPLACE TYPE rain_tab IS    TABLE OF VARCHAR2(100 CHAR);

create or replace FUNCTION rain_bow 
-- RETURN t_tf_tab 
return rain_tab
PIPELINED AS
BEGIN
    PIPE ROW ( other_color_injector.current_color );   

  RETURN;
END;
/

create or replace view cloud as select column_value as color from rain_bow();

The view is somehow important for the next step whihc puts all the pieces together:

BEGIN
    dbms_rls.add_policy(object_schema =>   'KNOW', 
                        object_name =>     'CLOUD', 
                        policy_name =>     'RAIN', 
                        function_schema => 'KNOW', 
                        policy_function => 'OTHER_COLOR_INJECTOR.RLS_HOOK',
                        statement_types => 'select', 
                        update_check => false);
END;
/

With this objects in place, a beautiful, but maybe confusing result can be created:

Enter user-name: know/thyself@pdb1

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select * from cloud;

COLOR
-------------------------------------------
white

SQL> select /* red */  * from cloud;

COLOR
-------------------------------------------
red

SQL> select * from cloud;

COLOR
-------------------------------------------
red

SQL> select /* green */  * from cloud;

COLOR
-------------------------------------------
green

SQL> select * from cloud;

COLOR
-------------------------------------------
green

SQL>

The important part is this line in OTHER_COLOR_INJECTOR.RLS_HOOK:

text_keeper := sys_context('userenv', 'CURRENT_SQL');

SYS_CONTEXT has access to CURRENT_SQL (and some other useful parameters) -  but only in fine grained audot / RLS events. So all the other objects are required to make this information available in my view rain_bow.

---

I also created a small twitter quiz where I asked for methods to identify the current SQL and create values based on the SQL.
Rene Jeruschkat suggested a solution based on latest v$sqlstats:

exploiting the excessive privileges you granted know and keeping track of the color in v$sqlstats. pic.twitter.com/2tCu8eQyoH

— Rene jeruschkat (@rene_jeruschkat) April 4, 2020

Mathias Rogel suggested to get the latest cursor from v$open_cursor which belongs to the current session:

pic.twitter.com/OpFWHm8epc

— Matthias Rogel 🏃🚴🏊‍♂️🏋️ (@MatthiasRogel) April 5, 2020

Both are great solutions and show: there is quite often more than one way to solve a problem!

---

If you are curious about the greek letters in the title: Γνῶθι σεαυτόν they can be translated to know thyself - something which is often hard - not only for SQL statements.