Package State (Global Variables and Cursors)

In Oracle PL/SQL, a package is a schema object that groups logically related PL/SQL types, items, and subprograms. One of its powerful features is the ability to maintain package state. This means that variables declared at the package level (outside of any specific procedure or function within the package) retain their values for the duration of a user's session. Similarly, cursors opened at the package level can also maintain their state. This persistence of data across multiple subprogram calls within the same session is a key concept for optimizing performance and managing complex application logic. Think of it as a shared memory area for your PL/SQL application within a single user session. This is incredibly useful for caching data, maintaining session-specific information, or implementing complex stateful logic.

Example 1: Package Global Variable

CREATE OR REPLACE PACKAGE emp_pkg AS
  -- Global package variable to store the last accessed employee ID
  g_last_employee_id NUMBER;

  PROCEDURE get_employee_name (p_employee_id IN NUMBER, p_employee_name OUT VARCHAR2);
END emp_pkg;
/

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  PROCEDURE get_employee_name (p_employee_id IN NUMBER, p_employee_name OUT VARCHAR2) IS
  BEGIN
    SELECT first_name || ' ' || last_name
    INTO p_employee_name
    FROM employees
    WHERE employee_id = p_employee_id;

    -- Update the global variable
    g_last_employee_id := p_employee_id;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      p_employee_name := 'Employee Not Found';
      g_last_employee_id := NULL; -- Clear if not found
    WHEN OTHERS THEN
      RAISE;
  END get_employee_name;
END emp_pkg;
/

-- Test the package
DECLARE
  v_emp_name VARCHAR2(100);
BEGIN
  emp_pkg.get_employee_name(100, v_emp_name);
  DBMS_OUTPUT.PUT_LINE('Employee Name for ID 100: ' || v_emp_name);
  DBMS_OUTPUT.PUT_LINE('Last Accessed Employee ID (from global var): ' || emp_pkg.g_last_employee_id);

  emp_pkg.get_employee_name(101, v_emp_name);
  DBMS_OUTPUT.PUT_LINE('Employee Name for ID 101: ' || v_emp_name);
  DBMS_OUTPUT.PUT_LINE('Last Accessed Employee ID (from global var): ' || emp_pkg.g_last_employee_id);

  emp_pkg.get_employee_name(999, v_emp_name); -- Non-existent ID
  DBMS_OUTPUT.PUT_LINE('Employee Name for ID 999: ' || v_emp_name);
  DBMS_OUTPUT.PUT_LINE('Last Accessed Employee ID (from global var): ' || emp_pkg.g_last_employee_id);
END;
/

Explanation

This example demonstrates a global package variable g_last_employee_id. This variable is declared in the package specification and its value persists across multiple calls to emp_pkg.get_employee_name within the same session. Each time get_employee_name is called, it updates g_last_employee_id with the employee_id that was just looked up. This is a simple yet powerful way to maintain session-specific data, such as tracking recent activity or user preferences. It showcases how package variables offer persistent storage for session-related data in PL/SQL applications, improving efficiency by avoiding redundant data retrieval.

Example 2: Package Global Cursor

CREATE OR REPLACE PACKAGE dept_pkg AS
  -- Global package cursor to fetch department details
  CURSOR c_all_departments IS
    SELECT department_id, department_name, location_id
    FROM departments
    ORDER BY department_name;

  PROCEDURE display_all_departments;
END dept_pkg;
/

CREATE OR OR REPLACE PACKAGE BODY dept_pkg AS
  PROCEDURE display_all_departments IS
  BEGIN
    -- Open the global cursor if not already open
    IF NOT c_all_departments%ISOPEN THEN
      OPEN c_all_departments;
    END IF;

    DBMS_OUTPUT.PUT_LINE('--- All Departments ---');
    LOOP
      FETCH c_all_departments INTO department_id, department_name, location_id;
      EXIT WHEN c_all_departments%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Dept ID: ' || department_id || ', Name: ' || department_name || ', Location: ' || location_id);
    END LOOP;
    -- Do not close the cursor here if you intend to reuse it within the session
  EXCEPTION
    WHEN OTHERS THEN
      IF c_all_departments%ISOPEN THEN
        CLOSE c_all_departments;
      END IF;
      RAISE;
  END display_all_departments;
END dept_pkg;
/

-- Test the package
BEGIN
  -- First call to display departments
  dept_pkg.display_all_departments;

  -- Second call within the same session will reuse the open cursor (or re-open if closed)
  DBMS_OUTPUT.PUT_LINE(CHR(10) || '--- Calling again within the same session ---');
  dept_pkg.display_all_departments;

  -- Close the cursor explicitly if no longer needed
  IF dept_pkg.c_all_departments%ISOPEN THEN
    CLOSE dept_pkg.c_all_departments;
  END IF;
END;
/

Explanation

This example illustrates the use of a global package cursor c_all_departments. By declaring the cursor at the package level, it can be opened and fetched from across multiple procedure calls within the same session. This is particularly useful for scenarios where you need to iterate through a result set multiple times without re-executing the query, thus boosting performance. The IF NOT c_all_departments%ISOPEN THEN OPEN c_all_departments; END IF; block ensures that the cursor is opened only if it's not already, optimizing resource usage. This technique is valuable for managing persistent data access and improving the efficiency of data retrieval in PL/SQL applications.

