ORA-00972: identifier is too long. Oracle 12.1.0.2.0

[davidbasilashvili]

Hi everyone,

I'm trying to create reference and facing to identifier limitation in Oracle 12.1.0.2.0.

Seems like expected behaviour: Exposed trim long id to supported length in current database version.
Actual behaviour: on Oracle 12.1.0.2.0 Exposed doesn't trim long ids to 30 digit ids

Regarding documentation long ids introduced in 12.2 Oracle release: Long Identifiers in Oracle Database 12c Release 2 (12.2)

The same issue was fixed in #654, but for Oracle 11.

Regarding actual code we can go from reference creation:
val refColumn: Column<Long> = reference(name = "reference_name", refColumn = referenceColumn) regarding actual code we have:

Exposed/exposed-core/src/main/kotlin/org/jetbrains/exposed/sql/Table.kt

Lines 758 to 768 in 2092d3b

	fun <T : Comparable<T>> reference(
	name: String,
	refColumn: Column<T>,
	onDelete: ReferenceOption? = null,
	onUpdate: ReferenceOption? = null,
	fkName: String? = null
	): Column<T> {
	val column = Column<T>(this, name, refColumn.columnType.cloneAsBaseType()).references(refColumn, onDelete, onUpdate, fkName)
	_columns.addColumn(column)
	return column
	}

And next fkName go to ForeignKeyConstraint as name:

Exposed/exposed-core/src/main/kotlin/org/jetbrains/exposed/sql/Table.kt

Lines 703 to 716 in 2092d3b

	fun <T : Comparable<T>, S : T, C : Column<S>> C.references(
	ref: Column<T>,
	onDelete: ReferenceOption? = null,
	onUpdate: ReferenceOption? = null,
	fkName: String? = null
	): C = apply {
	this.foreignKey = ForeignKeyConstraint(
	target = ref,
	from = this,
	onUpdate = onUpdate,
	onDelete = onDelete,
	name = fkName
	)
	}

And next we can see how fkName should created:

Exposed/exposed-core/src/main/kotlin/org/jetbrains/exposed/sql/Constraints.kt

Lines 107 to 110 in 2092d3b

	val fkName: String
	get() = tx.db.identifierManager.cutIfNecessaryAndQuote(
	name ?: "fk_${fromTable.tableNameWithoutScheme}_${from.joinToString("_") { it.name }}__${target.joinToString("_") { it.name }}"
	).inProperCase()

And cutting code here, you can see that it depends on identifierLengthLimit:

Exposed/exposed-core/src/main/kotlin/org/jetbrains/exposed/sql/statements/api/IdentifierManagerApi.kt

Line 93 in 2092d3b

fun cutIfNecessaryAndQuote(identity: String) = quoteIfNecessary(identity.take(identifierLengthLimit))

And limit init found here:

Exposed/exposed-core/src/main/kotlin/org/jetbrains/exposed/sql/statements/api/IdentifierManagerApi.kt

Lines 21 to 29 in 2092d3b

	protected enum class OracleVersion { Oracle11g, Oracle12plus, NonOracle }
	protected val identifierLengthLimit by lazy {
	when (oracleVersion) {
	OracleVersion.Oracle11g -> 30
	OracleVersion.Oracle12plus -> 128
	else -> maxColumnNameLength.takeIf { it > 0 } ?: Int.MAX_VALUE
	}
	}

Seems like there is no case for oracle version later 11g but before 12.2.

Workaround: put fk name explicitly in reference method like val refColumn: Column<Long> = reference(name = "reference_name", refColumn = referenceColumn, fkName = "fk_constraint_name")
but for big codebase it can be overkill.

Could you please advise another workaround if possible or fix it somehow?

Many thanks!

[Tapac]

Will be fixed in 0.38.2