2 Replies Latest reply: Jul 20, 2012 12:13 AM by 79426 RSS

    Oracle Connection Release Issue with Latest Driver not with the legacy one

    79426

      Hi

      I came across a scenario in the http://www.oracleutilities.com/Packages/dbms_alert.html, which discuss about the usage of the dbms_alert (Alert.java program). I used this example with classes12.jar and terminated the program abnormally without raising the signal. When i checked the oracle session in v$session table i didn't see any stale session, however when i used the ojdbc6_g.jar (followed the same step as i mentioned earlier for classses12.jar) i found the connection is not released. Any help/pointers would be highly appreciated

      More information of driver and db are given below


      classes12.jar

      Manifest-Version: 1.0
      Specification-Title: "Oracle JDBC driver classes for use with JDK1.2 and JDK1.3"
      Specification-Version: "Oracle JDBC Driver version - 9.0.2.0.0"
      Specification-Vendor: "Oracle Corporation" .
      Implementation-Title: "classes12 archives"
      Implementation-Version: "Oracle JDBC Driver version - 9.0.2.0.0"
      Implementation-Vendor: "Oracle Corporation"
      Implementation-Time: "Fri Jun 9 04:27:20 2006"



      ojdbc6_g.jar

      Manifest-Version: 1.0
      Implementation-Vendor: Oracle Corporation
      Implementation-Title: ojdbc6_g.jar
      Implementation-Version: Oracle JDBC Driver version - "11.1.0.7.0-Produ
      ction"
      Implementation-Time: Thu Aug 28 18:32:51 2008
      Specification-Vendor: Oracle Corporation
      Created-By: 1.6.0 (Sun Microsystems Inc.)
      Specification-Title: Oracle JDBC driver classes for use with JDK6
      Specification-Version: Oracle JDBC Driver version - "11.1.0.7.0-Produc
      tion"
      Main-Class: oracle.jdbc.OracleDriver


      Database & OS info

      OS : Linux bld-qa-ts26 2.6.32-200.13.1.el5uek #1 SMP Wed Jul 27 21:02:33 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux
      ORACLE VERSION : 11GR2 ( 11.2.0.2.0 ) SE

        • 1. Re: Oracle Connection Release Issue with Latest Driver not with the legacy one
          rp0428
          >
          I used this example with classes12.jar and terminated the program abnormally without raising the signal. When i checked the oracle session in v$session table i didn't see any stale session, however when i used the ojdbc6_g.jar (followed the same step as i mentioned earlier for classses12.jar) i found the connection is not released. Any help/pointers would be highly appreciated
          . . .
          ORACLE VERSION : 11GR2 ( 11.2.0.2.0 ) SE
          >
          Let me see if I understand this right
          1. You are using an example from several years ago
          2. The example is for code that captures the LOGON to the database
          3. You don't mention anything about whether you got the LOGON notification but mention that you terminate the program abnormally.
          4. You use a jar file that has been obsolete for over 10 years
          5. You use the latest version of the Oracle database (which the jar file does not support)

          Did I get that right?

          And this surprises you?

          Why on Earth would you do this? You should be using an example and jar files that support and are supported by the Database and JDK that your are using. When you don't you can expect to have problems and many of them you will not be able to resolve.

          You don't provide the JDK version that you are using but there's a good chance it is the wrong version for this hodgepodge also.

          1. Use JDK 1.6
          2. Use OJDBC6 jar
          3. Use examples for Oracle 11g

          Here is the official Oracle JDBC page that shows the Oracle DB versions supported for each of the JDBC drivers available and also describes the JDBC jars and what JDK versions they support.
          http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#02_01
          >
          Which JDBC drivers support which versions of Oracle Database?
          JDBC 9.0.1 drivers can talk to RDBMS
          11.1.0
          . . .

          . . .
          classes12.jar
          Classes for the Thin and OCI drivers when using a Java 1.2 or 1.3 VM.
          >
          And here is the PL/SQL Packages and Types doc that has an example in Chap 18 DBMS_ALERT (not for LOGON)
          http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_alert.htm#BEHBCFCF
          >
          Examples
          Suppose you want to graph average salaries by department, for all employees. Your application needs to know whenever EMP is changed. Your application would look similar to this code:

          DBMS_ALERT.REGISTER('emp_table_alert');
          <<readagain>>:
          /* ... read the emp table and graph it */
          DBMS_ALERT.WAITONE('emp_table_alert', :message, :status);
          if status = 0 then goto <<readagain>>; else
          /* ... error condition */

          The EMP table would have a trigger similar to this:
          CREATE TRIGGER emptrig AFTER INSERT OR UPDATE OR DELETE ON emp
          BEGIN
          DBMS_ALERT.SIGNAL('emp_table_alert', 'message_text');
          END;

          When the application is no longer interested in the alert, it makes this request:

          DBMS_ALERT.REMOVE('emp_table_alert');

          This reduces the amount of work required by the alert signaller. If a session exits (or dies) while registered alerts exist, the alerts are eventually cleaned up by future users of this package.

          The example guarantees that the application always sees the latest data, although it may not see every intermediate value.
          • 2. Re: Oracle Connection Release Issue with Latest Driver not with the legacy one
            79426
            Hi,

            Thanks for response, my apologies for not mentioning the JDK version, i am using JDK 1.6 also not provided some context behind why i using the classes12.jar

            I do understand that the driver is out dated and why i should be using it. The scenario is i am working on a project where i should migrate the legacy driver to latest one ojdbc6.jar. When i migrate to latest one i didn't see any issue apart from what i see is connections are not released if i terminate the application abnormally. For instance if i KILL the process.

            My responses to your query


            +1. You are using an example from several years ago+
            Just to point the scenario, i made use of it


            +2. The example is for code that captures the LOGON to the database+
            If my understanding is correct, it explains the LOGON and receiving notification from DB also.

            +3. You don't mention anything about whether you got the LOGON notification but mention that you terminate the program abnormally.+
            I am testing the scenario, that the application connects to database, register for a notification and it is waiting for any signal to occur. Instead of raising the signal, i am terminating the application to see how the system behaves

            +4. You use a jar file that has been obsolete for over 10 years+
            Yes, I am working on legacy project. So i just to verified it with classes12.jar

            +5. You use the latest version of the Oracle database (which the jar file does not support)+
            I concur with what you say. I am facing the issue when i do the migration to latest one.