Example 3: Managing Session-Specific Configuration

CREATE OR REPLACE PACKAGE app_config_pkg AS
  g_debug_mode BOOLEAN := FALSE; -- Default debug mode
  g_log_level    VARCHAR2(10) := 'INFO'; -- Default log level

  PROCEDURE set_debug_mode(p_mode IN BOOLEAN);
  PROCEDURE set_log_level(p_level IN VARCHAR2);
  FUNCTION get_debug_mode RETURN BOOLEAN;
  FUNCTION get_log_level RETURN VARCHAR2;
END app_config_pkg;
/

CREATE OR REPLACE PACKAGE BODY app_config_pkg AS
  PROCEDURE set_debug_mode(p_mode IN BOOLEAN) IS
  BEGIN
    g_debug_mode := p_mode;
  END set_debug_mode;

  PROCEDURE set_log_level(p_level IN VARCHAR2) IS
  BEGIN
    -- Basic validation
    IF p_level IN ('DEBUG', 'INFO', 'WARN', 'ERROR') THEN
      g_log_level := p_level;
    ELSE
      RAISE_APPLICATION_ERROR(-20001, 'Invalid log level: ' || p_level);
    END IF;
  END set_log_level;

  FUNCTION get_debug_mode RETURN BOOLEAN IS
  BEGIN
    RETURN g_debug_mode;
  END get_debug_mode;

  FUNCTION get_log_level RETURN VARCHAR2 IS
  BEGIN
    RETURN g_log_level;
  END get_log_level;
END app_config_pkg;
/

-- Test the configuration package
BEGIN
  -- Check default values
  IF app_config_pkg.get_debug_mode THEN
    DBMS_OUTPUT.PUT_LINE('Debug Mode is ON (Default)');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Debug Mode is OFF (Default)');
  END IF;
  DBMS_OUTPUT.PUT_LINE('Log Level (Default): ' || app_config_pkg.get_log_level);

  -- Change configuration
  app_config_pkg.set_debug_mode(TRUE);
  app_config_pkg.set_log_level('DEBUG');

  -- Verify changes within the same session
  IF app_config_pkg.get_debug_mode THEN
    DBMS_OUTPUT.PUT_LINE('Debug Mode is ON (After change)');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Debug Mode is OFF (After change)');
  END IF;
  DBMS_OUTPUT.PUT_LINE('Log Level (After change): ' || app_config_pkg.get_log_level);
END;
/

Explanation

This example demonstrates how package global variables can be used to manage session-specific application configurations, such as debug modes or logging levels. The app_config_pkg stores g_debug_mode and g_log_level which can be set and retrieved by any part of the application within the same user session. This eliminates the need to pass these configuration parameters repeatedly, simplifying code and ensuring consistency. This pattern is widely used in complex PL/SQL applications for dynamic control over application behavior, making it easier to manage and troubleshoot.

Example 4: Implementing a Simple Cache

CREATE OR REPLACE PACKAGE product_cache_pkg AS
  TYPE product_rec IS RECORD (
    product_id   NUMBER,
    product_name VARCHAR2(100),
    list_price   NUMBER
  );
  TYPE product_tab IS TABLE OF product_rec INDEX BY PLS_INTEGER;

  g_product_cache product_tab;
  g_cache_loaded BOOLEAN := FALSE;

  PROCEDURE load_cache;
  FUNCTION get_product_name(p_product_id IN NUMBER) RETURN VARCHAR2;
END product_cache_pkg;
/

CREATE OR REPLACE PACKAGE BODY product_cache_pkg AS
  PROCEDURE load_cache IS
  BEGIN
    IF NOT g_cache_loaded THEN
      DBMS_OUTPUT.PUT_LINE('Loading product cache...');
      FOR rec IN (SELECT product_id, product_name, list_price FROM products) LOOP
        g_product_cache(rec.product_id).product_id := rec.product_id;
        g_product_cache(rec.product_id).product_name := rec.product_name;
        g_product_cache(rec.product_id).list_price := rec.list_price;
      END LOOP;
      g_cache_loaded := TRUE;
    END IF;
  END load_cache;

  FUNCTION get_product_name(p_product_id IN NUMBER) RETURN VARCHAR2 IS
  BEGIN
    load_cache; -- Ensure cache is loaded
    IF g_product_cache.EXISTS(p_product_id) THEN
      RETURN g_product_cache(p_product_id).product_name;
    ELSE
      RETURN 'Product Not Found in Cache';
    END IF;
  END get_product_name;
END product_cache_pkg;
/

-- Test the caching package
BEGIN
  -- First access will load the cache
  DBMS_OUTPUT.PUT_LINE('Product Name (ID 200): ' || product_cache_pkg.get_product_name(200));
  DBMS_OUTPUT.PUT_LINE('Product Name (ID 201): ' || product_cache_pkg.get_product_name(201));

  -- Subsequent accesses will use the cached data (no "Loading product cache..." message)
  DBMS_OUTPUT.PUT_LINE('Product Name (ID 200) again: ' || product_cache_pkg.get_product_name(200));
  DBMS_OUTPUT.PUT_LINE('Product Name (ID 999): ' || product_cache_pkg.get_product_name(999)); -- Non-existent
