Using Comments at the Start of a JDBC INSERT Statement Fails With the Following Error "java.sql.SQLException: operation not allowed" (Doc ID 1076230.1)

Last updated on AUGUST 03, 2016

Applies to:

JDBC - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

When using a comment at the start of the an INSERT statement using Oracle JDBC the following error occurs.

Exception in thread "main" java.sql.SQLException: operation not allowed
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:197)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:269)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:490)
at oracle.jdbc.driver.OracleStatement.getGeneratedKeys(OracleStatement.java:4719)
at oracle.jdbc.driver.OracleStatementWrapper.getGeneratedKeys(OracleStatementWrapper.java:347)
at comment_test.main(comment_test.java:27)

Using comments for SELECT/UPDATE/DELETE all work fine. Just fails when using INSERT.

STEPS TO REPRODUCE

The issue can be reproduced at will with the following steps:
  1. Create the following database structure.
    CREATE TABLE TEST_COMMENTS
     (
     ID NUMBER NOT NULL,
     COL1 NUMBER NOT NULL
     );

  2. Insert using SQL PLUS:
    SQL> /* This is a Comment */ insert into test_comments (id, col1) values (1,0);

    1 row created.

    SQL> select * from test_comments;

    ID COL1
    ---------- ----------
    1 0

  3. Create a Java class named comment_test.java and add the following code:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;

    public class comment_test2 {

    private final static String INSERT_STATEMENT_WITH_BEGINNING_COMMENT =
    "/* This is a Comment */ insert into test_comments (id, col1) values (REFERENCE_SEQ.nextval,0)";


    public static void main(final String[] args) throws ClassNotFoundException, SQLException {
    final String connectionString = "jdbc:oracle:thin:@localhost:1521:orcl";
    final String userName = "scott";
    final String password = "tiger";

    Class.forName("oracle.jdbc.OracleDriver");

    final Connection connection = DriverManager.getConnection(connectionString, userName, password);

    try {
    final Statement statement = connection.createStatement();
    statement.execute(INSERT_STATEMENT_WITH_BEGINNING_COMMENT, new String[] { "id" });

    System.out.println("SQL STATEMENT >> " + INSERT_STATEMENT_WITH_BEGINNING_COMMENT);
    final ResultSet generatedKeys = statement.getGeneratedKeys();

    statement.close();
    } finally {
    try {
    connection.rollback();
    connection.close();
    } catch (final SQLException e) {
    throw new RuntimeException(e);
    }

    }
    }
    }

  4. Compile and run code.

  5. The following error will occur using for the 11.1.0.7 JDBC driver.
    Exception in thread "main" java.sql.SQLException: operation not allowed
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)
    at oracle.jdbc.driver.OracleStatement.getGeneratedKeys(OracleStatement.java:4761)
    at oracle.jdbc.driver.OracleStatementWrapper.getGeneratedKeys(OracleStatementWrapper.java:347)
    at project1.comment_test2.main(comment_test2.java:30)
    Process exited with exit code 1.
  6. The following error will occur using for the 11.2.0.1 JDBC driver.
    Exception in thread "main" java.sql.SQLException: operation not allowed
    at oracle.jdbc.driver.OracleStatement.getGeneratedKeys(OracleStatement.java:5156)
    at oracle.jdbc.driver.OracleStatementWrapper.getGeneratedKeys(OracleStatementWrapper.java:349)
    at project1.comment_test2.main(comment_test2.java:30)
    Process exited with exit code 1.


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