CODEWITHSUNDEEP
javadatabaseoracle-databasejdbcojdbc
Ueslei Lima
Ueslei Lima

Reputation: 331

Oracle 19c jdbc driver doesn't support schema names bigger than 30 bytes

According to Oracle's 19c documentation:

The schema name can be 128 bytes, the table name can be 128 bytes, and the column name can be 128 bytes.

However, I'm facing this issue whenever I try to use a schema name bigger than 30 bytes:

Caused by: java.sql.SQLException: Invalid argument(s) in call
at oracle.jdbc.driver.PhysicalConnection.setSchema(PhysicalConnection.java:9462)
at com.zaxxer.hikari.pool.ProxyConnection.setSchema(ProxyConnection.java:460)
at com.zaxxer.hikari.pool.HikariProxyConnection.setSchema(HikariProxyConnection.java)

The driver used is:

        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>19.7.0.0</version>
        </dependency>

It looks like the driver is not supporting longer object names introduced as per 12c version, any clues if this is somehow configurable? Could it also perhaps be some AWS RDS specific issue?

On SQL Developer using same jdbc url:

SELECT name, value FROM v$parameter WHERE name = 'compatible';

NAME       | VALUE
-------------------
compatible | 19.0.0

ALTER SESSION SET CURRENT_SCHEMA = VERY_VERY_VERY_LONG_SCHEMA_NAME;

Session altered.

UPDATE: After decompiling the driver code this is what I see:

public void setSchema(String schema) throws SQLException {
    try {
        String quoted = "\"[^\u0000\"]{0,28}\"";
        String unquoted = "(\\p{javaLowerCase}|\\p{javaUpperCase})(\\p{javaLowerCase}|\\p{javaUpperCase}|\\d|_|\\$|#){0,29}";
        String idPat = "(" + quoted + ")|(" + unquoted + ")";
        SQLException var10000;
        SQLException var9;
        if (schema == null) {
            var10000 = var9 = (SQLException)((SQLException)DatabaseError.createSqlException(this.getConnectionDuringExceptionHandling(), 68).fillInStackTrace());
            throw var10000;
        } else if (!schema.matches(idPat)) {
            var10000 = var9 = (SQLException)((SQLException)DatabaseError.createSqlException(this.getConnectionDuringExceptionHandling(), 68).fillInStackTrace());
            throw var10000;
        } else {
            String sql = "alter session set current_schema = " + schema;
            Statement stmt = null;
            try {
                stmt = this.createStatement();
                stmt.execute(sql);
    ...
}

Meaning that the driver is hardcoded to accept only 30 chars. So it seems to be a bug in the Oracle JDBC driver implementation. Any ideas for alternatives?

Upvotes: 2

Views: 1625

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21095

The setSchema seems to be forgotten in the long identifier change (and it seems you'll have to open SR with Oracle to get it work)

Contrary to that the basic usage of long identifiers (inclusive the binding by name) in JDBC seems to work fine.

Example

 def rs = stmt.executeQuery("select COL1, LAAAAAAAAAAAAAAAAAAAAAAAAAAAAARGE_NAME from LAAAAAAAAAAAAAAAAAAAAAAAAAAAAARGE_NAME.LAAAAAAAAAAAAAAAAAAAAAAAAAAAAARGE_NAME")
 
 while(rs.next())
 {
   println "col1= ${rs.getInt('COL1')} col2= ${rs.getInt('LAAAAAAAAAAAAAAAAAAAAAAAAAAAAARGE_NAME')}"
 }

Tested with DB Version 19.3.0.0.0

DriverVersion 19.3.0.0.0 and 21.1.0.0.0

Upvotes: 3

MT0
MT0

Reputation: 168351

From your documentation link:

The following list of rules applies to both quoted and nonquoted identifiers unless otherwise indicated:

  1. The maximum length of identifier names depends on the value of the COMPATIBLE initialization parameter.

    • If COMPATIBLE is set to a value of 12.2 or higher, then names must be from 1 to 128 bytes long with these exceptions:

      • Names of databases are limited to 8 bytes.
      • Names of disk groups, pluggable databases (PDBs), rollback segments, tablespaces, and tablespace sets are limited to 30 bytes.

    • If COMPATIBLE is set to a value lower than 12.2, then names must be from 1 to 30 bytes long with these exceptions:

      • Names of databases are limited to 8 bytes.
      • Names of database links can be as long as 128 bytes.

You need to check the COMPATIBLE initialisation parameter and if it is set below 12.2 then you are limited to 30 bytes.

Upvotes: 0

Related Questions