END;
/

Explanation

This example illustrates a common and highly effective use of package state: implementing a simple in-memory cache for frequently accessed data. The g_product_cache associative array (index-by table) stores product details, and g_cache_loaded acts as a flag to ensure the cache is loaded only once per session. This significantly reduces database round trips for repetitive data lookups, leading to substantial performance improvements. This pattern is essential for high-performance PL/SQL applications that deal with static or slowly changing reference data, making your applications faster and more efficient by reducing database load.

Example 5: Session Counter

CREATE OR REPLACE PACKAGE session_metrics_pkg AS
  -- Global variable to count procedure calls within the session
  g_call_count NUMBER := 0;

  PROCEDURE increment_call_count;
  FUNCTION get_call_count RETURN NUMBER;
END session_metrics_pkg;
/

CREATE OR REPLACE PACKAGE BODY session_metrics_pkg AS
  PROCEDURE increment_call_count IS
  BEGIN
    g_call_count := g_call_count + 1;
  END increment_call_count;

  FUNCTION get_call_count RETURN NUMBER IS
  BEGIN
    RETURN g_call_count;
  END get_call_count;
END session_metrics_pkg;
/

-- Test the session counter
BEGIN
  DBMS_OUTPUT.PUT_LINE('Initial call count: ' || session_metrics_pkg.get_call_count);

  session_metrics_pkg.increment_call_count;
  DBMS_OUTPUT.PUT_LINE('Call count after 1st increment: ' || session_metrics_pkg.get_call_count);

  session_metrics_pkg.increment_call_count;
  session_metrics_pkg.increment_call_count;
  DBMS_OUTPUT.PUT_LINE('Call count after 3 increments: ' || session_metrics_pkg.get_call_count);

  -- Demonstrate persistence across different blocks in the same session
  DECLARE
    v_current_count NUMBER;
  BEGIN
    v_current_count := session_metrics_pkg.get_call_count;
    DBMS_OUTPUT.PUT_LINE('Call count in a new block: ' || v_current_count);
  END;
END;
/

Explanation

This example demonstrates a simple session counter using a global package variable g_call_count. Each time increment_call_count is invoked, the counter is incremented, and its value persists throughout the user's session. This is a basic illustration of how package state can be used for session-specific metrics, auditing, or tracking user activity. While simple, this concept can be extended to more complex scenarios like tracking user preferences, performance metrics, or managing session-specific transactions, proving the versatility of package state in managing dynamic session data in PL/SQL.

The PRAGMA SERIALLY_REUSABLE Directive

The PRAGMA SERIALLY_REUSABLE directive is a compiler hint that you can apply to a package to indicate that its state should not persist across multiple SQL statements or PL/SQL anonymous blocks executed within the same session. By default, Oracle maintains the state of a package for the entire user session. However, for certain types of packages, especially those that act as utilities or provide stateless services, this default behavior can lead to unnecessary memory consumption or even data integrity issues if not carefully managed. SERIALLY_REUSABLE instructs Oracle to reset the package state (re-initialize global variables and close cursors) after each call to a packaged subprogram from SQL or after each top-level PL/SQL block that references the package. This makes the package memory-efficient and suitable for environments where session-persistent state is not desired. It's particularly useful in multi-threaded application servers or connection pools where a single database session might be reused by different application users.

Example 1: Package Without SERIALLY_REUSABLE (Default Behavior)

CREATE OR REPLACE PACKAGE non_reusable_pkg AS
  g_counter NUMBER := 0; -- Global variable

  PROCEDURE increment_and_display;
END non_reusable_pkg;
/

CREATE OR REPLACE PACKAGE BODY non_reusable_pkg AS
  PROCEDURE increment_and_display IS
  BEGIN
    g_counter := g_counter + 1;
    DBMS_OUTPUT.PUT_LINE('Non-Reusable Counter: ' || g_counter);
  END increment_and_display;
END non_reusable_pkg;
/

-- Test the default package behavior (state persists)
BEGIN
  DBMS_OUTPUT.PUT_LINE('--- Testing non_reusable_pkg (default) ---');
  non_reusable_pkg.increment_and_display; -- g_counter = 1
  non_reusable_pkg.increment_and_display; -- g_counter = 2
END;
/

BEGIN
  -- In a new anonymous block within the SAME session, the counter continues from where it left off
  DBMS_OUTPUT.PUT_LINE('--- Testing non_reusable_pkg (default) in new block ---');
  non_reusable_pkg.increment_and_display; -- g_counter = 3
END;
/

Explanation

This example demonstrates the default behavior of a package where PRAGMA SERIALLY_REUSABLE is not used. The g_counter global variable retains its value across multiple calls to increment_and_display and even across different anonymous PL/SQL blocks within the same database session. This clearly shows package state persistence, which is the standard behavior in Oracle PL/SQL. While useful for session-specific data, it can lead to unintended consequences if the package is designed to be stateless or if a shared session environment is used.

Example 2: Package With PRAGMA SERIALLY_REUSABLE

