Repository: knex/knex (js)

Issue:
Limit is hard coded when it has changed since oracle 12.2
https://github.com/knex/knex/blob/e37aeaa31c8ef9c1b07d2e4d3ec6607e557d800d/lib/dialects/oracle/utils.js#L3

As of their 12.2 release they now support long identifiers up to 128 bytes.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/newft/new-features.html#GUID-ACC0E96C-8D2D-468F-92E0-2013C7F13455

The tricky part could be that depending on the language a character may be multiple bytes.

https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch2charset.htm#NLSPG171

Thanks!

Pull request description:
Support Object Names Greater than 30 Characters for Oracle DB Versions 12.2 and Greater
Since oracle database 12.2, database object names longer than 30 characters has been supported. This PR dynamically sets the maximum character limit for names generated in oracledb based on either the version specified using the `version` attribute in the knex configuration, or it will auto-detect the version number if it is not specified (using a similar technique for auto-detection as is being done in postgres). I added a bit of extra version format processing since oracle version numbers come in both the major.minor format as well as an edition format (such as 9i).

Additional unit and integration tests have been added for the new auto-version-detection features as well as the name max length resolution. I have also updated existing integration and unit tests that relied on the hashed names to expect the standard format since the test oracle database is using version 18.

For older oracle versions, the old hashed name method is still used.

One aspect of this change that may or may not need additional consideration is in the scenario that someone using an older knex version, prior to this change, on oracle 12.2 or greater upgrades to the newest version of knex after this feature is released. Knex will no longer try to convert to the hashed name format when performing schema operations. This would be problematic when a "legacy-named" object is altered or deleted in a migration after upgrading. One potential work-around is to set the version manually to `12.1` in the knex configuration. The other option is providing a means for renaming the old format to the new format. This may make this a breaking change as it may require configuration changes in some use cases.

When running the oracle integration tests, I had several failing, even on master before any of these changes. I have gone through to make sure that all tests that were failing after these changes were the same that were failing before. Unit test results appear to be completing without issue before and after changes.

Closes #3914 

