fix(oracle): fix changeColumn SQL for BLOB to avoid implicit conversion (#17719)

hjamil-24 · WikiRik · web-flow · commit 5b7c8015c907 · 2025-03-28T12:24:49.000+01:00
Co-authored-by: Rik Smale &lt;13023439+WikiRik@users.noreply.github.com&gt;
diff --git a/src/dialects/oracle/query-generator.js b/src/dialects/oracle/query-generator.js
@@ -504,6 +504,7 @@ export class OracleQueryGenerator extends AbstractQueryGenerator {
    * @param {string} attributeName The name of the attribute which would get altered
    */
   _modifyQuery(definition, table, attributeName) {
+    definition = definition.startsWith('BLOB') ? definition.replace('BLOB ', '') : definition;
     const query = Utils.joinSQLFragments([
       'ALTER TABLE',
       this.quoteTable(table),
diff --git a/test/unit/dialects/oracle/query-generator.test.js b/test/unit/dialects/oracle/query-generator.test.js
@@ -5,7 +5,8 @@ const Support   = require('../../support'),
   DataTypes = require('sequelize/lib/data-types'),
   expectsql = Support.expectsql,
   current   = Support.sequelize,
-  sql       = current.dialect.queryGenerator;
+  sql       = current.dialect.queryGenerator,
+  sinon = require('sinon');
 
 if (current.dialect.name === 'oracle') {
   describe('createTable', () => {
@@ -28,4 +29,36 @@ if (current.dialect.name === 'oracle') {
       });
     });
   });
-}
+
+  // Alter table for blob tries implicit type conversion if datatype is mentioned in SQL, which leads to failure.
+  describe('changeColumn', () => {
+    const Model = current.define('items', {
+      blobCol: {
+        type: DataTypes.BLOB
+      }
+    }, { timestamps: false });
+
+    before(function() {
+      this.stub = sinon.stub(current, 'query').resolvesArg(0);
+    });
+
+    beforeEach(function() {
+      this.stub.resetHistory();
+    });
+
+    after(function() {
+      this.stub.restore();
+    });
+    
+    it('properly generate alter queries for BLOB', () => {
+      return current.getQueryInterface().changeColumn(Model.getTableName(), 'blobCol', {
+        type: DataTypes.BLOB,
+        allowNull: false
+      }).then(sql => {
+        expectsql(sql, {
+          oracle: 'DECLARE CONS_NAME VARCHAR2(200); BEGIN BEGIN EXECUTE IMMEDIATE \'ALTER TABLE "items" MODIFY "blobCol" NOT NULL\'; EXCEPTION WHEN OTHERS THEN IF SQLCODE = -1442 OR SQLCODE = -1451 THEN EXECUTE IMMEDIATE \'ALTER TABLE "items" MODIFY "blobCol" \'; ELSE RAISE; END IF; END; END;'
+        });
+      });
+    });
+  });
+}