CREATE OR REPLACE PACKAGE reusable_pkg AS
  PRAGMA SERIALLY_REUSABLE; -- Directive to reset package state
  g_counter NUMBER := 0;   -- Global variable

  PROCEDURE increment_and_display;
END reusable_pkg;
/

CREATE OR REPLACE PACKAGE BODY reusable_pkg AS
  PROCEDURE increment_and_display IS
  BEGIN
    g_counter := g_counter + 1;
    DBMS_OUTPUT.PUT_LINE('Reusable Counter: ' || g_counter);
  END increment_and_and_display;
END reusable_pkg;
/

-- Test the SERIALLY_REUSABLE package behavior (state resets)
BEGIN
  DBMS_OUTPUT.PUT_LINE('--- Testing reusable_pkg (SERIALLY_REUSABLE) ---');
  reusable_pkg.increment_and_display; -- g_counter = 1
  reusable_pkg.increment_and_display; -- g_counter = 1 (resets after first call from SQL/PLSQL block)
END;
/

BEGIN
  -- In a new anonymous block within the SAME session, the counter starts from 1 again
  DBMS_OUTPUT.PUT_LINE('--- Testing reusable_pkg (SERIALLY_REUSABLE) in new block ---');
  reusable_pkg.increment_and_display; -- g_counter = 1
END;
/

Explanation

This example showcases the effect of PRAGMA SERIALLY_REUSABLE. Notice that g_counter is reset to its initial value (0, then incremented to 1) at the beginning of each anonymous PL/SQL block or each SQL statement that calls a packaged subprogram. This is because the package state is re-initialized, making the package "serially reusable" by different contexts or calls within the same session. This is critical for shared environments like connection pools, where the same database session might serve multiple application users, ensuring data isolation and preventing unintended state leakage.

Example 3: Using SERIALLY_REUSABLE for Utility Functions

CREATE OR REPLACE PACKAGE string_utils_pkg AS
  PRAGMA SERIALLY_REUSABLE;

  -- A function that might internally use a temporary variable
  FUNCTION reverse_string(p_input_string IN VARCHAR2) RETURN VARCHAR2;
END string_utils_pkg;
/

CREATE OR REPLACE PACKAGE BODY string_utils_pkg AS
  -- Even if there was a package-level variable used internally for some complex calculation,
  -- SERIALLY_REUSABLE ensures it's reset for each call.
  -- For this simple example, it's more about demonstrating the concept.
  FUNCTION reverse_string(p_input_string IN VARCHAR2) RETURN VARCHAR2 IS
    v_reversed_string VARCHAR2(4000);
  BEGIN
    FOR i IN REVERSE 1..LENGTH(p_input_string) LOOP
      v_reversed_string := v_reversed_string || SUBSTR(p_input_string, i, 1);
    END LOOP;
    RETURN v_reversed_string;
  END reverse_string;
END string_utils_pkg;
/

-- Test the utility package
BEGIN
  DBMS_OUTPUT.PUT_LINE('Original: Hello, Reversed: ' || string_utils_pkg.reverse_string('Hello'));
  DBMS_OUTPUT.PUT_LINE('Original: Oracle, Reversed: ' || string_utils_pkg.reverse_string('Oracle'));
END;
/

-- Call from SQL (demonstrates reset for each SQL context)
SELECT string_utils_pkg.reverse_string('SQL Example') FROM DUAL;
SELECT string_utils_pkg.reverse_string('Another One') FROM DUAL;

Explanation

This example illustrates a common use case for SERIALLY_REUSABLE: utility packages that offer stateless functions. The string_utils_pkg provides a reverse_string function. Even if this function (or other functions within the package) were to internally use a package-level variable for some temporary processing, PRAGMA SERIALLY_REUSABLE ensures that any such state is reset with each invocation, preventing any lingering data from affecting subsequent calls. This makes the package robust and predictable, ideal for shared utility functions in a multi-user environment where state management per call is crucial for data integrity.

Example 4: When NOT to Use SERIALLY_REUSABLE

-- This package is designed to maintain state (e.g., a session-specific ID generator)
-- and should NOT use PRAGMA SERIALLY_REUSABLE.
CREATE OR REPLACE PACKAGE session_id_generator_pkg AS
  g_current_id NUMBER := 1000;

  FUNCTION get_next_id RETURN NUMBER;
END session_id_generator_pkg;
/

CREATE OR REPLACE PACKAGE BODY session_id_generator_pkg AS
  FUNCTION get_next_id RETURN NUMBER IS
  BEGIN
    g_current_id := g_current_id + 1;
    RETURN g_current_id;
  END get_next_id;
END session_id_generator_pkg;
/

-- Test the session ID generator
BEGIN
  DBMS_OUTPUT.PUT_LINE('First ID: ' || session_id_generator_pkg.get_next_id);
  DBMS_OUTPUT.PUT_LINE('Second ID: ' || session_id_generator_pkg.get_next_id);
END;
/

BEGIN
  DBMS_OUTPUT.PUT_LINE('Third ID (new block, same session): ' || session_id_generator_pkg.get_next_id);
END;
/

Explanation

