Edition name as a property for JDBC thin client

A question on Edition-Based Redefinition appeared on the OTN forum:

The purpose is to try to upgrade an app in production. For most application users they will keep using the pre-upgrade app connecting to the old edition by default, while at the same time some UAT users will test out the post-upgraded app on the new edition. The question is how will the Java app connect to a user schema to use the new edition


There are many documented ways to specify Edition for an application:

  • direct ALTER SESSION call from the application itself
  • indirect ALTER SESSION call from the application; for example, in the WebLogic it is possible to specify Init SQL for pooled connections
  • AFTER LOGON trigger with some code to distinguish connections of the new, upgraded application and again – ALTER SESSION call
  • There is another more convenient way to perform the task – using property oracle.jdbc.editionName. Here is a simple demonstration:

    drop edition version2 cascade;
    alter user tim enable editions;
    create edition version2 as child of ora$base;
    import java.sql.*;
    import java.util.*;
    
    public class testEBR {
        public static void main(String[] args) throws SQLException, ClassNotFoundException {
            Class.forName("oracle.jdbc.OracleDriver");
            Properties p = new Properties();
            p.put("user", "tim");
            p.put("password", "t");
            Connection conn1 = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:test11g2", p);
            System.out.println(conn1.getMetaData().getDriverVersion());
    
            p.put("oracle.jdbc.editionName", "version2");
            Connection conn2 = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:test11g2", p);
    
            test(conn1);
            test(conn2);
        }
        public static void test(Connection conn) throws SQLException {
        	ResultSet rs = conn.createStatement().executeQuery(
        		"select sys_context('userenv', 'current_edition_name') from dual");
        	rs.next();
        	System.out.println(rs.getString(1));
        }
    }

    And the output:

    [oracle@localhost tests]$ java -classpath .:$ORACLE_HOME/jdbc/lib/ojdbc6.jar testEBR
    11.2.0.1.0
    ORA$BASE
    VERSION2

    This actually works with older 11gR1 JDBC drivers too:

    [oracle@localhost tests]$ java -classpath .:/home/oracle/Desktop/ojdbc6-11107.jar testEBR
    11.1.0.7.0-Production
    ORA$BASE
    VERSION2
    [oracle@localhost tests]$ java -classpath .:/home/oracle/Desktop/ojdbc6-11106.jar testEBR
    11.1.0.6.0-Production+
    ORA$BASE
    VERSION2
    

    The property can also be supplied as a startup argument:

    [oracle@localhost tests]$ java -classpath .:$ORACLE_HOME/jdbc/lib/ojdbc6.jar -Doracle.jdbc.editionName=version2 testEBR
    11.2.0.1.0
    VERSION2
    VERSION2

    The property isn’tdocumented yet, it is mentioned on a JDBC page only, and strangely it is claimed 11gR2 JDBC driver new feature which is obviously not true.
    I’m still concerned on the original idea of the OP – I don’t know if it’s possible to have both old and new versions of read-write application co-exist some time and I’m inclined to “no, it’s not possible in the generic case”.
    By the way, this approach does not use ALTER SESSION call, which means oracle.jdbc.editionName property is native JDBC thin driver feature.

    Advertisements

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s