Oracle

The Oracle query runner allows you to connect to and query Oracle databases, supporting both direct connections and DSN service names.

Configuration

The Oracle query runner requires the following configuration:

{
  "user": "username",
  "password": "password",
  "host": "oracle.example.com",
  "port": 1521,
  "servicename": "orcl",
  "encoding": "UTF-8"
}

For using a DSN service name directly (without host/port):

{
  "user": "username",
  "password": "password",
  "host": "_useservicename",
  "port": 1521,
  "servicename": "fully.qualified.dsn",
  "encoding": "UTF-8"
}

Configuration Schema

Parameter	Type	Required	Description
user	String	Yes	Username for authentication
password	String	Yes	Password for authentication
host	String	Yes	Oracle server hostname or IP address (use _useservicename to use DSN directly)
port	Number	Yes	Oracle server port (default: 1521)
servicename	String	Yes	Oracle service name or SID
encoding	String	No	Character encoding (e.g., "UTF-8")

Example Usage

from legion_query_runner.query_runner import QueryRunner

# Initialize with Oracle configuration
oracle_config = {
    "user": "system",
    "password": "oracle",
    "host": "oracle.example.com",
    "port": 1521,
    "servicename": "ORCL"
}

# Create a QueryRunner instance
runner = QueryRunner('oracle', oracle_config)

# Test connection
runner.test_connection()

# Execute a query
results = runner.run_query("SELECT * FROM employees WHERE rownum <= 10")

Type Mapping

The Oracle query runner maps Oracle data types to standardized types:

Oracle Type	Legion Type
NUMBER (scale=0)	INTEGER
NUMBER (scale>0)	FLOAT
DATETIME	DATETIME
TIMESTAMP	DATETIME
INTERVAL	DATETIME
CLOB	STRING
LOB	STRING
STRING, NCHAR, FIXED_CHAR, FIXED_NCHAR	STRING
LONG_STRING	STRING
NATIVE_FLOAT	FLOAT
ROWID	INTEGER

Query Limits

The Oracle query runner has built-in support for limiting query results using the FETCH NEXT syntax:

-- This will be automatically limited to 1000 rows
SELECT * FROM large_table

-- This will use the specified limit
SELECT * FROM large_table FETCH NEXT 50 ROWS ONLY

Schema Retrieval

The Oracle query runner extracts schema information from Oracle data dictionary views:

# Get schema for all tables
schema = runner.get_schema()

# Get columns for a specific table
columns = runner.get_table_columns("employees")

# Get column types for a specific table
column_types = runner.get_table_types("employees")

Implementation Details

The Oracle query runner uses the oracledb (formerly cx_Oracle) library to connect to Oracle databases. This library must be installed for the query runner to be enabled.

To install the required dependency:

pip install oracledb

The query runner also includes support for:

• Custom output handling for LOB and CLOB types
• Custom number conversion to handle Oracle NUMBER types appropriately
• Connection through service names or direct host/port
• NLS language settings through the encoding parameter
• Proper error reporting with line number and position for failed queries

Troubleshooting

If you encounter connection issues:

1. Verify that the Oracle client libraries are properly installed
2. Check that the service name is correct
3. Ensure the user has appropriate permissions
4. For encoding issues, set the correct value in the encoding parameter