This example demonstrates a package that should not use PRAGMA SERIALLY_REUSABLE. The session_id_generator_pkg is designed to maintain a unique, incrementing ID (g_current_id) within a user's session. If SERIALLY_REUSABLE were applied here, g_current_id would reset to its initial value (1000) with each new SQL statement or PL/SQL block, defeating its purpose of generating sequential IDs across the session. This highlights the importance of understanding package state and choosing SERIALLY_REUSABLE only when a stateless or per-call state reset is explicitly desired, ensuring the integrity of session-persistent logic.

Example 5: Interaction with Cursors and SERIALLY_REUSABLE

CREATE OR REPLACE PACKAGE cursor_reusable_pkg AS
  PRAGMA SERIALLY_REUSABLE;
  
  -- Package-level cursor
  CURSOR c_employees IS
    SELECT employee_id, first_name, last_name
    FROM employees
    WHERE ROWNUM <= 3
    ORDER BY employee_id;

  PROCEDURE fetch_and_display_employees;
END cursor_reusable_pkg;
/

CREATE OR REPLACE PACKAGE BODY cursor_reusable_pkg AS
  PROCEDURE fetch_and_display_employees IS
    v_employee_id   employees.employee_id%TYPE;
    v_first_name    employees.first_name%TYPE;
    v_last_name     employees.last_name%TYPE;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Fetching employees...');
    -- With SERIALLY_REUSABLE, the cursor is automatically closed and re-opened
    -- for each top-level call, ensuring it starts from the beginning.
    IF NOT c_employees%ISOPEN THEN
      OPEN c_employees;
    END IF;

    LOOP
      FETCH c_employees INTO v_employee_id, v_first_name, v_last_name;
      EXIT WHEN c_employees%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_id || ' - ' || v_first_name || ' ' || v_last_name);
    END LOOP;

    -- The cursor will be implicitly closed by SERIALLY_REUSABLE after the call completes.
  END fetch_and_display_employees;
END cursor_reusable_pkg;
/

-- Test the cursor behavior with SERIALLY_REUSABLE
BEGIN
  DBMS_OUTPUT.PUT_LINE('--- First call to fetch_and_display_employees ---');
  cursor_reusable_pkg.fetch_and_display_employees;
END;
/

BEGIN
  DBMS_OUTPUT.PUT_LINE(CHR(10) || '--- Second call to fetch_and_display_employees (new block) ---');
  -- Due to SERIALLY_REUSABLE, the cursor will be re-opened and refetched from start
  cursor_reusable_pkg.fetch_and_display_employees;
END;
/

Explanation

This example demonstrates how PRAGMA SERIALLY_REUSABLE affects package-level cursors. When cursor_reusable_pkg is marked as SERIALLY_REUSABLE, the c_employees cursor's state is reset after each top-level call to a subprogram within the package (e.g., fetch_and_display_employees). This means that for every new SQL statement or PL/SQL block that calls this procedure, the cursor will effectively be opened anew and start fetching from the beginning of the result set. This behavior prevents unintended continuation of cursor state across separate logical operations, ensuring predictable and isolated data retrieval, which is essential for resource management and data integrity in shared environments.

The AUTHID Clause (CURRENT_USER vs. DEFINER)

The AUTHID clause in PL/SQL packages, procedures, and functions determines the privilege model under which the code executes. It specifies whether the subprogram runs with the privileges of the user who defined it (DEFINER's rights) or the user who is currently executing it (CURRENT_USER's rights). Understanding AUTHID is crucial for managing security, controlling access to underlying database objects, and ensuring the correct execution context for your PL/SQL code. This powerful feature allows you to build secure and flexible applications, protecting sensitive data while still enabling necessary operations.

DEFINER'S RIGHTS (AUTHID DEFINER)

This is the default behavior for PL/SQL stored procedures, functions, and packages. When a subprogram runs with DEFINER's rights, it executes with the privileges of the schema owner (the user who created or last compiled the object). This means that any SQL operations within the subprogram (e.g., SELECT, INSERT, UPDATE, DELETE) will succeed if the definer has the necessary privileges on the underlying objects, regardless of the privileges of the caller. This is often used for creating secure APIs, where users only need execute privilege on the package/procedure but don't need direct access to the underlying tables.

CURRENT_USER'S RIGHTS (AUTHID CURRENT_USER)

When a subprogram is explicitly created with AUTHID CURRENT_USER, it executes with the privileges of the current user (the user who is calling or invoking the subprogram). This means that for any SQL operation within the subprogram to succeed, the caller must have the necessary privileges on the underlying objects. This is useful for building generic utilities or applications where the security checks should be based on the end-user's privileges. It also impacts how unqualified object names are resolved (they are resolved in the current user's schema first).

Example 1: DEFINER'S RIGHTS Package (Default Behavior)

-- Connect as user 'APP_OWNER' (the definer)
-- Assume APP_OWNER has CREATE SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE PACKAGE privileges.
-- And APP_OWNER has privileges on EMPLOYEES table (e.g., SELECT, INSERT).

