My Oracle Support Banner

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

Last updated on MARCH 15, 2019

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 <USER> cascade;

create user <USER> identified by <PASSWORD> default tablespace users temporary tablespace temp;
grant resource, connect, dba to <USER>;

GRANT EXECUTE ON DBMS_AQADM to <USER>;
GRANT EXECUTE ON DBMS_AQ TO <USER>;

 

conn <USER>/<PASSWORD>

-- create queue and de-qeueue function


CREATE OR REPLACE TYPE <USER>.MSG_TYP AS OBJECT (MSG_ID NUMBER)
/


BEGIN
  SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
  (  QUEUE_TABLE => '<USER>.POOLENGINE0_Q_TBL'
    ,QUEUE_PAYLOAD_TYPE => '<USER>.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 => '<USER>.POOLENGINE0_Q'
    ,QUEUE_TABLE => '<USER>.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 => '<USER>.POOLENGINE0_Q'
    ,ENQUEUE => TRUE
    ,DEQUEUE => TRUE
  );
END;
/


CREATE OR REPLACE FUNCTION <USER>.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) := '<USER>.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 = "<USER>";
      DBPassword = "<PASSWORD>";
      DBConnString = "jdbc:oracle:thin:@<HOST>:<PORT>:<SID>";

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

      String sql = "{ ? = call <USER>.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.

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.