Oracle Visual Query Builder
Builds and runs Oracle SQL queries using visual-style inputs (tables, joins, filters) without writing SQL. Supports JOINs, WHERE, GROUP BY, HAVING, ORDER BY, and row limits. Results can be returned in text/JSON by default, or exported in HTML/XLSX/PDF when requested.
Usage
Use this node when you want to query Oracle data by specifying tables, join relationships, and conditions via parameters. It fits into workflows that retrieve tabular data for reporting or analysis, optionally exporting results to HTML, Excel, or PDF. Provide Oracle credentials, set the main table and optional schema, define selected columns and joins, add filters/grouping/sorting, and choose the output format.
| Field | Required | Type | Description | Example |
|---|
| credentials_path | True | STRING | Path or reference to stored Oracle credentials configured for the service. | /connections/oracle/default.json |
| timeout | True | INT | Request timeout in seconds for executing the query. | 60 |
| main_table | True | STRING | Primary table to query from. | employees |
| schema | True | STRING | Optional schema name to qualify tables. Leave empty to use the current user schema. | HR |
| selected_columns | True | STRING | Comma-separated list of columns to select, or * for all columns. | employee_id, first_name, last_name |
| join_config | True | STRING | JSON array describing JOINs to include. Each item can include type (INNER/LEFT/RIGHT), table, optional schema, and the ON clause. | [{"type":"INNER","table":"departments","schema":"HR","on":"employees.department_id = departments.department_id"}] |
| where_conditions | True | STRING | Raw WHERE conditions without the WHERE keyword. | salary > 50000 AND department_id = 10 |
| group_by_columns | True | STRING | Comma-separated list of columns for GROUP BY. | department_id |
| having_conditions | True | STRING | Raw HAVING conditions without the HAVING keyword. Only applies if GROUP BY is used. | COUNT(*) > 5 |
| order_by_columns | True | STRING | Comma-separated list of ORDER BY expressions. Append DESC for descending order. | salary DESC, last_name |
| limit_rows | True | INT | Limit the number of rows returned (0 means no limit). | 100 |
| output_format | True | ['text','html','xlsx','pdf','all'] | Choose the output format. 'text' returns plain text plus JSON. 'html', 'xlsx', or 'pdf' produce those formats. 'all' returns all available formats. | text |
Outputs
| Field | Type | Description | Example |
|---|
| text | STRING | Human-readable summary of the query results, including a title and possibly a preview. | Oracle Visual Query: employees — 10 rows returned |
| json | STRING | JSON-encoded full result payload, including rows and metadata as returned by the service. | {"rows":[{"EMPLOYEE_ID":100,"FIRST_NAME":"Steven"}],"count":1} |
| html | STRING | HTML table representation of the results, populated when the node generates HTML or when 'all' is selected. | |
| xlsx | STRING | A reference or encoded content for an Excel (XLSX) export of the results, populated when XLSX or 'all' is selected. | |
| pdf | STRING | A reference or encoded content for a PDF export of the results, populated when PDF or 'all' is selected. | |
Important Notes
- Join configuration must be valid JSON: If join_config is not valid JSON, joins are skipped and a warning is logged.
- Schema is optional: If provided, it prefixes the main table and joined tables to fully qualify them.
- Limit behavior: Row limiting uses 'FETCH FIRST n ROWS ONLY'. Ensure your Oracle version supports it (Oracle 12c+).
- Output selection: Use output_format='all' to generate exports (HTML/XLSX/PDF). Otherwise, the default returns text and JSON.
- Raw clauses: where_conditions, having_conditions, and order_by_columns are inserted as-is; ensure proper Oracle SQL syntax to avoid errors.
Troubleshooting
- Invalid join_config JSON: Replace join_config with a valid JSON array. Example format: [{"type":"INNER","table":"departments","schema":"HR","on":"employees.department_id = departments.department_id"}].
- No results or empty output: Check where_conditions and joins for over-restriction; test with fewer filters or limit_rows=0.
- Oracle version errors for FETCH FIRST: If you see syntax errors, your Oracle version may not support FETCH FIRST. Remove limit_rows or use equivalent ROWNUM filters in where_conditions.
- Permission or schema errors (ORA-xxxx): Verify credentials and schema/table names. Try leaving schema empty to use the current user schema.
- Timeouts: Increase the timeout input or optimize the query (fewer joins, narrower columns, add filters).