CREATE TABLE employee_logs (
    log_id      NUMBER GENERATED BY DEFAULT AS IDENTITY,
    employee_id NUMBER,
    log_message VARCHAR2(200),
    log_date    TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE OR REPLACE PACKAGE employee_api_definer AS
  -- AUTHID DEFINER is the default, so no explicit clause needed, but good for clarity.
  -- AUTHID DEFINER; -- Can be explicitly added for clarity if desired
  PROCEDURE log_employee_activity (p_employee_id IN NUMBER, p_message IN VARCHAR2);
  FUNCTION get_employee_count RETURN NUMBER;
END employee_api_definer;
/

CREATE OR REPLACE PACKAGE BODY employee_api_definer AS
  PROCEDURE log_employee_activity (p_employee_id IN NUMBER, p_message IN VARCHAR2) IS
  BEGIN
    INSERT INTO employee_logs (employee_id, log_message)
    VALUES (p_employee_id, p_message);
    COMMIT;
  END log_employee_activity;

  FUNCTION get_employee_count RETURN NUMBER IS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count FROM employees; -- APP_OWNER has SELECT on employees
    RETURN v_count;
  END get_employee_count;
END employee_api_definer;
/

-- Grant execute to a less privileged user
GRANT EXECUTE ON employee_api_definer TO APP_USER;

-- Connect as user 'APP_USER'
-- Assume APP_USER has CREATE SESSION, but NO direct privileges on EMPLOYEE_LOGS or EMPLOYEES.
-- Test as APP_USER
BEGIN
  DBMS_OUTPUT.PUT_LINE('--- Testing definer''s rights as APP_USER ---');
  employee_api_definer.log_employee_activity(100, 'Accessed employee record.');
  DBMS_OUTPUT.PUT_LINE('Employee Count: ' || employee_api_definer.get_employee_count);
  DBMS_OUTPUT.PUT_LINE('Activity logged successfully as APP_USER (via definer''s rights).');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

-- Connect back as APP_OWNER to verify the log
SELECT * FROM employee_logs WHERE employee_id = 100;

Explanation

This example demonstrates DEFINER'S RIGHTS, which is the default privilege model. The employee_api_definer package is created by APP_OWNER. Even though APP_USER has no direct privileges on the employee_logs table or the employees table, APP_USER can successfully call log_employee_activity (which performs an INSERT) and get_employee_count (which performs a SELECT) because the package executes with APP_OWNER's privileges. This is a fundamental security pattern for encapsulating sensitive data access and providing controlled interfaces to application users, enabling users to interact with data without having direct table access.

Example 2: CURRENT_USER'S RIGHTS Package

-- Connect as user 'APP_OWNER' (the definer)
-- Assume APP_OWNER has CREATE SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE PACKAGE privileges.

CREATE OR REPLACE PACKAGE employee_api_current_user AUTHID CURRENT_USER AS
  PROCEDURE log_activity (p_message IN VARCHAR2);
  FUNCTION get_session_user RETURN VARCHAR2;
END employee_api_current_user;
/

CREATE OR REPLACE PACKAGE BODY employee_api_current_user AS
  PROCEDURE log_activity (p_message IN VARCHAR2) IS
  BEGIN
    -- This insert will only work if the CURRENT_USER has INSERT privilege on employee_logs
    INSERT INTO employee_logs (log_message) VALUES ('Log by ' || USER || ': ' || p_message);
    COMMIT;
  END log_activity;

  FUNCTION get_session_user RETURN VARCHAR2 IS
  BEGIN
    RETURN USER; -- USER function returns the current user
  END get_session_user;
END employee_api_current_user;
/

-- Grant execute to a less privileged user
GRANT EXECUTE ON employee_api_current_user TO APP_USER;

-- Now, as APP_OWNER, grant APP_USER direct insert privilege on the log table for the test
GRANT INSERT ON employee_logs TO APP_USER;

-- Connect as user 'APP_USER'
-- Test as APP_USER
BEGIN
  DBMS_OUTPUT.PUT_LINE('--- Testing current_user''s rights as APP_USER ---');
  DBMS_OUTPUT.PUT_LINE('Package reports current user as: ' || employee_api_current_user.get_session_user);
  
  employee_api_current_user.log_activity('Performed an action.'); -- This will now succeed
  DBMS_OUTPUT.PUT_LINE('Activity logged successfully by APP_USER (via current_user''s rights).');

  -- Revoke the privilege for a failed scenario
  EXECUTE IMMEDIATE 'REVOKE INSERT ON APP_OWNER.employee_logs FROM APP_USER';
  DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Attempting to log after privilege revoke...');
  
  employee_api_current_user.log_activity('Attempt after revoke.'); -- This will now fail
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('As expected, logging failed after privilege revoke, demonstrating CURRENT_USER impact.');
END;
/

Explanation

This example illustrates CURRENT_USER'S RIGHTS. The employee_api_current_user package is defined with AUTHID CURRENT_USER. When APP_USER calls log_activity, the INSERT statement within the package executes with APP_USER's privileges. Initially, APP_OWNER grants APP_USER INSERT privilege on employee_logs, allowing the call to succeed. However, once that privilege is revoked, the same call fails, clearly demonstrating that the current user's privileges govern the execution. This model is ideal for scenarios where fine-grained access control based on the end-user's permissions is required, and for auditing purposes where the actions are truly attributed to the calling user.

Example 3: Resolving Unqualified Object Names with AUTHID

-- Connect as user 'HR'
-- Create a table in HR schema
CREATE TABLE hr_employees (
    emp_id   NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100)
);
INSERT INTO hr_employees VALUES (1, 'Alice');

