Oracle Database icon

Oracle Database

Interact with Oracle Database
Join our community

Actions4

Overview

This node enables executing SQL queries and statements against an Oracle Database using connection pooling for efficient resource management. It supports two main operations:

  • Execute Query: Run SELECT queries to retrieve data.
  • Execute Statement: Run DML (INSERT, UPDATE, DELETE), DDL (CREATE, DROP, ALTER), or stored procedures.

The node is useful in scenarios where you need to integrate Oracle database operations into your workflows, such as fetching user data, updating records, or managing database schema changes programmatically.

Practical examples:

  • Fetching customer details by ID with a parameterized SELECT query.
  • Inserting new records into a table with an INSERT statement.
  • Running maintenance scripts like creating or dropping tables.

Properties

Name Meaning
SQL Query The SQL statement to execute. For "Execute Query" operation, typically a SELECT statement.
Parameters JSON object defining bind parameters for the SQL query, allowing safe substitution of values.
Include Other Input Fields Boolean flag indicating whether to include all input fields alongside query results in the output.
Result Format Controls case formatting of the query result keys for "Execute Query": Uppercase, Lowercase, or Original.
Connection Pool Options Collection of options to configure the connection pool:
- Pool Min: Minimum number of connections.
- Pool Max: Maximum number of connections.
- Queue Timeout (Ms): Time to wait for a connection when pool is busy.

Output

  • The output is an array of JSON objects representing rows returned from the query or the result of the executed statement.
  • For Execute Query, each item corresponds to a row with column names formatted according to the selected result format (uppercase, lowercase, or original).
  • For Execute Statement, the output includes metadata such as the number of affected rows and any output bind variables.
  • If "Include Other Input Fields" is enabled, the output JSON merges the original input fields with the query results or execution metadata.
  • The node does not output binary data.

Dependencies

  • Requires an Oracle Database accessible via credentials that include user, password, host, port, and service name or connection string.
  • Uses Oracle's official Node.js driver (oracledb package).
  • Environment variables may be needed if using connection strings stored externally.
  • Optional environment variables for Oracle client library paths if not using thin mode.
  • Requires configuration of an API key credential for authentication to the Oracle database.

Troubleshooting

  • Connection pool not initialized: Ensure credentials are correctly set and the node has successfully created a connection pool before executing queries.
  • Connection timeout (NJS-040): Indicates all connections in the pool are busy. Consider increasing the maximum pool size or optimizing queries.
  • Invalid JSON parameters: The "Parameters" field must contain valid JSON. Errors here require correcting the JSON syntax.
  • Invalid query type for SELECT operation: Using non-SELECT statements with the "Execute Query" operation will cause errors; use "Execute Statement" instead.
  • Environment variable not set: When using connection strings from environment variables, ensure those variables are defined in the runtime environment.
  • Oracle client initialization errors: If not using thin mode, verify Oracle client libraries are installed and environment variables for their paths are set.

Links and References

Discussion