Unable to rename table with qualified target table name

[nielsmeijer]

When renaming a table using jOOQ on Oracle dialect using qualified table names, an oracle exception is raised: ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations.

Code sample:

DSL.using(SQLDialect.ORACLE12C)
.alterTable(name("schema_name", "old_table_name"))
renameTo(name("schema_name", "new_table_name"))
.execute();

jOOQ translates this code the following SQL query

alter table "MY_SCHEMA"."MY_TABLE"
  rename to "MY_SCHEMA"."NEW_NAME"

The query fails on the database with ORA error ORA-14047

When you omit the schema name, the query executes without any issue:

DSL.using(SQLDialect.ORACLE12C)
.alterTable(name("schema_name", "old_table_name"))
renameTo(name("new_table_name"))
.execute();

alter table "MY_SCHEMA"."MY_TABLE"
  rename to "NEW_NAME"

Versions:

• jOOQ: tested on 3.13.2 (pro)
• Database: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
• JDBC Driver: oracle ojdbc7

[lukaseder]

Thank you very much for your report.

I think there had been similar cases in the past where qualified renames didn't work for other object types. We should add integration tests for all of these rename operations to make sure qualification works if the source and target schema are the same, which should always work irrespective of the dialect.

[lukaseder]

Looking into this now. This isn't Oracle specific. Numerous dialects are affected, among others:

• Db2
• Derby
• Informix
• Oracle
• SQL Server

We're already unqualifying the target table name in:

• BigQuery
• CockroachDB
• PostgreSQL
• Redshift
• YugabyteDB

I'm not sure why this hasn't been done yet for all affected dialects.

[lukaseder]

Fixed in jOOQ 3.19.0, 3.18.5 (#15066), 3.17.14 (#15067), and 3.16.20 (#15068)