TUHRA Logo

Oracle JDeveloper 10g for Forms & PL/SQL Developers
Script
Chapter 15
Section Loading Audit Columns Using a Database Procedure and Application Context | Create the Database Objects
Code Script to create Context and security package
Return to listings...

Run the following script from SQL*Plus or a similar SQL worksheet. Connect as HR

CREATE CONTEXT hr_context USING security_pkg;

CREATE OR REPLACE PACKAGE security_pkg
IS
   PROCEDURE set_security_context (
      p_username    IN VARCHAR2,
      p_application IN VARCHAR2 DEFAULT 'TUHRA');

   FUNCTION logged_in_user
      RETURN VARCHAR2;

END security_pkg;
/

CREATE OR REPLACE PACKAGE BODY security_pkg
IS
   PROCEDURE set_security_context (
      p_username    IN VARCHAR2,
      p_application IN VARCHAR2 DEFAULT 'TUHRA')
   IS
   BEGIN
      -- Write the user info into the context area
      SYS.DBMS_SESSION.set_context ('HR_CONTEXT', 'APP_USERNAME', p_username);
   EXCEPTION
      WHEN OTHERS
      THEN RAISE_APPLICATION_ERROR(-20001,
            'Error in SECURITY_PKG.SET_SECURITY_CONTEXT: ' || SQLERRM);
   END;

   FUNCTION logged_in_user
      RETURN VARCHAR2
   IS
      v_username   VARCHAR2(100);
   BEGIN   
      v_username := UPPER(SYS_CONTEXT('HR_CONTEXT', 'APP_USERNAME'));
      RETURN v_username;
   EXCEPTION
      WHEN OTHERS
      THEN RETURN 'Error in LOGGED_IN_USER';
   END;   
   
END security_pkg;
/