Oracle Visual Query Builder
Builds and executes Oracle SQL queries from visual parameters without requiring users to write SQL. Supports JOINs via a JSON join configuration, optional WHERE/GROUP BY/HAVING/ORDER BY clauses, and row limiting. Results can be returned as plain text/JSON, or exported to HTML, Excel (XLSX), PDF, or all formats.
Usage
Use this node when you need to compose complex Oracle queries interactively. Provide the main table and optional schema, list the columns to select, and optionally add JOINs, filters, grouping, and ordering. Choose the desired output format to get human-readable tables (HTML/PDF), spreadsheet-friendly data (XLSX), or a standard text/JSON response. Typically placed after a credentials provider and before downstream nodes that consume text, JSON, or file outputs.
| Field | Required | Type | Description | Example |
|---|
| credentials_path | True | STRING | Path to the stored Oracle credentials configuration that this node will use to authenticate. | /data/credentials/oracle.json |
| timeout | True | INT | Maximum time (in seconds) to wait for the query to complete before failing. | 60 |
| main_table | True | STRING | Primary table to query from. | employees |
| schema | False | STRING | Oracle schema name. Leave empty to use the current user's default schema. | HR |
| selected_columns | True | STRING | Columns to select. Use comma-separated column names or '*' for all columns. | employee_id, first_name, last_name, department_id |
| join_config | False | STRING | JSON array describing JOINs to apply. Each item supports 'type' (INNER/LEFT/RIGHT/FULL), 'table', optional 'schema', and 'on' join condition. | [{"type":"INNER","table":"departments","schema":"HR","on":"employees.department_id = departments.department_id"}] |
| where_conditions | False | STRING | Filter conditions for the WHERE clause (without the WHERE keyword). | salary > 50000 AND department_id = 10 |
| group_by_columns | False | STRING | Comma-separated columns to GROUP BY. | department_id |
| having_conditions | False | STRING | HAVING clause conditions to filter aggregated results (without the HAVING keyword). | COUNT(*) > 5 |
| order_by_columns | False | STRING | Comma-separated ORDER BY expressions. Append DESC for descending order as needed. | salary DESC, last_name ASC |
| limit_rows | False | INT | Limit the number of rows returned (0 means no limit). | 100 |
| output_format | True | ['text', 'html', 'xlsx', 'pdf', 'all'] | Select output format: text (plain text + JSON), html (HTML table), xlsx (Excel), pdf (PDF), or all (returns all available formats). | all |
Outputs
| Field | Type | Description | Example |
|---|
| text_output | STRING | Human-readable summary or table text of the query results. Included for text and all formats. | Oracle Visual Query: employees Rows: 10 Columns: employee_id, first_name, last_name, department_name |
| json_output | STRING | Raw query results in JSON string form. Always included for text and all formats; included alongside other formats when selected individually. | [{"EMPLOYEE_ID":101,"FIRST_NAME":"Neena","LAST_NAME":"Kochhar","DEPARTMENT_NAME":"Executive"}] |
| html_output | STRING | HTML table rendering of the results when output format is html or all. | | EMPLOYEE_ID | FIRST_NAME | ...
|---|
...
|
| xlsx_file | BYTES | Excel workbook (XLSX) containing the results when output format is xlsx or all. | |
| pdf_file | BYTES | PDF document containing tabular results when output format is pdf or all. | |
Important Notes
- JOIN configuration must be valid JSON: If join_config is invalid JSON, JOINs are skipped.
- Schema prefixing: When 'schema' is provided, the main table is referenced as schema.main_table.
- Row limiting: The query uses 'FETCH FIRST n ROWS ONLY' when limit_rows > 0.
- SQL validity: where_conditions, group_by_columns, having_conditions, and order_by_columns must be valid Oracle SQL fragments (do not include the keywords themselves).
- Selected columns: Use '*' to select all columns or a comma-separated list for specific columns.
- Outputs by format: 'text' returns text + JSON; 'html' returns JSON + HTML; 'xlsx' returns JSON + XLSX; 'pdf' returns JSON + PDF; 'all' returns text, JSON, HTML, XLSX, and PDF.
Troubleshooting
- Invalid join_config JSON: Ensure join_config is a well-formed JSON array. Example: [{"type":"INNER","table":"departments","on":"employees.department_id = departments.department_id"}].
- SQL errors from database: Verify column names, table names, and syntax in where_conditions/group_by_columns/having_conditions/order_by_columns.
- No rows returned: Remove or adjust filters in where_conditions and verify the main_table/schema. Temporarily set limit_rows to 0 to see all results.
- Row limit not applied: Some Oracle environments may not support FETCH FIRST syntax; consider adding a ROWNUM filter in where_conditions (e.g., ROWNUM <= 100) as a workaround.
- Permission errors: Confirm credentials have access to the specified schema and tables.
- Large result sets causing timeouts: Increase timeout, apply stricter filters, or reduce limit_rows.