Proposal for fixing ORA-00972

[f-wrobel]

Feature Description

The error ORA-00972 is caused by Oracle database which has an identifier length limit of 30 bytes.
The problem may occur during TypeORM's tests because of the Oracle docker configuration.
Also, the problem may occur if a user uses an old Oracle db.

Oracle changed the limit from 30 to 128 bytes in 12.2.
https://stackoverflow.com/a/3085571
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/newft/new-features.html#GUID-64283AD6-0939-47B0-856E-5E9255D7246B

TypeORM is using a docker image which probably is Oracle XE 18. If I understand correctly, there should be a limit of 128 instead of 30 bytes in this version.
So, why is this happening? Is something wrong with the image, configuration? Moreover, contributors of TypeORM are already aware of this. In alias implementation, there’s comment describing this issue. https://github.com/typeorm/typeorm/blob/defb409f5650fed0b7a4ff2933208282a45572fb/src/driver/oracle/OracleDriver.ts#L216-#L227
Furthermore, I've checked it on a fresh Oracle XE 18 and it indeed hasn't the limit of 30.

If I checked correctly, currently ORA-00972 will come up only if the column name is too long, because other causes are already handled in OracleDriver code (alias) or in naming strategy (other things).

Edit: I've checked compatibility level on TypeORM's test Oracle docker:

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

and it's 12.0.0.0.0. It should be at least 12.2.

The Solution

I would like to propose two things:

1. Make a life easier for people who really need to use Oracle with 30 bytes limit. For them I've created a PR with a new naming strategy.
   PR: feat: naming strategy for legacy Oracle #9703
   This kind of solution was proposed in the past:
   Complete Oracle tests #452 (comment)
   Users of other ORMs like Hibernate also tend to use a different naming strategy for Oracle.
   https://stackoverflow.com/questions/38565495/how-to-handle-the-ora-00972-identifier-is-too-long-exception-with-hibernate-5-a
2. Investigate why this limit in TypeORM's test docker is 30 bytes and manage to change it to 128 bytes. This should almost eradicate the problem. -> There is a need to change compatible on TypeORM's test Oracle docker to at lest 12.2.

Considered Alternatives

A user could use their own CustomNamingStrategy to acheive the same effect.
A developer who writes tests for TypeORM must change the lengths of identifiers to meet the 30 bytes requirement.

Additional Context

There are numerous issues with Oracle DB and the infamous ORA-00972:

#454
#8959
#693
#5067
#6591
#6624
#6751
#9696

and more.

Refactoring of naming strategies is worth considering in the future.

Relevant Database Driver(s)

• aurora-mysql
• aurora-postgres
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• spanner
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

Yes, I have the time, and I know how to start.

[f-wrobel]

If I understand correctly, the author of the Oracle docker is @imnotjames (docker-compose.yml). Could you answer why COMPATIBLE is 12.0.0.0.0?