Oracle Visual Query Builder
Builds and executes Oracle SQL SELECT queries using a visual, parameter-driven interface. Supports JOINs via JSON configuration, WHERE/GROUP BY/HAVING/ORDER BY clauses, and optional row limits. Results can be returned in multiple formats, including text, HTML, Excel, and PDF.
Usage
Use this node when you need to assemble complex Oracle queries without manually writing SQL. It is ideal for combining tables with JOINs, filtering with conditions, grouping, and ordering. Connect it to your Oracle credentials and set visual parameters to generate and run the query, then choose the desired output format for downstream use.
| Field | Required | Type | Description | Example |
|---|
| credentials_path | True | CREDENTIALS | Path or reference to Oracle credentials configured for the service. | |
| timeout | True | INT | Maximum time in seconds to wait for the operation before timing out. | 60 |
| main_table | True | STRING | Primary table to select from. | employees |
| schema | True | STRING | Schema name to qualify tables. Leave empty to use the current user schema. | HR |
| selected_columns | True | STRING | Columns to return. Use comma-separated list or * to select all. | employee_id, first_name, last_name |
| join_config | True | STRING | JSON array describing JOINs. Each item should include type, table, optional schema, and on condition. | [{"type": "INNER", "table": "departments", "schema": "HR", "on": "employees.department_id = departments.department_id"}] |
| where_conditions | True | STRING | Raw WHERE clause conditions without the WHERE keyword. | salary > 50000 AND department_id = 10 |
| group_by_columns | True | STRING | Comma-separated columns for GROUP BY. | department_id |
| having_conditions | True | STRING | Raw HAVING clause conditions without the HAVING keyword. | COUNT(*) > 5 |
| order_by_columns | True | STRING | Comma-separated ORDER BY expressions. Add DESC for descending. | salary DESC, last_name |
| limit_rows | True | INT | Maximum number of rows to return (0 means no limit). | 100 |
| output_format | True | CHOICE | Format of the output: text (plain text + JSON), html (HTML table), xlsx (Excel), pdf (PDF), or all (all formats). | text |
Outputs
| Field | Type | Description | Example |
|---|
| text | STRING | Human-readable summary or table preview of the query results. | Oracle Visual Query: employees — 100 rows returned |
| json | JSON | Raw result data serialized as JSON. | {"rows": [{"EMPLOYEE_ID": 101, "FIRST_NAME": "Neena"}], "row_count": 10} |
| html | HTML | HTML table representation of the results (available when output_format is html or all). | |
| xlsx | XLSX | Excel file bytes/handle for the results (available when output_format is xlsx or all). | |
| pdf | PDF | PDF file bytes/handle for the results (available when output_format is pdf or all). | |
Important Notes
- Selected columns accept '*' or a comma-separated list. Ensure column names are correctly qualified if ambiguous.
- join_config must be valid JSON. Invalid JSON is ignored and JOINs are skipped with a warning.
- Schema is optional; when omitted, the current user schema is used. JOIN targets can also specify their own schema.
- WHERE, GROUP BY, HAVING, and ORDER BY inputs are raw SQL snippets; no validation is performed.
- Row limiting uses 'FETCH FIRST n ROWS ONLY' which requires Oracle 12c or later. For older versions, results may not limit as expected.
- Choosing output_format = 'all' returns all supported formats; otherwise, a default text/JSON response is provided.
Troubleshooting
- Query returns no results: Verify where_conditions and join_config ON clauses. Test the base table and relax filters.
- Invalid JSON in join_config: Provide well-formed JSON array with objects that include type, table, and on. Example format is shown in the input tooltip.
- ORA-00942: table or view does not exist: Confirm schema and table names. Provide the schema parameter or qualify tables in join_config.
- Permission errors: Ensure the credentials have SELECT privileges on all referenced tables and schemas.
- Output not limited: If using an Oracle version prior to 12c, FETCH FIRST may not work. Set limit_rows to 0 and add a ROWNUM filter in where_conditions.
- Timeouts: Increase the timeout input or simplify the query (reduce JOINs, filters, or result size).
