REM ============================================================================
REM SAMPLE INIT_SESSION PACKAGE
REM
REM An init_session package can be used to perform user defined session
REM initialization for any database session used within the Control Center
REM Manager. 
REM
REM An init_session package must contain a parameterless operation named
REM init_session. The package will normally be installed into the owbsys 
REM account. The init_session package name must be defined to OWB in the file
REM [serverside-home]/owb/bin/admin/Runtime.properties. This is done by adding 
REM an entry like this: -
REM
REM   connection.init_session=owbsys.session_setup
REM
REM An init_session package can assume that the application values MODULE,
REM ACTION and CLIENT_INFO are set up when it is called: -
REM
REM MODULE      ==> OWB_CCS
REM ACTION      ==> DEPLOYMENT (when session is to be used for a deployment)
REM                 EXECUTION (when session is to be used for a execution)
REM                 INFRASTRUCTURE (otherwise)
REM CLIENT_INFO ==> LOCATION ID (when session is to be used for a deployment)
REM                 EXECUTION ID (when session is to be used for a execution)
REM                 "" (otherwise)
REM
REM The location ID is the value of the location_audit_id column within the
REM view all_rt_audit_locations. The execution ID is the value of the
REM execution_audit_id column within the view all_rt_audit_executions.
REM In either case the identified row can be used to extract additional
REM information about the session usage.
REM ============================================================================

REM Define the session_setup package.
CREATE OR REPLACE PACKAGE session_setup IS
    PROCEDURE init_session;
END;
/

REM Grant execute access to any accounts used to run deployments or executions.
grant execute on session_setup to owbuser;

REM Define the session_setup package body.
CREATE OR REPLACE PACKAGE BODY session_setup IS

    -- return the execution audit ID or null if the session is not being
    -- used for execution
    FUNCTION execution_audit_id RETURN NUMBER IS
        l_result NUMBER;
    BEGIN
        FOR s IN (
            SELECT client_info FROM v$session 
            WHERE 
                action = 'EXECUTION' AND
                audsid = sys_context('USERENV','SESSIONID'))
        LOOP
            l_result := s.client_info;
        END LOOP;
        RETURN l_result;
    END;
    
    -- return the location audit ID or null if the session is not being
    -- used for deployment
    FUNCTION location_audit_id RETURN NUMBER IS
        l_result NUMBER;
    BEGIN
        FOR s IN (
            SELECT client_info FROM v$session 
            WHERE 
                action = 'DEPLOYMENT' AND
                audsid = sys_context('USERENV','SESSIONID'))
        LOOP
            l_result := s.client_info;
        END LOOP;
        RETURN l_result;
    END;
    
    /***************************************************************************    
        It is also possible to retrieve the audit ID for a PL/SQL map execution 
        prior to 11.2, as illustrated by the following code. This code relies
        on the MODULE value having the following form in this case: -    
          "OWB Runtime Service - [<audit-ID>] <map-name>" 
        where <audit-ID> is the key to the all_rt_executions view.
    
        FUNCTION execution_audit_id RETURN NUMBER IS
            l_result NUMBER;
            l_module VARCHAR2(80);
            l_end_index NUMBER;
        BEGIN
            SELECT module INTO l_module FROM v$session 
            WHERE 
                audsid = sys_context('USERENV','SESSIONID');
            IF l_module IS NOT NULL AND 
                SUBSTR(l_module, 1, 22) = 'OWB Runtime Service - '
            THEN
                l_end_index := INSTR(l_module, ' [');
                IF l_end_index > 0
                THEN
                    l_result := SUBSTR(l_module, 23, l_end_index - 23);
                END IF;
            END IF;
            
            RETURN l_result;
        END;
    
    ***************************************************************************/
    
    -- The init_session operation called to perform session initialization
    PROCEDURE init_session IS
        PRAGMA AUTONOMOUS_TRANSACTION;
        l_module VARCHAR2(80);
        l_execution_audit_id NUMBER;        
    BEGIN
        l_execution_audit_id := execution_audit_id;
        IF l_execution_audit_id IS NOT NULL
        THEN
            -- When used for execution retrieve the task name and reset ACTION.
            -- This might be used to change the way that resource manager
            -- consumer groups are assigned.
            FOR a IN (
                SELECT task_name FROM all_rt_audit_executions
                WHERE 
                    execution_audit_id = l_execution_audit_id)
            LOOP
                dbms_application_info.set_action(a.task_name);
            END LOOP;
        END IF;
        COMMIT;
    END;
END;
/