oracledb insert with DEFAULT values

[blazekas]

Environment

Knex version: 0.14.6
Database + version: Oracle 11g
OS: Windows 10

Bug

1. There is a bug in oracledb/query/compiler.js "insert" function - only first two instances of 'DEFAULT' are replaced which leads to ORA-06550 error.
   See: https://www.w3schools.com/jsref/jsref_replace.asp
   "If you are replacing a value (and not a regular expression), only the first instance of the value will be replaced."

        const parameterizedValuesWithoutDefaultAndBlob = parameterizedValues
          .replace('DEFAULT, ', '')
          .replace(', DEFAULT', '')
          .replace('EMPTY_BLOB(), ', '')
          .replace(', EMPTY_BLOB()', '');

2. Error message:
   { Error: ORA-06550: line 1, column 242:
   PLS-00103: Encountered the symbol "DEFAULT" when expecting one of the following:

   ( - + case in mod new not null out
   
   continue avg count current exists max min prior sql stddev
   sum variance execute forall merge time timestamp interval
   date
   pipe
   
   <an errorNum: 6550, offset: 241 }

3. Test code:

var knex = require('knex')({
      client: 'oracledb',
      connection: {
         user: 'user',
         password: 'password',
         connectString: 'localhost/XE'
      }
   });

var data = [{
      a: 1,
      b: 1,
      c: 1,
      d: 1
   }, {
      a: 2
   }
];

knex.schema.dropTableIfExists('insert_default').then(function () {
   return knex.schema.createTable('insert_default', function (table) {
      table.integer('a');
      table.integer('b');
      table.integer('c');
      table.integer('d');
   })

}).then(function () {
   return knex('insert_default').insert(data);

}).then(function () {
   process.exit(0);

}).catch(function (err) {
   console.log(err);
   process.exit(1);
});

@atiertant

[blazekas]

Should be:

        const parameterizedValuesWithoutDefaultAndBlob = parameterizedValues
          .replace(/DEFAULT, /g, '')
          .replace(/, DEFAULT/g, '')
          .replace(/EMPTY_BLOB(), /g, '')
          .replace(/, EMPTY_BLOB()/g, '');

[kibertoad]

Released in 1.0.4