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;
/