-- create_session_structures.sql
-- Chapter 13, Oracle Database XE PHP Web Programming
-- by Michael McLaughlin
--
-- This query builds a SESSION_LOG table, a sequence for the table, and
-- two DBMS_APPLICATION_INFO wrapper procedures READ_CLIENT_INFO and
-- GET_CLIENT_INFO,

-- Conditionally drop table and sequence when they're there.
BEGIN
  FOR i IN (SELECT   null
            FROM     user_tables
            WHERE    table_name = 'SESSION_LOG') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE session_log CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT   null
            FROM     user_sequences
            WHERE    sequence_name = 'SESSION_LOG_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE session_log_s1';
  END LOOP;
END;
/

-- Define SESSION_LOG table.
CREATE TABLE session_log
( session_id        NUMBER
, session_activity  VARCHAR2(3)
, session_name      VARCHAR2(64)
, session_date      DATE);

-- Declare sequence.
CREATE SEQUENCE session_log_s1;


-- Define package specification.
CREATE OR REPLACE PACKAGE session_manager IS

  PROCEDURE set_session
  (session_name IN VARCHAR2);
  
  PROCEDURE get_session
  (session_name IN OUT VARCHAR2);

END session_manager;
/

-- Define package body implementation.
CREATE OR REPLACE PACKAGE BODY session_manager IS

  PROCEDURE set_session
  (session_name IN VARCHAR2) IS

  BEGIN

    -- Set the V$SESSION.CLIENT_INFO column for the session.
    dbms_application_info.set_client_info(session_name);

    -- Record and commit activity.
    INSERT INTO session_log VALUES
    ( session_log_s1.nextval
    ,'SET'
    , session_name
    , SYSDATE );
    
    COMMIT;

  END set_session;
  
  PROCEDURE get_session
  (session_name IN OUT VARCHAR2) IS

  BEGIN

    dbms_application_info.read_client_info(session_name);

    -- Conditionally record and commit read activities.
    IF session_name IS NOT NULL THEN
    
      -- Record and commit activity.
      INSERT INTO session_log VALUES
      ( session_log_s1.nextval
      ,'GET'
      , session_name
      , SYSDATE );
    
      COMMIT;
      
    END IF;

  END get_session;

END session_manager;
/
