23 Database Resident Connection Pooling

Example 23-2 Example of PL/SQL Fix-Up Callback

If the server does not find an idle connection with the desired tag(s), then it executes preconfigured PL/SQL procedures immediately after attaching the server connection to fix up the session state to the desired one. For example, the following code snippet updates the NLS_LANGUAGE to the desired value:

CREATE OR REPLACE PACKAGE mycb_pack AS 
PROCEDURE mycallback (
 desired_props IN VARCHAR2, 
 actual_props IN VARCHAR2); 
END; 
/ 

CREATE OR REPLACE PACKAGE BODY mycb_pack AS 
PROCEDURE mycallback (
 desired_props IN VARCHAR2,
 actual_props IN VARCHAR2
 ) IS
 BEGIN
   IF(desired_props = 'nls_lang=it') THEN 
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE=Italian'; 
   ELSIF(desired_props = 'nls_lang=gr') THEN 
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE=German'; 
   ELSE EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE=American';
   END IF; 
 END; 
END mycb_pack;/

You can then configure the callback in JDBC as shown in the following example:

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;

public class PLSQLCallback {
  static final String URL = "jdbc:oracle:thin:@drcpinst";
  static final String USER = "myusr";
  static final String PWD = "mypwd";

  public static void main(String a[]) throws Exception {
    OracleDataSource ods = new OracleDataSource();
    ods.setURL(URL);
    ods.setUser(USER);
    ods.setPassword(PWD);
    Properties props = new Properties();
    props.put("oracle.jdbc.DRCPConnectionClass", "SYS_DEFAULT_CONNECTION_POOL");
    props.put(OracleConnection.CONNECTION_PROPERTY_USE_DRCP_MULTIPLE_TAG,
                                "true");
    // Default NLS LANGUAGE is American
    props.put(OracleConnection.CONNECTION_PROPERTY_DRCP_TAG_NAME,
                                "nls_lang=am");
    props.put(OracleConnection.CONNECTION_PROPERTY_DRCP_PLSQL_CALLBACK,
                                "mycb_pack.mycallback");
    ods.setConnectionProperties(props);
    OracleConnection conn1 = (OracleConnection) ods.getConnection();
    updateNLSLangValue(conn1, "Italian");
    // Detach the session with the tag Italian
    conn1.detachServerConnection("nls_lang=it");

    OracleConnection conn2 = (OracleConnection) ods.getConnection();
    updateNLSLangValue(conn2, "German");
    // Detach the session with the tag German
    conn2.detachServerConnection("nls_lang=gr");

    OracleConnection conn3 = (OracleConnection) ods.getConnection();
    updateNLSLangValue(conn3, "American");
    // Detach the session with the tag American
    conn3.detachServerConnection("nls_lang=am");
                
    // Attaching the connection will execute the
    // PLSQL Callback and restore the NLS_LANG
    // to Italian
    conn1.attachServerConnection();
    printNLSLangValue(conn1);
    conn1.detachServerConnection("nls_lang=it");

    // Attaching the connection will execute the
    // PLSQL Callback and restore the NLS_LANG
    // to German
    conn2.attachServerConnection();
    printNLSLangValue(conn2);
    conn2.detachServerConnection("nls_lang=gr");

    // Attaching the connection will execute the
    // PLSQL Callback and restore the NLS_LANG
    // to American
    conn3.attachServerConnection();
    printNLSLangValue(conn3);
    conn3.detachServerConnection("nls_lang=am");
  }
        
  private static void printNLSLangValue(OracleConnection conn)
      throws Exception {
  Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(
        "SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_LANGUAGE'");
    if (rs.next()) {
        System.out.println("Current Session NLS_LANGUANGE : " + rs.getString(1));
    }
    rs.close();
    stmt.close();
  }
        
  private static void updateNLSLangValue(OracleConnection conn, String value)
      throws Exception {
    Statement stmt = conn.createStatement();
    stmt.execute("ALTER SESSION SET NLS_LANGUAGE='" + value + "'");
    stmt.close();
  }
}