-- Connect as user 'SCOTT'
-- Create a table in SCOTT schema
CREATE TABLE employees (
    emp_id   NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100)
);
INSERT INTO employees VALUES (10, 'Bob');

-- Connect as 'APP_OWNER' (who will define the packages)
-- APP_OWNER has SELECT privilege on HR.HR_EMPLOYEES and SCOTT.EMPLOYEES
GRANT SELECT ON HR.hr_employees TO APP_OWNER;
GRANT SELECT ON SCOTT.employees TO APP_OWNER;

CREATE OR REPLACE PACKAGE name_resolution_definer AUTHID DEFINER AS
  FUNCTION get_employee_name_definer(p_emp_id IN NUMBER) RETURN VARCHAR2;
END name_resolution_definer;
/

CREATE OR REPLACE PACKAGE BODY name_resolution_definer AS
  FUNCTION get_employee_name_definer(p_emp_id IN NUMBER) RETURN VARCHAR2 IS
    v_name VARCHAR2(100);
  BEGIN
    -- Unqualified name 'employees' will resolve to APP_OWNER's schema first.
    -- If APP_OWNER doesn't have a table named 'employees', it looks through synonyms.
    -- Assuming APP_OWNER does not have an 'employees' table,
    -- this will likely fail or require a synonym for SCOTT.employees.
    -- For this example, let's assume APP_OWNER has a synonym 'employees' for SCOTT.employees.
    -- Or, it's explicitly SCOTT.employees or HR.hr_employees below.
    SELECT emp_name INTO v_name FROM SCOTT.employees WHERE emp_id = p_emp_id;
    RETURN v_name;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN 'Not Found (Definer)';
    WHEN OTHERS THEN RETURN 'Error (Definer): ' || SQLERRM;
  END get_employee_name_definer;
END name_resolution_definer;
/

CREATE OR REPLACE PACKAGE name_resolution_current_user AUTHID CURRENT_USER AS
  FUNCTION get_employee_name_current_user(p_emp_id IN NUMBER) RETURN VARCHAR2;
END name_resolution_current_user;
/

CREATE OR REPLACE PACKAGE BODY name_resolution_current_user AS
  FUNCTION get_employee_name_current_user(p_emp_id IN NUMBER) RETURN VARCHAR2 IS
    v_name VARCHAR2(100);
  BEGIN
    -- Unqualified name 'employees' will resolve to the CURRENT_USER's schema first.
    -- If APP_USER is connected and calls this, it will look in APP_USER's schema.
    SELECT emp_name INTO v_name FROM employees WHERE emp_id = p_emp_id;
    RETURN v_name;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN 'Not Found (Current User)';
    WHEN OTHERS THEN RETURN 'Error (Current User): ' || SQLERRM;
  END get_employee_name_current_user;
END name_resolution_current_user;
/

-- Grant execute to SCOTT
GRANT EXECUTE ON name_resolution_definer TO SCOTT;
GRANT EXECUTE ON name_resolution_current_user TO SCOTT;

-- Connect as SCOTT
-- Test name resolution
BEGIN
  DBMS_OUTPUT.PUT_LINE('--- Testing Name Resolution as SCOTT ---');
  -- Definer's rights package: Will try to find SCOTT.employees, or if qualified, APP_OWNER's view of it.
  -- In this specific example, since it's qualified as SCOTT.employees in the definer's package body,
  -- it will use APP_OWNER's privilege to select from SCOTT.employees.
  DBMS_OUTPUT.PUT_LINE('Definer''s Package (EMP 10): ' || name_resolution_definer.get_employee_name_definer(10));

  -- Current user's rights package: Unqualified 'employees' will refer to SCOTT.employees
  DBMS_OUTPUT.PUT_LINE('Current User''s Package (EMP 10): ' || name_resolution_current_user.get_employee_name_current_user(10));
END;
/

Explanation

This example demonstrates how AUTHID affects the resolution of unqualified object names.

