Initializing a connection from a connection pool

Oracle may maintain state in a database connection that can cause difficult to diagnose problems.

Packages may maintain state and context variables may be set.

After obtaining a connection from a connection pool one should prepare the connection. An easy way to do this is to create the following procedure and call immediately after obtaining a connection.

Java developers should be able to configure the datasource externally and not have to change any code.

PROCEDURE prepare_connection is
    context_info DBMS_SESSION.AppCtxTabTyp;
    info_count   PLS_INTEGER;
    indx         PLS_INTEGER;
BEGIN
    DBMS_SESSION.LIST_CONTEXT ( context_info, info_count);
    indx := context_info.FIRST; 
    LOOP
       EXIT WHEN indx IS NULL;
       DBMS_SESSION.CLEAR_CONTEXT(
           context_info(indx).namespace,
           context_info(indx).attribute,
          null
        );
       indx := context_info.NEXT (indx);
   END LOOP;
   DBMS_SESSION.RESET_PACKAGE;
END prepare_connection;