CREATE TABLE TW1SESS
(
  TW1SESS_ID        VARCHAR2(20),
  TW1SESS_TIME      DATE,
  TW1SESS_VALUE_1   VARCHAR2(100),
  TW1SESS_VALUE_2   VARCHAR2(100),
  TW1SESS_VALUE_3   VARCHAR2(100),
  TW1SESS_VALUE_4   VARCHAR2(100),
  TW1SESS_VALUE_5   VARCHAR2(100),
  TW1SESS_VALUE_6   VARCHAR2(100),
  TW1SESS_VALUE_7   VARCHAR2(100),
  TW1SESS_VALUE_8   VARCHAR2(100),
  TW1SESS_VALUE_9   VARCHAR2(100),
  TW1SESS_VALUE_10  VARCHAR2(1000),
  TW1SESS_VALUE_11  VARCHAR2(1000),
  TW1SESS_VALUE_12  VARCHAR2(1000),
  TW1SESS_VALUE_13  VARCHAR2(1000),
  TW1SESS_VALUE_14  VARCHAR2(1000),
  TW1SESS_VALUE_15  VARCHAR2(1000),
  TW1SESS_VALUE_16  VARCHAR2(1000),
  TW1SESS_VALUE_17  VARCHAR2(1000),
  TW1SESS_VALUE_18  VARCHAR2(1000),
  TW1SESS_VALUE_19  VARCHAR2(1000),
  TW1SESS_VALUE_20  VARCHAR2(1000),
  TW1SESS_PIDM      NUMBER
);

CREATE OR REPLACE PACKAGE twgzsess
AS
/******************************************************************************
   NAME:       twgzsess
   PURPOSE:    Create sessions for custom web applications at 
               Fuller Theological Seminary

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        7/15/2004   Edward Stoever   1. Created this package body.
******************************************************************************/
   FUNCTION sess
      RETURN tw1sess%ROWTYPE;

   PROCEDURE set_session_variable (
      sess_var               IN OUT   tw1sess%ROWTYPE,
      sess_variable_number   IN       NUMBER,
      sess_variable_value    IN       VARCHAR2
   );

   PROCEDURE test_page;
END twgzsess;
/

