# 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.

## Creating a connection {#creating-a-connection}

From the Dashboard, go to Connections -> Outgoing -> SQL and set up a new Oracle connection. The database name field is the service name of the database you connect to, e.g. `FREEPDB1`.

If your database is addressed by a SID rather than a service name, add `use_sid=True` to the connection's extra options.

## Querying data {#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.

```python
# -*- 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:

```python
[
    {"user_id": 1, "username": "john.doe"},
    {"user_id": 2, "username": "jane.smith"},
    {"user_id": 3, "username": "bob.jones"}
]
```

## Querying with parameters {#querying-with-parameters}

Bind variables keep your queries safe from injection attacks - pass values through the params dictionary rather than concatenating strings:

```python
# -*- 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 {#returning-a-single-row}

For queries that should match exactly one record, `conn.one` returns that record directly. An exception occurs if zero or multiple rows match. Use `conn.one_or_none` when no match is acceptable:

```python
# -*- 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 {#calling-stored-procedures-with-inputoutput-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:

```python
# -*- 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:

```python
{"output_num": 666, "output_str": "Input was: Hello"}
```

Parameters must appear in the same sequence as declared in the procedure:

```sql
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-returning-multiple-rows}

Procedures that return result sets through REF CURSOR parameters work with `RowsOut` - the cursor is read while the call runs and `rows_out.get()` gives you its rows as a list of dictionaries, one per row:

```python
# -*- 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])

        # The rows the cursor returned, as a list of dicts
        rows = rows_out.get()

        # Return the rows to the caller
        self.response.payload = rows
```

Response:

```python
[
    {"user_id": 1, "username": "john.doe"},
    {"user_id": 2, "username": "jane.smith"}
]
```

The corresponding procedure:

```sql
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 {#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`:

```python
from zato.common.oracledb import NumberIn, NumberOut, StringIn, StringOut, RowsOut
```

## Extra options {#extra-options}

When creating a connection in the Dashboard, you can provide extra options as a list of `key=value` pairs. Each option goes on its own line.

| Option | Description |
| --- | --- |
| `use_sid` | Set to `True` if the database name is a SID rather than a service name |
| `arraysize` | Number of rows fetched at a time (default: 50) |
| `pool_recycle` | After how many seconds a pooled connection is replaced with a fresh one |
| `max_overflow` | How many connections above the pool size may be opened under load |

Example in Dashboard:

```text
use_sid=True
arraysize=100
```

## Audit log {#audit-log}

The connection's `Audit log` dropdown sets what the [audit log](https://zato.io/docs/admin/audit-log/index.html#outgoing-sql-connections) stores per statement - outcome and duration only, the SQL text, the text with parameters, or everything with rows. A procedure call is stored as `BEGIN name(); END;`, its input values as the parameters, its OUT values as the rows.

## How to avoid SQL injection attacks {#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. With MS SQL, a `:param_name` marker with no matching parameter, or a parameter with no matching marker, is refused before the statement is sent to the database.

```python
# 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}'
```

## More resources {#more-resources}

- [SQL databases overview](https://zato.io/docs/dev/examples/sql/index.html)
- [PostgreSQL](https://zato.io/docs/dev/examples/sql/postgresql.html)
- [MySQL](https://zato.io/docs/dev/examples/sql/mysql.html)
- [Microsoft SQL Server](https://zato.io/docs/dev/examples/sql/mssql.html)

## Learn more {#learn-more}

- [Development documentation](https://zato.io/docs/dev/) - The complete reference for writing services
- [Requests and responses](https://zato.io/docs/dev/request-response/) - What a service receives, what it returns and how both are structured
- [Integration examples](https://zato.io/docs/dev/examples/) - Ready-made code for the systems you are likely to connect to
- [IDE and debugging](https://zato.io/docs/dev/ide/) - Write services in the Dashboard or in your own editor
- [Data models](https://zato.io/docs/dev/model/) - Declare inputs and outputs and have them validated for you
- [In-depth API tutorial](https://zato.io/tutorials/main/01.html) - The full platform tutorial, from installation to production patterns