OracleInstall

Overview

This n8n node allows you to execute custom SQL queries against an Oracle database. It is designed for scenarios where you need to fetch, manipulate, or analyze data stored in Oracle from within your n8n workflows. Typical use cases include:

• Automating data extraction and reporting from Oracle databases.
• Integrating Oracle data with other systems via n8n.
• Running parameterized queries for dynamic data processing.

Practical examples:

• Fetching all products with low stock for inventory alerts.
• Updating user records based on workflow logic.
• Generating reports by joining multiple tables.

Properties

Name	Type	Meaning
Query	String	The SQL query to execute. Supports bind variables (parameters) for dynamic values. Example: SELECT id, name FROM product WHERE quantity > :quantity AND price <= :price.
Options	Collection	Additional options for the query execution: - Bind Params (JSON): An object containing key-value pairs for bind variables used in the query. - Thick Mode (Boolean): Enables thick connection mode.
Notice (when Thick Mode enabled)	Notice	Informs that Oracle Instant Client must be available when using thick mode. Provides a link to relevant documentation.

Output

The node outputs an array of JSON objects representing the result set of the executed query. Each object corresponds to a row returned by the query, with keys as column names and values as their respective data.

• If the query does not return any rows or output, the node returns { "success": true }.
• If an error occurs and "Continue On Fail" is enabled, the output will include an object like { "error": "Error message here" }.
• Binary data is not produced by this node.

Dependencies

• Oracle Database: You must have access credentials (user, password, host, port, SID) for the target Oracle instance.
• n8n Credentials: Requires an "oracleApi" credential configured in n8n.
• Node.js OracleDB Driver (oracledb): This dependency is bundled with the node.
• Thick Mode: If enabled, the Oracle Instant Client must be installed and accessible on the host running n8n. See Enabling Node-oracledb Thick Mode.

Troubleshooting

Common Issues:

• Connection Errors:
  • Message: "ORA-12154: TNS:could not resolve the connect identifier specified"
    Resolution: Check your host, port, and SID values in the credentials.
• Authentication Errors:
  • Message: "ORA-01017: invalid username/password; logon denied"
    Resolution: Verify your Oracle username and password.
• Missing Oracle Instant Client (Thick Mode):
  • Message: "DPI-1047: Cannot locate a 64-bit Oracle Client library"
    Resolution: Install the Oracle Instant Client and ensure it is in your system's PATH.
• Query Syntax or Bind Param Errors:
  • Message: "ORA-01036: illegal variable name/number"
    Resolution: Ensure your bind parameters match those referenced in your SQL query.

General Advice:

• Always test your query directly in an Oracle client before using it in n8n.
• Use the "Bind Params" option for safer, parameterized queries.

Links and References

• OracleDB Node.js Documentation
• Bind Variables in OracleDB
• Enabling Node-oracledb Thick Mode