Patch:
diff --git a/lib/dialects/oracle/schema/internal/incrementUtils.js b/lib/dialects/oracle/schema/internal/incrementUtils.js
index cfd8c4d3..5f583697 100644
--- a/lib/dialects/oracle/schema/internal/incrementUtils.js
+++ b/lib/dialects/oracle/schema/internal/incrementUtils.js
@@ -2,11 +2,13 @@ const Trigger = require('./trigger');
 
 // helper function for pushAdditional in increments() and bigincrements()
 function createAutoIncrementTriggerAndSequence(columnCompiler) {
+  const trigger = new Trigger(columnCompiler.client.version);
+
   // TODO Add warning that sequence etc is created
   columnCompiler.pushAdditional(function () {
     const tableName = this.tableCompiler.tableNameRaw;
     const schemaName = this.tableCompiler.schemaNameRaw;
-    const createTriggerSQL = Trigger.createAutoIncrementTrigger(
+    const createTriggerSQL = trigger.createAutoIncrementTrigger(
       this.client.logger,
       tableName,
       schemaName
diff --git a/lib/dialects/oracle/schema/internal/trigger.js b/lib/dialects/oracle/schema/internal/trigger.js
index d84ff933..e906df13 100644
--- a/lib/dialects/oracle/schema/internal/trigger.js
+++ b/lib/dialects/oracle/schema/internal/trigger.js
@@ -1,13 +1,21 @@
-const utils = require('../../utils');
+const { NameHelper } = require('../../utils');
 
-const trigger = {
-  renameColumnTrigger: function (logger, tableName, columnName, to) {
-    const triggerName = utils.generateCombinedName(
+class Trigger {
+  constructor(oracleVersion) {
+    this.nameHelper = new NameHelper(oracleVersion);
+  }
+
+  renameColumnTrigger(logger, tableName, columnName, to) {
+    const triggerName = this.nameHelper.generateCombinedName(
       logger,
       'autoinc_trg',
       tableName
     );
-    const sequenceName = utils.generateCombinedName(logger, 'seq', tableName);
+    const sequenceName = this.nameHelper.generateCombinedName(
+      logger,
+      'seq',
+      tableName
+    );
     return (
       `DECLARE ` +
       `PK_NAME VARCHAR(200); ` +
@@ -41,19 +49,19 @@ const trigger = {
       `  end if;` +
       `END;`
     );
-  },
+  }
 
-  createAutoIncrementTrigger: function (logger, tableName, schemaName) {
+  createAutoIncrementTrigger(logger, tableName, schemaName) {
     const tableQuoted = `"${tableName}"`;
     const tableUnquoted = tableName;
     const schemaQuoted = schemaName ? `"${schemaName}".` : '';
     const constraintOwner = schemaName ? `'${schemaName}'` : 'cols.owner';
-    const triggerName = utils.generateCombinedName(
+    const triggerName = this.nameHelper.generateCombinedName(
       logger,
       'autoinc_trg',
       tableName
     );
-    const sequenceNameUnquoted = utils.generateCombinedName(
+    const sequenceNameUnquoted = this.nameHelper.generateCombinedName(
       logger,
       'seq',
       tableName
@@ -86,17 +94,29 @@ const trigger = {
       `  end;'); ` +
       `END;`
     );
-  },
+  }
 
-  renameTableAndAutoIncrementTrigger: function (logger, tableName, to) {
-    const triggerName = utils.generateCombinedName(
+  renameTableAndAutoIncrementTrigger(logger, tableName, to) {
+    const triggerName = this.nameHelper.generateCombinedName(
       logger,
       'autoinc_trg',
       tableName
     );
-    const sequenceName = utils.generateCombinedName(logger, 'seq', tableName);
-    const toTriggerName = utils.generateCombinedName(logger, 'autoinc_trg', to);
-    const toSequenceName = utils.generateCombinedName(logger, 'seq', to);
+    const sequenceName = this.nameHelper.generateCombinedName(
+      logger,
+      'seq',
+      tableName
+    );
+    const toTriggerName = this.nameHelper.generateCombinedName(
+      logger,
+      'autoinc_trg',
+      to
+    );
+    const toSequenceName = this.nameHelper.generateCombinedName(
+      logger,
+      'seq',
+      to
+    );
     return (
       `DECLARE ` +
       `PK_NAME VARCHAR(200); ` +
@@ -129,7 +149,7 @@ const trigger = {
       `  end if;` +
       `END;`
     );
-  },
-};
+  }
+}
 
-module.exports = trigger;
+module.exports = Trigger;
diff --git a/lib/dialects/oracle/schema/oracle-compiler.js b/lib/dialects/oracle/schema/oracle-compiler.js
index 2307911a..4509c31a 100644
--- a/lib/dialects/oracle/schema/oracle-compiler.js
+++ b/lib/dialects/oracle/schema/oracle-compiler.js
@@ -11,7 +11,8 @@ class SchemaCompiler_Oracle extends SchemaCompiler {
 
   // Rename a table on the schema.
   renameTable(tableName, to) {
-    const renameTable = Trigger.renameTableAndAutoIncrementTrigger(
+    const trigger = new Trigger(this.client.version);
+    const renameTable = trigger.renameTableAndAutoIncrementTrigger(
       this.client.logger,
       tableName,
       to
@@ -60,7 +61,8 @@ class SchemaCompiler_Oracle extends SchemaCompiler {
 
   _dropRelatedSequenceIfExists(tableName) {
     // removing the sequence that was possibly generated by increments() column
-    const sequenceName = utils.generateCombinedName(
+    const nameHelper = new utils.NameHelper(this.client.version);
+    const sequenceName = nameHelper.generateCombinedName(
       this.client.logger,
       'seq',
       tableName
diff --git a/lib/dialects/oracle/schema/oracle-tablecompiler.js b/lib/dialects/oracle/schema/oracle-tablecompiler.js
index fb704a9d..b05a3c84 100644
--- a/lib/dialects/oracle/schema/oracle-tablecompiler.js
+++ b/lib/dialects/oracle/schema/oracle-tablecompiler.js
@@ -39,8 +39,9 @@ class TableCompiler_Oracle extends TableCompiler {
   renameColumn(from, to) {
     // Remove quotes around tableName
     const tableName = this.tableName().slice(1, -1);
+    const trigger = new Trigger(this.client.version);
     return this.pushQuery(
-      Trigger.renameColumnTrigger(this.client.logger, tableName, from, to)
+      trigger.renameColumnTrigger(this.client.logger, tableName, from, to)
     );
   }
 
@@ -86,8 +87,14 @@ class TableCompiler_Oracle extends TableCompiler {
   }
 
   _indexCommand(type, tableName, columns) {
+    const nameHelper = new utils.NameHelper(this.client.version);
     return this.formatter.wrap(
-      utils.generateCombinedName(this.client.logger, type, tableName, columns)
+      nameHelper.generateCombinedName(
+        this.client.logger,
+        type,
+        tableName,
+        columns
+      )
     );
   }
 
diff --git a/lib/dialects/oracle/utils.js b/lib/dialects/oracle/utils.js
index 48504527..dee38eb1 100644
--- a/lib/dialects/oracle/utils.js
+++ b/lib/dialects/oracle/utils.js
@@ -1,25 +1,44 @@
-function generateCombinedName(logger, postfix, name, subNames) {
-  const crypto = require('crypto');
-  const limit = 30;
-  if (!Array.isArray(subNames)) subNames = subNames ? [subNames] : [];
-  const table = name.replace(/\.|-/g, '_');
-  const subNamesPart = subNames.join('_');
-  let result = `${table}_${
-    subNamesPart.length ? subNamesPart + '_' : ''
-  }${postfix}`.toLowerCase();
-  if (result.length > limit) {
-    logger.warn(
-      `Automatically generated name "${result}" exceeds ${limit} character ` +
-        `limit for Oracle. Using base64 encoded sha1 of that name instead.`
-    );
-    // generates the sha1 of the name and encode it with base64
-    result = crypto
-      .createHash('sha1')
-      .update(result)
-      .digest('base64')
-      .replace('=', '');
+class NameHelper {
+  constructor(oracleVersion) {
+    this.oracleVersion = oracleVersion;
+
+    // In oracle versions prior to 12.2, the maximum length for a database
+    // object name was 30 characters. 12.2 extended this to 128.
+    const versionParts = oracleVersion
+      .split('.')
+      .map((versionPart) => parseInt(versionPart));
+    if (
+      versionParts[0] > 12 ||
+      (versionParts[0] === 12 && versionParts[1] >= 2)
+    ) {
+      this.limit = 128;
+    } else {
+      this.limit = 30;
+    }
+  }
+
+  generateCombinedName(logger, postfix, name, subNames) {
+    const crypto = require('crypto');
+    if (!Array.isArray(subNames)) subNames = subNames ? [subNames] : [];
+    const table = name.replace(/\.|-/g, '_');
+    const subNamesPart = subNames.join('_');
+    let result = `${table}_${
+      subNamesPart.length ? subNamesPart + '_' : ''
+    }${postfix}`.toLowerCase();
+    if (result.length > this.limit) {
+      logger.warn(
+        `Automatically generated name "${result}" exceeds ${this.limit} character ` +
+          `limit for Oracle Database ${this.oracleVersion}. Using base64 encoded sha1 of that name instead.`
+      );
+      // generates the sha1 of the name and encode it with base64
+      result = crypto
+        .createHash('sha1')
+        .update(result)
+        .digest('base64')
+        .replace('=', '');
+    }
+    return result;
   }
-  return result;
 }
 
 function wrapSqlWithCatch(sql, errorNumberToCatch) {
@@ -80,7 +99,7 @@ function isConnectionError(err) {
 }
 
 module.exports = {
-  generateCombinedName,
+  NameHelper,
   isConnectionError,
   wrapSqlWithCatch,
   ReturningHelper,
diff --git a/lib/dialects/oracledb/index.js b/lib/dialects/oracledb/index.js
index 06f0c0db..dc9af90c 100644
--- a/lib/dialects/oracledb/index.js
+++ b/lib/dialects/oracledb/index.js
@@ -25,6 +25,13 @@ const { compileCallback } = require('../../formatter/formatterUtils');
 class Client_Oracledb extends Client_Oracle {
   constructor(config) {
     super(config);
+
+    if (this.version) {
+      // Normalize version format; null bad format
+      // to trigger fallback to auto-detect.
+      this.version = parseVersion(this.version);
+    }
+
     if (this.driver) {
       process.env.UV_THREADPOOL_SIZE = process.env.UV_THREADPOOL_SIZE || 1;
       process.env.UV_THREADPOOL_SIZE =
@@ -120,43 +127,40 @@ class Client_Oracledb extends Client_Oracle {
   // Get a raw connection, called by the `pool` whenever a new
   // connection needs to be added to the pool.
   acquireRawConnection() {
-    const client = this;
-    const asyncConnection = new Promise(function (resolver, rejecter) {
+    return new Promise((resolver, rejecter) => {
       // If external authentication don't have to worry about username/password and
       // if not need to set the username and password
-      const oracleDbConfig = client.connectionSettings.externalAuth
-        ? { externalAuth: client.connectionSettings.externalAuth }
+      const oracleDbConfig = this.connectionSettings.externalAuth
+        ? { externalAuth: this.connectionSettings.externalAuth }
         : {
-            user: client.connectionSettings.user,
-            password: client.connectionSettings.password,
+            user: this.connectionSettings.user,
+            password: this.connectionSettings.password,
           };
 
       // In the case of external authentication connection string will be given
       oracleDbConfig.connectString = resolveConnectString(
-        client.connectionSettings
+        this.connectionSettings
       );
 
-      if (client.connectionSettings.prefetchRowCount) {
-        oracleDbConfig.prefetchRows =
-          client.connectionSettings.prefetchRowCount;
+      if (this.connectionSettings.prefetchRowCount) {
+        oracleDbConfig.prefetchRows = this.connectionSettings.prefetchRowCount;
       }
 
-      if (client.connectionSettings.stmtCacheSize !== undefined) {
-        oracleDbConfig.stmtCacheSize = client.connectionSettings.stmtCacheSize;
+      if (this.connectionSettings.stmtCacheSize !== undefined) {
+        oracleDbConfig.stmtCacheSize = this.connectionSettings.stmtCacheSize;
       }
 
-      client.driver.fetchAsString = client.fetchAsString;
+      this.driver.fetchAsString = this.fetchAsString;
 
-      client.driver.getConnection(oracleDbConfig, function (err, connection) {
+      this.driver.getConnection(oracleDbConfig, (err, connection) => {
         if (err) {
           return rejecter(err);
         }
-        monkeyPatchConnection(connection, client);
+        monkeyPatchConnection(connection, this);
 
         resolver(connection);
       });
     });
-    return asyncConnection;
   }
 
   // Used to explicitly close a connection, called internally by the pool
@@ -165,6 +169,39 @@ class Client_Oracledb extends Client_Oracle {
     return connection.release();
   }
 
+  // Handle oracle version resolution on acquiring connection from pool instead of connection creation.
+  // Must do this here since only the client used to create a connection would be updated with version
+  // information on creation. Poses a problem when knex instance is cloned since instances share the
+  // connection pool while having their own client instances.
+  async acquireConnection() {
+    const connection = await super.acquireConnection();
+    this.checkVersion(connection);
+    return connection;
+  }
+
+  // In Oracle, we need to check the version to dynamically determine
+  // certain limits. If user did not specify a version, get it from the connection.
+  checkVersion(connection) {
+    // Already determined version before?
+    if (this.version) {
+      return this.version;
+    }
+
+    const detectedVersion = parseVersion(connection.oracleServerVersionString);
+    if (!detectedVersion) {
+      // When original version is set to null, user-provided version was invalid and we fell-back to auto-detect.
+      // Otherwise, we couldn't auto-detect at all. Set error message accordingly.
+      throw new Error(
+        this.version === null
+          ? 'Invalid Oracledb version number format passed to knex. Unable to successfully auto-detect as fallback. Please specify a valid oracledb version.'
+          : 'Unable to detect Oracledb version number automatically. Please specify the version in knex configuration.'
+      );
+    }
+
+    this.version = detectedVersion;
+    return detectedVersion;
+  }
+
   // Runs the query on the specified connection, providing the bindings
   // and any other necessary prep work.
   _query(connection, obj) {
@@ -300,12 +337,29 @@ class Client_Oracledb extends Client_Oracle {
   }
 
   processPassedConnection(connection) {
+    this.checkVersion(connection);
     monkeyPatchConnection(connection, this);
   }
 }
 
 Client_Oracledb.prototype.driverName = 'oracledb';
 
+function parseVersion(versionString) {
+  try {
+    // We only care about first two version components at most
+    const versionParts = versionString.split('.').slice(0, 2);
+    // Strip off any character suffixes in version number (ex. 12c => 12, 12.2c => 12.2)
+    versionParts.forEach((versionPart, idx) => {
+      versionParts[idx] = versionPart.replace(/\D$/, '');
+    });
+    const version = versionParts.join('.');
+    return version.match(/^\d+\.?\d*$/) ? version : null;
+  } catch (err) {
+    // Non-string versionString passed in.
+    return null;
+  }
+}
+
 function resolveConnectString(connectionSettings) {
   if (connectionSettings.connectString) {
     return connectionSettings.connectString;


Test patch:
diff --git a/test/db-less-test-suite.js b/test/db-less-test-suite.js
index 647d83af..8f2dc935 100644
--- a/test/db-less-test-suite.js
+++ b/test/db-less-test-suite.js
@@ -47,6 +47,10 @@ if (config.postgres) {
   require('./unit/dialects/postgres');
 }
 
+if (config.oracledb) {
+  require('./unit/dialects/oracledb');
+}
+
 if (config.mysql) {
   require('./unit/dialects/mysql');
 }
diff --git a/test/integration2/dialects/oracledb.spec.js b/test/integration2/dialects/oracledb.spec.js
index 71fe3ca3..11df200c 100644
--- a/test/integration2/dialects/oracledb.spec.js
+++ b/test/integration2/dialects/oracledb.spec.js
@@ -31,6 +31,35 @@ describe('Oracledb dialect', () => {
             });
           });
         });
+
+        describe('Version Detection', () => {
+          it('should correctly resolve the oracle database version on connection if not specified', async () => {
+            const client = knex.client;
+            const connection = await client.acquireConnection();
+            await client.releaseConnection(connection);
+
+            expect(client.version).to.match(/^\d+\.\d+$/);
+          });
+
+          it('should not attempt to dynamically resolve database version on connection if specified', async () => {
+            // Manually specifying a version number other than the one we use for testing.
+            const client = getKnexForDb('oracledb', { version: '12.2' }).client;
+            const connection = await client.acquireConnection();
+            await client.releaseConnection(connection);
+
+            expect(client.version).to.equal('12.2');
+          });
+
+          it('should dynamically resolve database version on connection if specified version is invalid', async () => {
+            const client = getKnexForDb('oracledb', {
+              version: 'not a good version number',
+            }).client;
+            const connection = await client.acquireConnection();
+            await client.releaseConnection(connection);
+
+            expect(client.version).to.match(/^\d+\.\d+$/);
+          });
+        });
       });
 
       describe('Client', () => {
diff --git a/test/integration2/schema/misc.spec.js b/test/integration2/schema/misc.spec.js
index befed615..0365988f 100644
--- a/test/integration2/schema/misc.spec.js
+++ b/test/integration2/schema/misc.spec.js
@@ -789,7 +789,7 @@ describe('Schema (misc)', () => {
                 `DECLARE PK_NAME VARCHAR(200); BEGIN  EXECUTE IMMEDIATE ('CREATE SEQUENCE "test_table_one_seq"');  SELECT cols.column_name INTO PK_NAME  FROM all_constraints cons, all_cons_columns cols  WHERE cons.constraint_type = 'P'  AND cons.constraint_name = cols.constraint_name  AND cons.owner = cols.owner  AND cols.table_name = 'test_table_one';  execute immediate ('create or replace trigger "test_table_one_autoinc_trg"  BEFORE INSERT on "test_table_one"  for each row  declare  checking number := 1;  begin    if (:new."' || PK_NAME || '" is null) then      while checking >= 1 loop        select "test_table_one_seq".nextval into :new."' || PK_NAME || '" from dual;        select count("' || PK_NAME || '") into checking from "test_table_one"        where "' || PK_NAME || '" = :new."' || PK_NAME || '";      end loop;    end if;  end;'); END;`,
                 'comment on column "test_table_one"."logins" is \'\'',
                 'comment on column "test_table_one"."about" is \'A comment.\'',
-                'create index "NkZo/dGRI9O73/NE2fHo+35d4jk" on "test_table_one" ("first_name")',
+                'create index "test_table_one_first_name_index" on "test_table_one" ("first_name")',
                 'alter table "test_table_one" add constraint "test_table_one_email_unique" unique ("email")',
                 'create index "test_table_one_logins_index" on "test_table_one" ("logins")',
               ]);
@@ -833,7 +833,7 @@ describe('Schema (misc)', () => {
               tester('oracledb', [
                 `create table "test_table_timestamp" ("id" number(20, 0) not null primary key, "createdAt" timestamp with local time zone default CURRENT_TIMESTAMP not null, "updatedAt" timestamp with local time zone default CURRENT_TIMESTAMP not null)`,
                 `DECLARE PK_NAME VARCHAR(200); BEGIN  EXECUTE IMMEDIATE ('CREATE SEQUENCE "test_table_timestamp_seq"');  SELECT cols.column_name INTO PK_NAME  FROM all_constraints cons, all_cons_columns cols  WHERE cons.constraint_typ` +
-                  `e = 'P'  AND cons.constraint_name = cols.constraint_name  AND cons.owner = cols.owner  AND cols.table_name = 'test_table_timestamp';  execute immediate ('create or replace trigger "gT8ntVvbOANQHra05aYo1kc6cCI"  BEFORE INSERT on` +
+                  `e = 'P'  AND cons.constraint_name = cols.constraint_name  AND cons.owner = cols.owner  AND cols.table_name = 'test_table_timestamp';  execute immediate ('create or replace trigger "test_table_timestamp_autoinc_trg"  BEFORE INSERT on` +
                   ` "test_table_timestamp"  for each row  declare  checking number := 1;  begin    if (:new."' || PK_NAME || '" is null) then      while checking >= 1 loop        select "test_table_timestamp_seq".nextval into :new."' || PK_N` +
                   `AME || '" from dual;        select count("' || PK_NAME || '") into checking from "test_table_timestamp"        where "' || PK_NAME || '" = :new."' || PK_NAME || '";      end loop;    end if;  end;'); END;`,
               ]);
@@ -1260,8 +1260,8 @@ describe('Schema (misc)', () => {
               ]);
               tester('oracledb', [
                 'create table "test_foreign_table_two" ("id" integer not null primary key, "fkey_two" integer, "fkey_three" integer, "fkey_four" integer)',
-                'DECLARE PK_NAME VARCHAR(200); BEGIN  EXECUTE IMMEDIATE (\'CREATE SEQUENCE "test_foreign_table_two_seq"\');  SELECT cols.column_name INTO PK_NAME  FROM all_constraints cons, all_cons_columns cols  WHERE cons.constraint_type = \'P\'  AND cons.constraint_name = cols.constraint_name  AND cons.owner = cols.owner  AND cols.table_name = \'test_foreign_table_two\';  execute immediate (\'create or replace trigger "m6uvAnbUQqcHvfWTN5IAjip1/vk"  BEFORE INSERT on "test_foreign_table_two"  for each row  declare  checking number := 1;  begin    if (:new."\' || PK_NAME || \'" is null) then      while checking >= 1 loop        select "test_foreign_table_two_seq".nextval into :new."\' || PK_NAME || \'" from dual;        select count("\' || PK_NAME || \'") into checking from "test_foreign_table_two"        where "\' || PK_NAME || \'" = :new."\' || PK_NAME || \'";      end loop;    end if;  end;\'); END;',
-                'alter table "test_foreign_table_two" add constraint "q7TfvbIx3HUQbh+l+e5N+J+Guag" foreign key ("fkey_two") references "test_table_two" ("id")',
+                'DECLARE PK_NAME VARCHAR(200); BEGIN  EXECUTE IMMEDIATE (\'CREATE SEQUENCE "test_foreign_table_two_seq"\');  SELECT cols.column_name INTO PK_NAME  FROM all_constraints cons, all_cons_columns cols  WHERE cons.constraint_type = \'P\'  AND cons.constraint_name = cols.constraint_name  AND cons.owner = cols.owner  AND cols.table_name = \'test_foreign_table_two\';  execute immediate (\'create or replace trigger "test_foreign_table_two_autoinc_trg"  BEFORE INSERT on "test_foreign_table_two"  for each row  declare  checking number := 1;  begin    if (:new."\' || PK_NAME || \'" is null) then      while checking >= 1 loop        select "test_foreign_table_two_seq".nextval into :new."\' || PK_NAME || \'" from dual;        select count("\' || PK_NAME || \'") into checking from "test_foreign_table_two"        where "\' || PK_NAME || \'" = :new."\' || PK_NAME || \'";      end loop;    end if;  end;\'); END;',
+                'alter table "test_foreign_table_two" add constraint "test_foreign_table_two_fkey_two_foreign" foreign key ("fkey_two") references "test_table_two" ("id")',
                 'alter table "test_foreign_table_two" add constraint "fk_fkey_three" foreign key ("fkey_three") references "test_table_two" ("id")',
                 'alter table "test_foreign_table_two" add constraint "fk_fkey_four" foreign key ("fkey_four") references "test_table_two" ("id")',
               ]);
@@ -1330,7 +1330,7 @@ describe('Schema (misc)', () => {
               ]);
               tester('oracledb', [
                 'create table "composite_key_test" ("column_a" integer, "column_b" integer, "details" clob, "status" smallint)',
-                'alter table "composite_key_test" add constraint "zYmMt0VQwlLZ20XnrMicXZ0ufZk" unique ("column_a", "column_b")',
+                'alter table "composite_key_test" add constraint "composite_key_test_column_a_column_b_unique" unique ("column_a", "column_b")',
               ]);
               tester('mssql', [
                 'CREATE TABLE [composite_key_test] ([column_a] int, [column_b] int, [details] nvarchar(max), [status] tinyint)',
@@ -1391,7 +1391,7 @@ describe('Schema (misc)', () => {
               ]);
               tester('oracledb', [
                 'create table "charset_collate_test" ("id" integer not null primary key, "account_id" integer, "details" clob, "status" smallint)',
-                'DECLARE PK_NAME VARCHAR(200); BEGIN  EXECUTE IMMEDIATE (\'CREATE SEQUENCE "charset_collate_test_seq"\');  SELECT cols.column_name INTO PK_NAME  FROM all_constraints cons, all_cons_columns cols  WHERE cons.constraint_type = \'P\'  AND cons.constraint_name = cols.constraint_name  AND cons.owner = cols.owner  AND cols.table_name = \'charset_collate_test\';  execute immediate (\'create or replace trigger "x9C3VzXH9urIKnTjm32JM7OvYYQ"  BEFORE INSERT on "charset_collate_test"  for each row  declare  checking number := 1;  begin    if (:new."\' || PK_NAME || \'" is null) then      while checking >= 1 loop        select "charset_collate_test_seq".nextval into :new."\' || PK_NAME || \'" from dual;        select count("\' || PK_NAME || \'") into checking from "charset_collate_test"        where "\' || PK_NAME || \'" = :new."\' || PK_NAME || \'";      end loop;    end if;  end;\'); END;',
+                'DECLARE PK_NAME VARCHAR(200); BEGIN  EXECUTE IMMEDIATE (\'CREATE SEQUENCE "charset_collate_test_seq"\');  SELECT cols.column_name INTO PK_NAME  FROM all_constraints cons, all_cons_columns cols  WHERE cons.constraint_type = \'P\'  AND cons.constraint_name = cols.constraint_name  AND cons.owner = cols.owner  AND cols.table_name = \'charset_collate_test\';  execute immediate (\'create or replace trigger "charset_collate_test_autoinc_trg"  BEFORE INSERT on "charset_collate_test"  for each row  declare  checking number := 1;  begin    if (:new."\' || PK_NAME || \'" is null) then      while checking >= 1 loop        select "charset_collate_test_seq".nextval into :new."\' || PK_NAME || \'" from dual;        select count("\' || PK_NAME || \'") into checking from "charset_collate_test"        where "\' || PK_NAME || \'" = :new."\' || PK_NAME || \'";      end loop;    end if;  end;\'); END;',
               ]);
               tester('mssql', [
                 'CREATE TABLE [charset_collate_test] ([id] int identity(1,1) not null primary key, [account_id] int, [details] nvarchar(max), [status] tinyint)',
diff --git a/test/unit/dialects/oracledb.js b/test/unit/dialects/oracledb.js
new file mode 100644
index 00000000..f7f251bb
--- /dev/null
+++ b/test/unit/dialects/oracledb.js
@@ -0,0 +1,94 @@
+const { expect } = require('chai');
+const Oracle_Client = require('../../../lib/dialects/oracledb');
+const { NameHelper } = require('../../../lib/dialects/oracle/utils');
+
+describe('OracleDB Unit Tests', () => {
+  describe('Client', () => {
+    describe('Version Parsing', () => {
+      const fakeBadVersionQueryConnection = {
+        execute: function () {
+          const cb = arguments[arguments.length - 1];
+          cb(null, {
+            rows: [
+              [
+                'Oracle Database Bad Version String That Should Not Happen Unless Something Is Very Wrong',
+              ],
+            ],
+          });
+        },
+      };
+
+      it('should set its version to null if version in options is invalid', () => {
+        const client = new Oracle_Client({
+          client: 'oracledb',
+          version: 'invalid',
+        });
+
+        expect(client.version).to.be.null;
+      });
+
+      it('should return the version provided in options if given from .checkVersion()', async () => {
+        const client = new Oracle_Client({
+          client: 'oracledb',
+          version: '1.13',
+        });
+        const version = await client.checkVersion();
+
+        expect(version).to.equal(client.version);
+      });
+
+      it('should strip off any character suffixes from versions', () => {
+        const client = new Oracle_Client({
+          client: 'oracledb',
+          version: '12c',
+        });
+        expect(client.version).to.equal('12');
+      });
+
+      it('should error indicating the need to specify a version if it could not be auto-detected', async () => {
+        const client = new Oracle_Client({ client: 'oracledb' });
+        try {
+          await client.checkVersion(fakeBadVersionQueryConnection);
+          expect.fail('.checkVersion should have rejected.');
+        } catch (err) {
+          expect(err.message).to.match(
+            /^Unable to detect .* version .* specify the version/
+          );
+        }
+      });
+
+      it('should require a valid version if it could not be auto-detected and provided version is invalid', async () => {
+        const client = new Oracle_Client({
+          client: 'oracledb',
+          version: 'bad version',
+        });
+        try {
+          await client.checkVersion(fakeBadVersionQueryConnection);
+          expect.fail('.checkVersion should have rejected.');
+        } catch (err) {
+          expect(err.message).to.match(
+            /^Invalid .* version number .* Unable to successfully auto-detect/
+          );
+        }
+      });
+    });
+  });
+
+  describe('Name Generation', () => {
+    it('should use a 128 character name limit for Oracle 12.2 and above', () => {
+      const ora122NameHelper = new NameHelper('12.2');
+      const oraNameHelper = new NameHelper('18');
+
+      expect(ora122NameHelper.limit).to.equal(128);
+      expect(oraNameHelper.limit).to.equal(128);
+    });
+
+    it('should use a 30 character name limit for Oracle 12.1 and below', () => {
+      const ora121NameHelper = new NameHelper('12.1');
+      const ora11NameHelper = new NameHelper('11.3');
+
+      expect(ora121NameHelper.limit).to.equal(30);
+      expect(ora11NameHelper.limit).to.equal(30);
+    });
+  });
+});
diff --git a/test/unit/schema-builder/oracle.js b/test/unit/schema-builder/oracle.js
index ea5df400..f93ec883 100644
--- a/test/unit/schema-builder/oracle.js
+++ b/test/unit/schema-builder/oracle.js
@@ -4,7 +4,7 @@ const { expect } = require('chai');
 
 const sinon = require('sinon');
 const Oracle_Client = require('../../../lib/dialects/oracle');
-const client = new Oracle_Client({ client: 'oracledb' });
+const client = new Oracle_Client({ client: 'oracledb', version: '18.0' });
 
 describe('Oracle SchemaBuilder', function () {
   let tableSql;
@@ -931,7 +931,7 @@ describe('Oracle SchemaBuilder', function () {
   });
 
   it('allows dropping a unique compound index with too long generated name', function () {
-    tableSql = client
+    tableSql = new Oracle_Client({ client: 'oracledb', version: '12.0' })
       .schemaBuilder()
       .table('composite_key_test', function (t) {
         t.dropUnique(['column_a', 'column_b']);
diff --git a/test/unit/schema-builder/oracledb.js b/test/unit/schema-builder/oracledb.js
index b33b039e..970b2ce8 100644
--- a/test/unit/schema-builder/oracledb.js
+++ b/test/unit/schema-builder/oracledb.js
@@ -6,7 +6,7 @@ const sinon = require('sinon');
 const Oracle_Client = require('../../../lib/dialects/oracledb');
 const knex = require('../../../knex');
 const FunctionHelper = require('../../../lib/knex-builder/FunctionHelper');
-const client = new Oracle_Client({ client: 'oracledb' });
+const client = new Oracle_Client({ client: 'oracledb', version: '18.0' });
 
 describe('OracleDb SchemaBuilder', function () {
   let tableSql;
@@ -59,6 +59,7 @@ describe('OracleDb SchemaBuilder', function () {
     before(function () {
       knexOracleDb = knex({
         client: 'oracledb',
+        version: '18.0',
         connection: {},
       });
     });
@@ -1143,7 +1144,7 @@ describe('OracleDb SchemaBuilder', function () {
   });
 
   it('allows dropping a unique compound index with too long generated name', function () {
-    tableSql = client
+    tableSql = new Oracle_Client({ client: 'oracledb', version: '12.0' })
       .schemaBuilder()
       .table('composite_key_test', function (t) {
         t.dropUnique(['column_a', 'column_b']);