JDBC Session Stays In The Database After The Java Program Was Canceled Using CTRL + C (Doc ID 1394687.1)

Last updated on MARCH 08, 2017

Applies to:

JDBC - Version 11.1.0.7 and later
Information in this document applies to any platform.

Symptoms

ACTUAL BEHAVIOR

An application that is using JDBC Thin driver changes in behavior when it is migrated from 10g to 11g version.


EXPECTED BEHAVIOR

It is expected that using the 11g JDBC driver the CTRL + C closes the connection to the database, but also in the database the session is also closed.

STEPS

conn / as sysdba
drop user router cascade;

create user router identified by abcde123 default tablespace users temporary tablespace temp;
grant resource, connect, dba to router;

GRANT EXECUTE ON DBMS_AQADM to router;
GRANT EXECUTE ON DBMS_AQ TO router;



conn router/abcde123

-- create queue and de-qeueue function


CREATE OR REPLACE TYPE ROUTER.MSG_TYP AS OBJECT (MSG_ID NUMBER)
/


BEGIN
  SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
  (  QUEUE_TABLE => 'ROUTER.POOLENGINE0_Q_TBL'
    ,QUEUE_PAYLOAD_TYPE => 'ROUTER.MSG_TYP'
    ,COMPATIBLE => '10.0.0'
    ,SORT_LIST => 'PRIORITY'
    ,MULTIPLE_CONSUMERS => FALSE
    ,MESSAGE_GROUPING => 0
    ,SECURE => FALSE
  );
End;
/


BEGIN
  SYS.DBMS_AQADM.CREATE_QUEUE
  (
     QUEUE_NAME => 'ROUTER.POOLENGINE0_Q'
    ,QUEUE_TABLE => 'ROUTER.POOLENGINE0_Q_TBL'
    ,QUEUE_TYPE => SYS.DBMS_AQADM.NORMAL_QUEUE
    ,MAX_RETRIES => 5000000
    ,RETRY_DELAY => 0
    ,RETENTION_TIME => 0
  );
END;
/

BEGIN
  SYS.DBMS_AQADM.START_QUEUE
  (
     QUEUE_NAME => 'ROUTER.POOLENGINE0_Q'
    ,ENQUEUE => TRUE
    ,DEQUEUE => TRUE
  );
END;
/


CREATE OR REPLACE FUNCTION ROUTER.get_messages RETURN INT
IS
  deque_options SYS.DBMS_AQ.dequeue_options_t;
  msg_prop SYS.DBMS_AQ.message_properties_t;
  msg_hndl RAW (16);
  msg msg_typ;
  qnm VARCHAR2 (30) := 'ROUTER.POOLENGINE0_Q';
  lcl_cnt INT := 0;
  q_empty_tmo EXCEPTION;
BEGIN
  deque_options.WAIT := SYS.DBMS_AQ.FOREVER;
  SYS.DBMS_AQ.dequeue (queue_name => qnm,
                       dequeue_options => deque_options,
                       message_properties => msg_prop,
                       payload => msg,
                       msgid => msg_hndl
                      );
  dbms_output.put_line('here');
  RETURN lcl_cnt;
  EXCEPTION
    WHEN OTHERS
      THEN raise_application_error (-20002, SQLERRM);
END get_messages;
/



Java code for dequeuing

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class RouterTest
{
  private static String DBConnString;
  private static String DBUser;
  private static String DBPassword;
  private static Connection conn;

  public static void main(String[] args) throws SQLException
  {
    CallableStatement cstmt = null;
    try
    {
      System.out.println( "Setting DB connection tokens from configuration file ");
      DBUser = "router";
      DBPassword = "abcde123";
      DBConnString = "jdbc:oracle:thin:@mhernand-us.us.oracle.com:1521:V10g";

      Class.forName("oracle.jdbc.OracleDriver");
      conn = DriverManager.getConnection( DBConnString, DBUser, DBPassword );
      System.out.println( "conn to RTTM " + conn);

      String sql = "{ ? = call router.get_messages()}";

      cstmt = conn.prepareCall(sql);
      cstmt.registerOutParameter(1, Types.INTEGER);

      cstmt.execute();
      Runtime.getRuntime().exit(0);

    }
    catch ( Exception e )
    {
      e.printStackTrace();
    }
    finally
    {
      if (cstmt != null)
        cstmt.close();
      conn.close();
    }
  }
}



Running the testcase using the 10g driver and pressing the CTRL + C the connection and the session is closed in the database, but when using the 11g driver the session in the database keeps open.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms