BGS-Oracledb-connector icon

BGS-Oracledb-connector

Execute queries on Oracle Database
Join our community

Actions5

Overview

This node executes SQL queries on an Oracle Database. It supports running custom SQL queries with named parameters, allowing dynamic and flexible data retrieval or manipulation. Common use cases include fetching user data, updating records, or running complex queries with parameter binding. For example, you can execute a query like 'SELECT * FROM users WHERE id = :id' with parameters to fetch specific user details.

Use Case Examples

  1. Fetching user details by ID using a parameterized SELECT query.
  2. Running an update query to modify records based on dynamic input.
  3. Executing complex queries with multiple parameters for reporting or data analysis.

Properties

Name Meaning
Query The SQL query to execute. Supports named parameters using :paramName syntax.
Query Parameters JSON object containing parameters to bind to the query. Parameter names should match those used in the query (e.g., :id).
Options Additional execution options including auto-commit, fetch size, and maximum rows to return.

Output

JSON

  • json
    • success - Indicates successful execution for queries that do not return rows.
    • rowsAffected - Number of rows affected by the query (for insert, update, delete operations).

Dependencies

  • Oracle Database client library (oracledb)

Troubleshooting

  • Invalid JSON in Query Parameters or Procedure Parameters: Ensure the JSON input is correctly formatted.
  • Oracle client initialization errors: The node attempts to initialize the Oracle client; if it fails, it logs a message but continues. Ensure Oracle client libraries are properly installed.
  • Connection errors: Verify credentials and connection string parameters (host, port, service name).
  • Query execution errors: Check SQL syntax and parameter names matching between query and parameters.
  • Transaction issues: If auto-commit is disabled, ensure to manage transactions properly to avoid locks or uncommitted changes.

Links

Discussion