Oracle
Oracle Database integration with positional parameter binding and schema discovery.
Execute SQL queries in Oracle Database with Axellero's workflow integration features.
Connector Configuration
| Parameter | Type | Required | Description |
|---|---|---|---|
host | TEXT | Yes | Database host address |
port | INT | No | Database port (default: 1521) |
user | TEXT | Yes | Database username |
password | TEXT | Yes | Database password |
service | TEXT | Yes | Oracle service name (e.g., XEPDB1) |
SQL Query Execution
Positional Parameter Binding
Oracle nodes use ? placeholders for safe parameter binding:
SELECT * FROM employees
WHERE department_id = ?
AND salary > ?
ORDER BY hire_date DESC;Variables (Array Format):
{
"variables": [10, 50000]
}Workflow Context Integration
Access data from other workflow nodes in your queries:
-- Use outputs from other nodes
SELECT * FROM orders
WHERE customer_id = ?
AND order_date >= ?;Variables:
{
"variables": [
"{{ctx.nodes.userInput.outputs.customerId}}",
"{{ctx.nodes.dateFilter.outputs.startDate}}"
]
}Editor Features
SQL Editor Capabilities:
- Auto-completion: Table and column names from Oracle data dictionary
- Parameter Management: Visual indicators for
?placeholder positions - Schema Discovery: Automatic detection of user-accessible tables and columns
- Oracle SQL Validation: Real-time syntax checking with Oracle-specific features
For complete editor details, see Code Editing & Schema Reference.
Schema Discovery
Axellero automatically discovers your Oracle database structure:
- User Tables: All tables accessible to the connected user
- Columns: Column names, data types, and constraints from
all_tab_columns - Auto-completion: Intelligent suggestions based on your actual Oracle schema
Connection Features
Connection Pooling
Oracle connections use automatic connection pooling:
- Pool Size: Maximum 10 concurrent connections
- Lifetime: 30-minute connection lifetime
- Automatic Cleanup: Expired connections removed automatically
Connection Testing
Test database connectivity and Oracle service availability.
Result Processing
Query Results
{
"rows": [
{
"employee_id": 100,
"first_name": "Steven",
"last_name": "King",
"hire_date": "2003-06-17T00:00:00Z"
}
],
"rowsAffected": 1
}Oracle Data Type Mapping
- NUMBER: Oracle numbers → JavaScript numbers
- VARCHAR2/CHAR: Oracle strings → JavaScript strings
- DATE/TIMESTAMP: Oracle dates → ISO string format
- CLOB: Oracle large text → JavaScript strings
- BLOB: Oracle binary data → byte arrays
Workflow Integration Patterns
Using Query Results in Other Nodes
// Access Oracle results in subsequent JavaScript nodes
const employees = ctx.nodes.oracleQuery.outputs.rows;
const managers = employees.filter(emp => emp.job_title.includes('Manager'));
callback({ managers: managers }, null);Conditional Logic with Database Results
// Use in Branch node conditions
{{ctx.nodes.oracleQuery.outputs.rows && ctx.nodes.oracleQuery.outputs.rows != ""}}
// Check for errors
{{!ctx.nodes.oracleQuery.error}}
// Check if query succeeded
{{ctx.nodes.oracleQuery.outputs.rowsAffected > 0}}Loop Processing Database Results
// Loop over query results
// Loop Value: {{ctx.nodes.oracleQuery.outputs.rows}}
// Access current row in loop body
{{ctx.nodes.loopNode.outputs.item.employee_id}}
{{ctx.nodes.loopNode.outputs.item.department_name}}Oracle-Specific Features
Oracle Functions and Syntax
-- Oracle date functions
SELECT employee_id,
MONTHS_BETWEEN(SYSDATE, hire_date) as months_employed,
TO_CHAR(hire_date, 'DD-MON-YYYY') as formatted_date
FROM employees
WHERE EXTRACT(YEAR FROM hire_date) = ?;Working with Oracle Sequences
-- Access Oracle sequences
SELECT employee_seq.NEXTVAL FROM DUAL;
-- Use in workflow context
SELECT ? as request_id, employee_seq.NEXTVAL as employee_id FROM DUAL;Error Handling
Connection Errors
- TNS-12541: Check host, port, and Oracle listener status
- ORA-01017: Verify username and password
- ORA-12154: Confirm service name configuration
- ORA-00942: Ensure user has SELECT privileges on tables
Query Errors
- Parameter Count: Ensure
?count matches variables array length - Data Type Mismatch: Verify parameter types match Oracle column types
- Privilege Issues: Confirm user has required permissions
Configuration Examples
Basic Connection
{
"host": "localhost",
"port": 1521,
"user": "hr",
"password": "hr_password",
"service": "XEPDB1"
}Enterprise Connection
{
"host": "oracle-prod.company.com",
"port": 1521,
"user": "{{ctx.vars.oracle_user}}",
"password": "{{ctx.vars.oracle_password}}",
"service": "PROD_SERVICE"
}Sample Query with Parameters
{
"query": "SELECT employee_id, first_name, last_name FROM employees WHERE department_id = ? AND salary > ? ORDER BY salary DESC",
"variables": [
"{{ctx.nodes.deptFilter.outputs.selectedDept}}",
"{{ctx.nodes.salaryFilter.outputs.minSalary}}"
]
}Parameter Binding Best Practices
Array Order Matters
-- Query with multiple parameters
SELECT * FROM employees
WHERE department_id = ? -- Position 0
AND salary BETWEEN ? AND ? -- Positions 1, 2
AND hire_date > ?; -- Position 3Corresponding Variables:
{
"variables": [
50, // department_id
30000, // min salary
100000, // max salary
"2020-01-01" // hire_date
]
}Dynamic Parameter Arrays
// Build parameter arrays dynamically in JavaScript nodes
const filters = [];
let query = "SELECT * FROM employees WHERE 1=1";
if (ctx.nodes.input.outputs.department) {
query += " AND department_id = ?";
filters.push(ctx.nodes.input.outputs.department);
}
if (ctx.nodes.input.outputs.minSalary) {
query += " AND salary >= ?";
filters.push(ctx.nodes.input.outputs.minSalary);
}
callback({
query: query,
variables: filters
}, null);Methods Reference
| Method | Description |
|---|---|
executeQuery | Execute SQL query with positional parameters |
testConnection | Validate Oracle database connection |
fetchSchema | Discover Oracle database schema information |