How to create n8n workflows that connect to Oracle Database

The n8n workflow automation tool describes itself as “a workflow automation tool that combines AI capabilities with business process automation.” Several users have created modules for workflow nodes that connect to Oracle Database. Here I try one of them out for the first time.

n8n requires a Node.js environment. I have this all set up locally so I followed the n8n instructions Install globally with npm. You may want to go down the Docker route instead.

I installed n8n into Node.js using:

$ npm install n8n -g

And then installed the n8n-nodes-oracle-database-parameterization module which seems to be the most advanced of the Oracle Database connectors. (Note this is a 3rd party module — Oracle does not contribute to it). Underneath, it does use Oracle’s node-oracledb driver:

$ npm install n8n-nodes-oracle-database-parameterization

Starting n8n was a simple matter of running:

$ export N8N_RUNNERS_ENABLED=true
$ export N8N_CUSTOM_EXTENSIONS=$(pwd)/node_modules/n8n-nodes-oracle-database-parameterization
$ n8n start

With this in place, I opened the default page http://localhost:5678 in my browser, and chose some credentials. The workflow pane then appeared:

To test out Oracle Database connectivity, I clicked on “Add first step…”. There are a lot of nodes that could be used!:

I searched for Oracle. If you don’t see it, make sure that N8N_CUSTOM_EXTENSIONS is correctly set and restart n8n:

I selected it. As well as adding an Oracle Database node, this will automatically create a default trigger node to start the workflow on a user click.

In the Oracle Database node pane I first clicked on “Select Credential”:

At the top I hovered over the “Oracle Credentials account” field and set a name “cj”:

I then entered my database credentials and connection string, and clicked “Save”:

Back on the Oracle Database pane I entered a SQL statement, here a simple query from the EMP table:

I didn’t add any parameters. I also left the Settings tab unchanged:

Clicking “Test step” produced output, here shown in Tabular form on the right. (N8n also allows you to see the same output as JSON or in a schema form):

Back on the workflow pane I decided to add another Oracle node, by clicking on the “+” sign and again searching for “Oracle” in the node list:

In this node I queried the DEPT table using bind variable placeholders:

To assign values to those placeholders, I clicked “Add Parameter”, set the placeholder name “en”, and simply dragged from the word “DEPTNO” in the “rows” column on the left, into the parameter “Value” field. N8n automatically changed the field to the appropriate syntax, and changed the type to an expression. This connects the output from the workflow’s previous node into the current one:

I added a parameter and did the same for the second bind variable placeholder “dno”, dragging the text “DEPTNO” from the left into the Values field:

Note n8n-nodes-oracle-database-parameterization only supports String and Number binds.

Testing the step showed the query from DEPT worked:

Let’s add a third node, this time one that inserts data into the database. But first, behind the scenes I created a table “t” using SQL*Plus:

SQL> create table t (name varchar2(10), department varchar2(13));

In the workflow pane I again searched for “Oracle” and selected it:

In the Oracle Database pane I entered the SQL INSERT statement and created the first bind variable placeholder “n”, setting its value by dragging the left hand text NAME to the Value field:

The second bind variable placeholder “d” was added and its value set by dragging DNAME to the appropriate Values field:

Testing the step shows that one row was inserted:

This was easily verified in SQL*Plus. The data has been inserted and committed:

SQL> select * from t;

NAME       DEPARTMENT
---------- -------------
SCOTT      RESEARCH

Here is the final workflow. Yes mine is all Oracle Database, but n8n has many pre-supplied nodes that you can add to your own workflows:

I hope this post has given you enough information to get started with n8n and Oracle Database. The only special step was installing an appropriate Oracle module, and setting N8N_CUSTOM_EXTENSIONS.

node-oracledb Resources

• Node-oracledb installation instructions are here.
• Node-oracledb documentation is here.
• Node-oracledb change log is here.
• Issues and questions about node-oracledb can be posted on GitHub or Slack (link to join Slack).