/* Formatted on 2004/07/15 08:50 (Formatter Plus v4.8.0) */
CREATE OR REPLACE PACKAGE BODY twgzsess
AS
/******************************************************************************
   NAME:       twgzsess
   PURPOSE:    Create sessions for custom web applications at
               Fuller Theological Seminary

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        7/15/2004   Edward Stoever   1. Created this package body.

   Notes:

   Usage:

      PROCEDURE typical_PLSQL_webpage
   IS
      sess_value   tw1sess%ROWTYPE;
   BEGIN

   -- If no login, there will be an error,
   -- and the page will not load

      sess_value := twgzsess.sess;

   -- To set a session variable, simply call the following
   -- procedure at any point in the program:

     twgzsess.set_session_variable (sess_value,
                            3,
                            'This is the text for session variable number 3'
                           );

     twgzsess.set_session_variable (sess_value,
                            7,
                            'This is the text for session variable number 7'
                           );

   END;

   Once a session variable is used by a given application
   (ie, #1, #2, #3 or #10) it would be wise not to use it
   by another production application. It is possible to create
   a conflict if one person attempts to make changes to a session
   variable in one application, then makes use of a different
   application in the same session. So, Once a variable is
   used by a production application, make note of it here:

#1 (varchar2(100)) :
#2 (varchar2(100)) :
#3 (varchar2(100)) :
#4 (varchar2(100)) :
#5 (varchar2(100)) :
#6 (varchar2(100)) :
#7 (varchar2(100)) :
#8 (varchar2(100)) :
#9 (varchar2(100)) :
#10 (varchar2(1000)) :
#11 (varchar2(1000)) :
#12 (varchar2(1000)) :
#13 (varchar2(1000)) :
#14 (varchar2(1000)) :
#15 (varchar2(1000)) :
#16 (varchar2(1000)) :
#17 (varchar2(1000)) :
#18 (varchar2(1000)) :
#19 (varchar2(1000)) :
#20 (varchar2(1000)) :

   These variables can be used in any order. There is no need to use #1 before #2, etc.


******************************************************************************/
   FUNCTION verify_numeric (check_this VARCHAR2)
      RETURN BOOLEAN
   IS
   BEGIN
      IF LTRIM (TRANSLATE (check_this, '123456789', '000000000'), '0') IS NOT NULL
      THEN
         RETURN FALSE;                           -- check_this is not numeric
      ELSE
         RETURN TRUE;                                -- check_this is numeric
      END IF;
   END verify_numeric;

   FUNCTION sess
      RETURN tw1sess%ROWTYPE
   IS
      cookie_value                 VARCHAR2 (2000)   := '';
      -- Used to store value of decoded cookie
      not_logged_in                EXCEPTION;
      last_digit_of_cookie_value   VARCHAR2 (1);
      sess_full_value              tw1sess%ROWTYPE;
      pidm                         NUMBER;
   BEGIN
      twgkwbis.p_fetchsecurecookie (cookie_value);
       --  for testing:
      --  cookie_value := 'xyz001';
      last_digit_of_cookie_value :=
                              SUBSTR (cookie_value, LENGTH (cookie_value), 1);

      IF LENGTH (cookie_value) > 1
      THEN
         IF verify_numeric (last_digit_of_cookie_value) = TRUE
         THEN
            -- make sure cookie_value does not exceed 20 characters
            pidm :=
                  TO_NUMBER (SUBSTR (cookie_value, twgkglib.f_pinlength + 1));
            cookie_value := SUBSTR (TRIM (cookie_value), 1, 20);

            -- here we set the session variables:
            UPDATE tw1sess
               SET tw1sess_time = SYSDATE
             WHERE tw1sess_id = cookie_value
               AND tw1sess_time > SYSDATE - 1 / 24;

            -- expire the session after one hour of inactivity
            IF (SQL%ROWCOUNT <> 0)
            THEN
               -- this session has been established, so simply delete
               -- expired sessions.
               DELETE FROM tw1sess
                     WHERE tw1sess_time < SYSDATE - 1 / 24;

               COMMIT;
            ELSE
               --this is a new valid session for this program unit
               INSERT INTO tw1sess
                           (tw1sess_id, tw1sess_time, tw1sess_pidm
                           )
                    VALUES (cookie_value, SYSDATE, pidm
                           );

               DELETE FROM tw1sess
                     WHERE tw1sess_time < SYSDATE - 1 / 24;

               COMMIT;
            END IF;

            SELECT tw1sess_id,
                   tw1sess_time,
                   tw1sess_value_1,
                   tw1sess_value_2,
                   tw1sess_value_3,
                   tw1sess_value_4,
                   tw1sess_value_5,
                   tw1sess_value_6,
                   tw1sess_value_7,
                   tw1sess_value_8,
                   tw1sess_value_9,
                   tw1sess_value_10,
                   tw1sess_value_11,
                   tw1sess_value_12,
                   tw1sess_value_13,
                   tw1sess_value_14,
                   tw1sess_value_15,
                   tw1sess_value_16,
                   tw1sess_value_17,
                   tw1sess_value_18,
                   tw1sess_value_19,
                   tw1sess_value_20,
                   tw1sess_pidm
              INTO sess_full_value
              FROM tw1sess
             WHERE tw1sess_id = cookie_value AND ROWNUM = 1;

            RETURN sess_full_value;
         ELSE
            RAISE not_logged_in;
         END IF;
      ELSE
         RAISE not_logged_in;
      END IF;
   EXCEPTION
      WHEN not_logged_in
      THEN
         /*        HTP.PRINT
                 ('
You are not logged in.
');*/ raise_application_error (-20011, 'Browser is not logged in or session has expired.' ); WHEN OTHERS THEN /* HTP.PRINT ('
An error has occured
'); */ raise_application_error (-20012, 'An undetermined error has occured in TWGZSESS.SESS.' ); END sess; PROCEDURE set_session_variable ( sess_var IN OUT tw1sess%ROWTYPE, sess_variable_number IN NUMBER, sess_variable_value IN VARCHAR2 ) /* sess_variable_number should be an integer between 1 and 20 inclusive */ IS var_val VARCHAR2 (2000); var_num VARCHAR2 (2); var_sess_id VARCHAR2 (20) := sess_var.tw1sess_id; sqlstr VARCHAR2 (200); BEGIN var_num := TO_CHAR (sess_variable_number); IF sess_variable_number < 10 THEN var_val := SUBSTR (sess_variable_value, 1, 100); ELSE var_val := SUBSTR (sess_variable_value, 1, 1000); END IF; IF sess_variable_number < 21 THEN sqlstr := 'update tw1sess set tw1sess_value_' || var_num || ' = ''' || REPLACE (var_val, '''', '''''') || ''' where tw1sess_id = ''' || var_sess_id || ''''; EXECUTE IMMEDIATE sqlstr; COMMIT; -- for debugging: -- HTP.PRINT ('
');
         -- HTP.PRINT (sqlstr);
         -- HTP.PRINT ('
'); SELECT tw1sess_id, tw1sess_time, tw1sess_value_1, tw1sess_value_2, tw1sess_value_3, tw1sess_value_4, tw1sess_value_5, tw1sess_value_6, tw1sess_value_7, tw1sess_value_8, tw1sess_value_9, tw1sess_value_10, tw1sess_value_11, tw1sess_value_12, tw1sess_value_13, tw1sess_value_14, tw1sess_value_15, tw1sess_value_16, tw1sess_value_17, tw1sess_value_18, tw1sess_value_19, tw1sess_value_20, tw1sess_pidm INTO sess_var FROM tw1sess WHERE tw1sess_id = var_sess_id AND ROWNUM = 1; END IF; END set_session_variable; PROCEDURE test_page /* this test_page procedure is an example of how the sess funcion can be used */ /* to make this procedure work, you must first login to campus pipeline, click the STUDENT DIRECTORY link, then view this procedure. For example, paste the following link into the page: http://lion.fuller.edu:7777/TEST/wtailor.twgzsess.test_page */ IS sess_value tw1sess%ROWTYPE; BEGIN sess_value := twgzsess.sess; twgzsess.set_session_variable (sess_value, 1, 'Noodles are delicious!'); HTP.PRINT ( '
Test page!'
                 || CHR (10)
                 || sess_value.tw1sess_id
                 || CHR (10)
                 || TO_CHAR (sess_value.tw1sess_time, 'MON-DD-YYYY HH:MI:SS')
                 || CHR (10)
                 || sess_value.tw1sess_value_1
                 || CHR (10)
                 || sess_value.tw1sess_value_2
                 || CHR (10)
                 || sess_value.tw1sess_value_3
                 || CHR (10)
                 || sess_value.tw1sess_value_4
                 || CHR (10)
                 || sess_value.tw1sess_value_5
                 || CHR (10)
                 || sess_value.tw1sess_value_6
                 || CHR (10)
                 || sess_value.tw1sess_value_7
                 || CHR (10)
                 || sess_value.tw1sess_value_8
                 || CHR (10)
                 || sess_value.tw1sess_value_9
                 || CHR (10)
                 || sess_value.tw1sess_value_10
                 || CHR (10)
                 || sess_value.tw1sess_value_11
                 || CHR (10)
                 || sess_value.tw1sess_value_12
                 || CHR (10)
                 || sess_value.tw1sess_value_13
                 || CHR (10)
                 || sess_value.tw1sess_value_14
                 || CHR (10)
                 || sess_value.tw1sess_value_15
                 || CHR (10)
                 || sess_value.tw1sess_value_16
                 || CHR (10)
                 || sess_value.tw1sess_value_17
                 || CHR (10)
                 || sess_value.tw1sess_value_18
                 || CHR (10)
                 || sess_value.tw1sess_value_19
                 || CHR (10)
                 || sess_value.tw1sess_value_20
                 || CHR (10)
                 || sess_value.tw1sess_pidm
                 || CHR (10)
                 || '
' ); twgzsess.set_session_variable (sess_value, 2, 'This is a test string. It''s my birtday! Wait!' ); twgzsess.set_session_variable (sess_value, 3, 'What''s up doc?'); twgzsess.set_session_variable (sess_value, 19, 'Yo! Hey! I want to write something!' ); END test_page; END twgzsess; /