In name_resolution_definer (DEFINER'S RIGHTS), when SCOTT calls it, the SELECT emp_name FROM SCOTT.employees within the package body executes using APP_OWNER's privileges. The unqualified name employees would normally resolve against APP_OWNER's schema, and then public synonyms, etc. However, if qualified (e.g., SCOTT.employees), it uses the definer's privileges to access that specific schema's object.

In name_resolution_current_user (CURRENT_USER'S RIGHTS), when SCOTT calls it, the unqualified SELECT emp_name FROM employees explicitly attempts to find employees within SCOTT's own schema first. This is a crucial distinction when working with multi-schema environments and designing reusable code.

Example 4: Using AUTHID for Dynamic SQL Security

-- Connect as APP_OWNER

CREATE OR REPLACE PACKAGE dynamic_sql_authid AUTHID CURRENT_USER AS
  PROCEDURE execute_ddl(p_ddl_statement IN VARCHAR2);
END dynamic_sql_authid;
/

CREATE OR REPLACE PACKAGE BODY dynamic_sql_authid AS
  PROCEDURE execute_ddl(p_ddl_statement IN VARCHAR2) IS
  BEGIN
    -- This execute immediate will run with CURRENT_USER's privileges.
    -- If the current user doesn't have DDL privileges, it will fail.
    EXECUTE IMMEDIATE p_ddl_statement;
    DBMS_OUTPUT.PUT_LINE('Successfully executed: ' || p_ddl_statement);
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error executing DDL: ' || SQLERRM);
      RAISE;
  END execute_ddl;
END dynamic_sql_authid;
/

-- Grant execute to APP_USER
GRANT EXECUTE ON dynamic_sql_authid TO APP_USER;

-- Connect as APP_USER
-- Test with APP_USER's privileges
BEGIN
  DBMS_OUTPUT.PUT_LINE('--- Testing dynamic_sql_authid as APP_USER ---');
  -- This will fail as APP_USER typically does not have CREATE TABLE privilege
  BEGIN
    dynamic_sql_authid.execute_ddl('CREATE TABLE my_temp_table (id NUMBER)');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Expected error: ' || SQLERRM);
  END;

  -- This will succeed if APP_USER has INSERT privilege (granted earlier for employee_logs)
  BEGIN
    dynamic_sql_authid.execute_ddl('INSERT INTO APP_OWNER.employee_logs (log_message) VALUES (''Dynamic SQL from APP_USER'')');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error on insert: ' || SQLERRM);
  END;
END;
/

Explanation

This example demonstrates the importance of AUTHID CURRENT_USER when dealing with dynamic SQL. The dynamic_sql_authid package's execute_ddl procedure executes arbitrary DDL statements. By defining it with AUTHID CURRENT_USER, the EXECUTE IMMEDIATE statement runs with the privileges of the calling user (APP_USER in this case). This prevents a less privileged user from performing unauthorized DDL operations simply by having EXECUTE privilege on the package. This is a crucial security consideration, ensuring that dynamic SQL operations respect the caller's privileges, preventing privilege escalation vulnerabilities in PL/SQL applications.

Example 5: Mixing AUTHID with Views for Controlled Access

-- Connect as APP_OWNER

-- Create a sensitive table (e.g., salaries)
CREATE TABLE employee_salaries (
    employee_id NUMBER PRIMARY KEY,
    salary      NUMBER
);
INSERT INTO employee_salaries VALUES (100, 50000);
INSERT INTO employee_salaries VALUES (101, 75000);

-- Create a view that exposes only specific employee salaries (e.g., for managers)
CREATE OR REPLACE VIEW manager_employee_salaries AS
SELECT employee_id, salary
FROM employee_salaries
WHERE employee_id IN (100, 101); -- Example: Only specific employees are viewable

-- Grant SELECT on the view to a role or specific user
GRANT SELECT ON manager_employee_salaries TO APP_USER;

-- Package with AUTHID CURRENT_USER to access the view
CREATE OR REPLACE PACKAGE salary_viewer_cu AUTHID CURRENT_USER AS
  FUNCTION get_salary(p_employee_id IN NUMBER) RETURN NUMBER;
END salary_viewer_cu;
/

CREATE OR REPLACE PACKAGE BODY salary_viewer_cu AS
  FUNCTION get_salary(p_employee_id IN NUMBER) RETURN NUMBER IS
    v_salary NUMBER;
  BEGIN
    -- This SELECT runs with CURRENT_USER's privileges on manager_employee_salaries
    SELECT salary INTO v_salary FROM manager_employee_salaries WHERE employee_id = p_employee_id;
    RETURN v_salary;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN NULL;
  END get_salary;
END salary_viewer_cu;
/

-- Grant execute to APP_USER
GRANT EXECUTE ON salary_viewer_cu TO APP_USER;

-- Connect as APP_USER
BEGIN
  DBMS_OUTPUT.PUT_LINE('--- Testing salary_viewer_cu as APP_USER ---');
  -- This will succeed because APP_USER has SELECT on manager_employee_salaries view
  DBMS_OUTPUT.PUT_LINE('Salary for EMP 100: ' || salary_viewer_cu.get_salary(100));

  -- This will fail (return NULL as NO_DATA_FOUND) if employee_id not in the view,
  -- even if it exists in the base table, because of view's filtering.
  DBMS_OUTPUT.PUT_LINE('Salary for EMP 102 (not in view): ' || salary_viewer_cu.get_salary(102));

  -- If APP_USER tried to select directly from employee_salaries, it would fail.
  -- SELECT salary FROM APP_OWNER.employee_salaries WHERE employee_id = 100; -- Would cause ORA-00942
END;
/

Explanation

This example demonstrates a sophisticated security pattern by combining AUTHID CURRENT_USER with database views. The salary_viewer_cu package, executing with CURRENT_USER privileges, accesses a view (manager_employee_salaries) which itself provides a restricted subset of data from a sensitive table (employee_salaries). This means APP_USER can only retrieve salaries for employees visible through the view, even if the underlying package could theoretically access the full employee_salaries table. This approach ensures that data access is strictly governed by the caller's privileges on the intermediary view, providing a powerful and flexible method for implementing fine-grained access control and enhancing data security in Oracle PL/SQL applications.