Database Reference
In-Depth Information
password
VARCHAR2 (10) NOT NULL,
active_flag
VARCHAR2 (1) NOT NULL
);
-- Create sequence for IDs
CREATE SEQUENCE sn_users;
-- Create Users
-- Note: You should not store passwords in
clear text.
-- This was done for demonstration purposes.
INSERT INTO tusers ( user_id, user_name,
password, active_flag)
VALUES (sn_users.NEXTVAL, 'martin',
'martin', 'Y');
INSERT INTO tusers ( user_id, user_name,
password, active_flag)
VALUES (sn_users.NEXTVAL, 'chris',
'chris', 'Y');
INSERT INTO tusers ( user_id, user_name,
password, active_flag)
VALUES (sn_users.NEXTVAL, 'cameron',
'cameron', 'Y');
-- Authentication Function
CREATE OR REPLACE FUNCTION f_login (p_username
IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN
AS
v_count
PLS_INTEGER;
BEGIN
SELECT COUNT (user_id)
INTO v_count
FROM tusers
WHERE LOWER (user_name) = LOWER (p_username)
AND password = p_password
