ERROR: connection.query is not a function

[gehal4]

While trying to insert data into the ddb, this error is returned :

TypeError: connection.query is not a function
    at Object.execute (...\.n8n\nodes\node_modules\n8n-nodes-oracle\nodes\Oracle\Sql\OracleSql.node.ts:343:42)
    at Workflow.runNode (...\node_modules\n8n-workflow\src\Workflow.ts:1312:8)
    at ...\node_modules\n8n-core\src\WorkflowExecute.ts:1040:29

It looks like when getting into the insert function is might be an issue with the connection variable.

[gehal4]

Eventually we can modify the insert part as below :

else if (operation === 'insert') {
            try {
                const table = this.getNodeParameter('table', 0, '', { extractValue: true });
                const columnString = this.getNodeParameter('columns', 0);
                const columns = columnString.split(',').map((column) => column.trim());
                const insertItems = (0, GenericFunctions_1.copyInputItems)(items, columns);
                const insertPlaceholder = `(${columns.map((_column) => '?').join(',')})`;
                const options = this.getNodeParameter('options', 0);
                const insertIgnore = options.ignore;
                const insertPriority = options.priority;

        const deleteFirst = options.truncate; //added

        if (deleteFirst){
             await connection.execute(`DELETE FROM ${table}`); // delete existing data from the table
        }

        const insertSQL = `INSERT ${insertPriority || ''} ${insertIgnore ? 'IGNORE' : ''} INTO ${table}(${columnString}) VALUES (${ columns.map((_, i) => `:${i+1}`).join(',')})`;

        const queryItems = insertItems.map(item => Object.values(item));

                const queryResult = await connection.executeMany(insertSQL, queryItems);
                returnItems = this.helpers.returnJsonArray(queryResult[0]);
        await connection.commit(); // commit the transaction
            }
            catch (error) {
        await connection.rollback(); // rollback if error

                if (this.continueOnFail()) {
                    returnItems = this.helpers.returnJsonArray({ error: error.message });
                }
                else {
                    await connection.close();
                    throw error;
                }
            }
        }

Also we can have a Truncate option which allows to delete first the data before inserting :

Around line 130 of file OracleSql.node.js:

options: [
                  {
                      displayName: 'Ignore',
                      name: 'ignore',
                      type: 'boolean',
                      default: true,
                      description: 'Whether to ignore any ignorable errors that occur while executing the INSERT statement',
                  },
{
                      displayName: 'Truncate',
                      name: 'truncate',
                      type: 'boolean',
                      default: false,
                      description: 'Whether to truncate the table first before inserting',
                  },

I tried it locally and it working file. The same logic can be applied to the update part, without the truncate part.