Connection initialization for Oracle databases

WebLogic application servers

Use the WebLogic Administration Console to specify a SQL statement that initializes each data source connection before WebLogic adds that connection to the pool.

If you have more than one statement to execute, you can put the statements into a SQL block. For example:

SQL BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('PolicyCenter_PCPROD1', NULL);
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = BINARY_CI';
END;

Non-weblogic application servers

One way to initialize the connections appropriately is to create an Oracle logon trigger for the owner of the database schema. The following sample code illustrates how to create a log-in trigger. You must be logged on as the system user to execute this trigger.

CREATE OR REPLACE TRIGGER
AFTER LOGON ON DATABASE
BEGIN

 IF (USER = 'Guidewire PCPROD1')
    THEN         
      DBMS_APPLICATION_INFO.SET_MODULE('PolicyCenter_PCPROD1', NULL);
      EXECUTE IMMEDIATE  'ALTER SESSION SET NLS_SORT = BINARY_CI';
END IF;

 END;
/