Oracle Database in Python
Oracle queries and stored procedures with input and output parameters, including REF CURSOR result sets.
Access Oracle databases from Zato through pooled connections defined in the Dashboard. Zato manages the connection lifecycle, so your services simply request a connection by name and start running queries.
Installing cx_Oracle
Oracle connectivity requires the cx_Oracle library, which is not included by default. Add it to your server's Python dependencies as described in the extra Python libraries documentation.
Creating a connection
From the Dashboard, go to Connections -> Outgoing -> SQL and set up a new Oracle connection. Provide your TNS details or host/port/SID configuration.
Note that due to the limitations of the Oracle DB driver, the maximum connection pool size is 10. Setting it to a higher value is not supported.
Querying data
Send SQL to Oracle via conn.execute. Bind variables use the :name syntax, and every row returned becomes a dictionary in the result list.
# -*- coding: utf-8 -*-
# Zato
from zato.server.service import Service
class GetAllUsers(Service):
""" Returns all users from the database.
"""
def handle(self):
# Get the Oracle connection from the pool
conn = self.out.sql['My Oracle DB']
# Build the query
query = 'SELECT * FROM users'
# Execute the query - returns a list of dicts
response = conn.execute(query)
# Return the results to the caller
self.response.payload = response
Response:
[
{"user_id": 1, "username": "john.doe"},
{"user_id": 2, "username": "jane.smith"},
{"user_id": 3, "username": "bob.jones"}
]
Querying with parameters
Bind variables keep your queries safe from injection attacks - pass values through the params dictionary rather than concatenating strings:
# -*- coding: utf-8 -*-
# Zato
from zato.server.service import Service
class GetUserById(Service):
""" Fetches a user by their user_id.
"""
input = 'user_id'
def handle(self):
# Get the Oracle connection from the pool
conn = self.out.sql['My Oracle DB']
# Build the parameterized query
query = 'SELECT * FROM users WHERE user_id = :user_id'
# Assign input data to query parameters
params = {'user_id': self.request.input.user_id}
# Execute the query with parameters
response = conn.execute(query, params)
# Return the results to the caller
self.response.payload = response
Returning a single row
For queries that should match exactly one record, conn.one hands you that record directly. An exception occurs if zero or multiple rows match. Use conn.one_or_none when no match is acceptable:
# -*- coding: utf-8 -*-
# Zato
from zato.server.service import Service
class GetSingleUser(Service):
""" Returns exactly one user, raises if not found.
"""
input = 'user_id'
def handle(self):
# Get the Oracle connection from the pool
conn = self.out.sql['My Oracle DB']
# Build the query
query = 'SELECT * FROM users WHERE user_id = :user_id'
# Assign input data to query parameters
params = {'user_id': self.request.input.user_id}
# Run the query - returns a dict, raises if not found
result = conn.one(query, params)
# Return the result to the caller
self.response.payload = result
class GetUserOrNone(Service):
""" Returns a user or None if not found.
"""
input = 'user_id'
def handle(self):
# Get the Oracle connection from the pool
conn = self.out.sql['My Oracle DB']
# Build the query
query = 'SELECT * FROM users WHERE user_id = :user_id'
# Assign input data to query parameters
params = {'user_id': self.request.input.user_id}
# Run the query - returns dict or None if not found
result = conn.one_or_none(query, params)
# Return the result or an error
if result:
self.response.payload = result
else:
self.response.payload = {'error': 'User not found'}
self.response.status_code = 404
Calling stored procedures with input/output parameters
Oracle procedures frequently use IN and OUT parameters. Zato provides wrapper classes that handle the type conversions and let you read output values after the call:
# -*- coding: utf-8 -*-
# Zato
from zato.common.oracledb import NumberIn, NumberOut, StringIn, StringOut
from zato.server.service import Service
class CallProcessData(Service):
""" Calls a stored procedure with input and output parameters.
"""
def handle(self):
# Get the Oracle connection from the pool
conn = self.out.sql['My Oracle DB']
# Prepare input parameters
in_num = NumberIn(333)
in_str = StringIn('Hello')
# Prepare output parameters - the procedure will write to these
out_num = NumberOut()
out_str = StringOut(size=200)
# Build the parameter list in the order expected by the procedure
params = [in_num, in_str, out_num, out_str]
# Call the stored procedure
conn.callproc('process_data', params)
# Extract and return the output values
self.response.payload = {
'output_num': out_num.get(),
'output_str': out_str.get()
}
Response:
Parameters must appear in the same sequence as declared in the procedure:
CREATE OR REPLACE PROCEDURE process_data (
input_num IN NUMBER,
input_str IN VARCHAR2,
output_num OUT NUMBER,
output_str OUT VARCHAR2
)
AS
BEGIN
output_num := input_num * 2;
output_str := 'Input was: ' || input_str;
END process_data;
Procedures returning multiple rows
Procedures that return result sets through REF CURSOR parameters work with RowsOut - it captures the cursor and lets you iterate the rows:
# -*- coding: utf-8 -*-
# Zato
from zato.common.oracledb import RowsOut
from zato.server.service import Service
class GetUsersFromProcedure(Service):
""" Calls a procedure that returns multiple rows via a cursor.
"""
def handle(self):
# Get the Oracle connection from the pool
conn = self.out.sql['My Oracle DB']
# Prepare the output parameter for the cursor
rows_out = RowsOut()
# Call the procedure - it will populate rows_out
conn.callproc('get_users', [rows_out])
# Extract the rows from the cursor
rows = list(rows_out.get())
# Return the rows to the caller
self.response.payload = rows
The corresponding procedure:
CREATE OR REPLACE PROCEDURE get_users (
recordset OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN recordset FOR
SELECT user_id, username
FROM users
ORDER BY user_id;
END get_users;
Available parameter types
These wrapper classes handle Oracle-specific parameter binding:
| Class | Purpose |
|---|---|
NumberIn | Input NUMBER parameter |
NumberOut | Output NUMBER parameter |
StringIn | Input VARCHAR2 parameter |
StringOut | Output VARCHAR2 parameter (optionally specify size) |
RowsOut | Output SYS_REFCURSOR for result sets |
All wrappers live in zato.common.oracledb:
Extra options
When creating a connection in the Dashboard, you can provide extra options as a list of key=value pairs. These are passed directly to the database driver. Each option goes on its own line.
| Option | Description |
|---|---|
encoding | Character encoding (e.g., UTF-8) |
nencoding | National character set encoding |
mode | Connection mode (SYSDBA, SYSOPER) |
arraysize | Number of rows fetched at a time (default: 50) |
service_name | Oracle service name (alternative to SID) |
Example in Dashboard:
How to avoid SQL injection attacks
Always use parameterized queries with named parameters (:param_name) rather than string formatting. This prevents SQL injection attacks and makes your queries more readable.
# Correct - parameterized query
query = 'SELECT * FROM users WHERE user_id = :user_id AND status = :status'
params = {'user_id': 123, 'status': 'active'}
result = conn.execute(query, params)
# Wrong - string formatting (vulnerable to SQL injection)
# query = f'SELECT * FROM users WHERE user_id = {user_id}'