-- Set the NLS_DATE_FORMAT for individual users at LOGON according to
-- entries made in an attribute table
-- Copyright (c) 2004, Caleb.com

-- Create the attribute table with some sample data
DROP TABLE cs_ses_atb;
CREATE TABLE cs_ses_atb (
   usr         VARCHAR2(32),
   nls_date_f  VARCHAR2(200));
INSERT INTO cs_ses_atb VALUES ('CALEB','dd-mon-yyyy hh24:mi:ss');
INSERT INTO cs_ses_atb VALUES ('SCOTT' ,'day dd month yyyy hh:mi:ssPM');
INSERT INTO cs_ses_atb VALUES ('HR' ,'ddth "of" month yyyy hh:mi:ss');
COMMIT;

-- Fire trigger when any user logs on
CREATE OR REPLACE TRIGGER cs_logon2
AFTER LOGON ON DATABASE
DECLARE
   v_nls_f  VARCHAR2(200);  -- date format string
BEGIN
   --
   -- Attempt to lookup attribute record for this user
   --
   debug1('ora_login_user="'||ora_login_user||'"');
   SELECT nls_date_f INTO v_nls_f
      FROM cs_ses_atb
      WHERE usr = ora_login_user;
   --
   -- If successful, go ahead and set attributes.  Note use of dynamic SQL
   ---
   debug1('v_nls_f="'||v_nls_f||'"');
   EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format = '''||v_nls_f||'''';

EXCEPTION
   -- If no record was found then handle exception for clean exit
   WHEN NO_DATA_FOUND THEN
      debug1('No record found');
      NULL;
END;
/