0
# cx_Oracle
1
2
A mature Python extension module providing comprehensive access to Oracle Database. cx_Oracle implements the Python Database API 2.0 specification with extensive Oracle-specific extensions, enabling Python applications to connect to and interact with Oracle databases (versions 11.2 through 21c) with high performance and advanced Oracle features.
3
4
## Package Information
5
6
- **Package Name**: cx_Oracle
7
- **Language**: Python (with C extensions)
8
- **Installation**: `pip install cx_Oracle`
9
- **Requirements**: Oracle client libraries (Oracle Instant Client, Oracle Database, or Oracle Full Client)
10
11
## Core Imports
12
13
```python
14
import cx_Oracle
15
```
16
17
For aliasing (common pattern):
18
19
```python
20
import cx_Oracle as oracledb
21
```
22
23
## Basic Usage
24
25
```python
26
import cx_Oracle
27
28
# Create connection
29
connection = cx_Oracle.connect("username", "password", "hostname:port/servicename")
30
31
# Alternative DSN format
32
dsn = cx_Oracle.makedsn("hostname", 1521, service_name="servicename")
33
connection = cx_Oracle.connect("username", "password", dsn)
34
35
# Execute query
36
cursor = connection.cursor()
37
cursor.execute("SELECT employee_id, first_name, last_name FROM employees WHERE department_id = :dept_id", {"dept_id": 10})
38
39
# Fetch results
40
for row in cursor:
41
print(f"ID: {row[0]}, Name: {row[1]} {row[2]}")
42
43
# Execute DML operations
44
cursor.execute("INSERT INTO employees (employee_id, first_name, last_name) VALUES (:1, :2, :3)", (1001, "John", "Doe"))
45
connection.commit()
46
47
# Close resources
48
cursor.close()
49
connection.close()
50
```
51
52
## Architecture
53
54
cx_Oracle is built around the Database API 2.0 specification with Oracle-specific enhancements:
55
56
- **Connection Management**: Direct connections and session pooling for scalable applications
57
- **Cursor Operations**: SQL execution, parameter binding, and result fetching with advanced Oracle features
58
- **Data Type Support**: Complete Oracle data type mapping including LOBs, objects, collections, and JSON
59
- **Advanced Features**: SODA for document operations, Advanced Queueing, continuous query notifications
60
- **Performance**: C implementation with features like array processing, statement caching, and connection pooling
61
62
## Capabilities
63
64
### Database Connections
65
66
Core connection management including direct connections, connection strings, authentication modes, and connection properties for Oracle Database access.
67
68
```python { .api }
69
def connect(user=None, password=None, dsn=None, **kwargs) -> Connection: ...
70
def makedsn(host, port, sid=None, service_name=None, region=None,
71
sharding_key=None, super_sharding_key=None, **kwargs) -> str: ...
72
def clientversion() -> tuple: ...
73
def init_oracle_client(lib_dir=None, config_dir=None, error_url=None,
74
driver_name=None) -> None: ...
75
def DateFromTicks(ticks: float) -> Date: ...
76
def TimestampFromTicks(ticks: float) -> Timestamp: ...
77
def Time(hour: int, minute: int, second: int): ... # Raises NotSupportedError
78
def TimeFromTicks(ticks: float): ... # Raises NotSupportedError
79
```
80
81
[Database Connections](./connections.md)
82
83
### SQL Execution and Cursors
84
85
SQL statement execution, parameter binding, result fetching, and cursor management with support for all Oracle SQL features and PL/SQL operations.
86
87
```python { .api }
88
class Cursor:
89
def execute(self, sql: str, parameters=None) -> Cursor: ...
90
def executemany(self, sql: str, seq_of_parameters) -> None: ...
91
def fetchone(self) -> tuple: ...
92
def fetchmany(self, numRows=None) -> list: ...
93
def fetchall(self) -> list: ...
94
def callfunc(self, name: str, returnType, parameters=None): ...
95
def callproc(self, name: str, parameters=None) -> list: ...
96
```
97
98
[SQL Execution and Cursors](./cursors.md)
99
100
### Session Pooling
101
102
Connection pooling for scalable applications with configurable pool sizes, connection sharing, and automatic connection management.
103
104
```python { .api }
105
class SessionPool:
106
def __init__(self, user: str, password: str, dsn: str, min: int, max: int, increment: int, **kwargs): ...
107
def acquire(self) -> Connection: ...
108
def release(self, connection: Connection) -> None: ...
109
def close(self, force=False) -> None: ...
110
```
111
112
[Session Pooling](./session-pools.md)
113
114
### Large Objects (LOBs)
115
116
Handling of CLOB, BLOB, NCLOB, and BFILE objects with streaming read/write operations, temporary LOB creation, and file-based LOB operations.
117
118
```python { .api }
119
class LOB:
120
def read(self, offset=1, amount=None) -> Union[str, bytes]: ...
121
def write(self, data: Union[str, bytes], offset=1) -> int: ...
122
def size(self) -> int: ...
123
def trim(self, newSize=0) -> None: ...
124
def getvalue(self) -> Union[str, bytes]: ...
125
```
126
127
[Large Objects (LOBs)](./lobs.md)
128
129
### SODA (Simple Oracle Document Access)
130
131
Document-oriented database operations for JSON documents with collection management, document operations, and query capabilities.
132
133
```python { .api }
134
class SodaDatabase:
135
def createCollection(self, name: str, metadata=None) -> SodaCollection: ...
136
def openCollection(self, name: str) -> SodaCollection: ...
137
def getCollectionNames(self, startName=None, limit=None) -> list: ...
138
139
class SodaCollection:
140
def insertOne(self, doc) -> SodaDoc: ...
141
def find(self) -> SodaOperation: ...
142
def createIndex(self, spec: dict) -> None: ...
143
```
144
145
[SODA (Simple Oracle Document Access)](./soda.md)
146
147
### Advanced Queueing (AQ)
148
149
Oracle Advanced Queueing for message-oriented middleware with message enqueueing, dequeueing, and queue management operations.
150
151
```python { .api }
152
class Queue:
153
def enqOne(self, msgProperties: MessageProperties) -> None: ...
154
def deqOne(self) -> MessageProperties: ...
155
def enqMany(self, msgPropertiesList: list) -> None: ...
156
def deqMany(self, maxMessages: int) -> list: ...
157
```
158
159
[Advanced Queueing (AQ)](./advanced-queueing.md)
160
161
### Oracle Object Types
162
163
Support for Oracle user-defined types including object types, collections (VARRAYs and nested tables), and object attribute access.
164
165
```python { .api }
166
class Object:
167
def copy(self) -> Object: ...
168
def aslist(self) -> list: ...
169
def asdict(self) -> dict: ...
170
def append(self, value) -> None: ...
171
def extend(self, sequence) -> None: ...
172
173
class ObjectType:
174
def newobject(self) -> Object: ...
175
@property
176
def attributes(self) -> list: ...
177
```
178
179
[Oracle Object Types](./object-types.md)
180
181
### Database Change Notifications
182
183
Continuous Query Notification (CQN) and Database Change Notification for real-time monitoring of database changes.
184
185
```python { .api }
186
class Subscription:
187
def __init__(self, connection: Connection, callback, **kwargs): ...
188
@property
189
def callback(self): ...
190
@property
191
def operations(self): ...
192
```
193
194
[Database Change Notifications](./notifications.md)
195
196
## Exception Handling
197
198
cx_Oracle provides a comprehensive exception hierarchy based on the Database API 2.0 specification:
199
200
```python { .api }
201
class Error(Exception): ...
202
class Warning(Exception): ...
203
204
# Database API standard exceptions
205
class InterfaceError(Error): ...
206
class DatabaseError(Error): ...
207
class DataError(DatabaseError): ...
208
class OperationalError(DatabaseError): ...
209
class IntegrityError(DatabaseError): ...
210
class InternalError(DatabaseError): ...
211
class ProgrammingError(DatabaseError): ...
212
class NotSupportedError(DatabaseError): ...
213
```
214
215
All exceptions provide detailed error information including Oracle error codes and messages.
216
217
## Database API 2.0 Compliance
218
219
cx_Oracle fully implements the Python Database API 2.0 specification:
220
221
- **API Level**: 2.0
222
- **Thread Safety**: 2 (Threads may share connections)
223
- **Parameter Style**: named (e.g., `:param_name`)
224
225
## Constants and Types
226
227
### Module Information
228
229
```python { .api }
230
# Module metadata
231
__version__: str # Module version string
232
version: str # Module version string (alias)
233
buildtime: str # Build timestamp
234
apilevel: str # DB API level ("2.0")
235
threadsafety: int # Thread safety level (2)
236
paramstyle: str # Parameter style ("named")
237
```
238
239
### Data Types
240
241
Oracle database types are available as module constants:
242
243
```python { .api }
244
# API types (DB API 2.0)
245
STRING: ApiType
246
BINARY: ApiType
247
NUMBER: ApiType
248
DATETIME: ApiType
249
ROWID: ApiType
250
251
# Oracle-specific database types (preferred names)
252
DB_TYPE_BFILE: DbType
253
DB_TYPE_BINARY_DOUBLE: DbType
254
DB_TYPE_BINARY_FLOAT: DbType
255
DB_TYPE_BINARY_INTEGER: DbType
256
DB_TYPE_BLOB: DbType
257
DB_TYPE_BOOLEAN: DbType
258
DB_TYPE_CHAR: DbType
259
DB_TYPE_CLOB: DbType
260
DB_TYPE_CURSOR: DbType
261
DB_TYPE_DATE: DbType
262
DB_TYPE_INTERVAL_DS: DbType
263
DB_TYPE_INTERVAL_YM: DbType
264
DB_TYPE_JSON: DbType
265
DB_TYPE_LONG: DbType
266
DB_TYPE_LONG_RAW: DbType
267
DB_TYPE_NCHAR: DbType
268
DB_TYPE_NCLOB: DbType
269
DB_TYPE_NUMBER: DbType
270
DB_TYPE_NVARCHAR: DbType
271
DB_TYPE_OBJECT: DbType
272
DB_TYPE_RAW: DbType
273
DB_TYPE_ROWID: DbType
274
DB_TYPE_TIMESTAMP: DbType
275
DB_TYPE_TIMESTAMP_LTZ: DbType
276
DB_TYPE_TIMESTAMP_TZ: DbType
277
DB_TYPE_VARCHAR: DbType
278
279
# Deprecated synonyms (for compatibility)
280
BFILE: DbType # Use DB_TYPE_BFILE
281
BLOB: DbType # Use DB_TYPE_BLOB
282
CLOB: DbType # Use DB_TYPE_CLOB
283
CURSOR: DbType # Use DB_TYPE_CURSOR
284
FIXED_CHAR: DbType # Use DB_TYPE_CHAR
285
NCHAR: DbType # Use DB_TYPE_NCHAR
286
NCLOB: DbType # Use DB_TYPE_NCLOB
287
TIMESTAMP: DbType # Use DB_TYPE_TIMESTAMP
288
```
289
290
### Authentication and Connection Modes
291
292
```python { .api }
293
# Authentication modes
294
DEFAULT_AUTH: int # Default authentication
295
SYSDBA: int # SYSDBA administrative privilege
296
SYSOPER: int # SYSOPER administrative privilege
297
SYSASM: int # SYSASM administrative privilege
298
SYSBKP: int # SYSBKP administrative privilege
299
SYSDGD: int # SYSDGD administrative privilege
300
SYSKMT: int # SYSKMT administrative privilege
301
SYSRAC: int # SYSRAC administrative privilege
302
PRELIM_AUTH: int # Preliminary authentication for startup/shutdown
303
304
# Session pool modes
305
SPOOL_ATTRVAL_WAIT: int # Wait for connection
306
SPOOL_ATTRVAL_NOWAIT: int # Don't wait for connection
307
SPOOL_ATTRVAL_FORCEGET: int # Force get connection
308
SPOOL_ATTRVAL_TIMEDWAIT: int # Timed wait for connection
309
310
# Purity levels
311
ATTR_PURITY_DEFAULT: int # Default purity
312
ATTR_PURITY_NEW: int # New connection purity
313
ATTR_PURITY_SELF: int # Self purity
314
315
# Database shutdown modes
316
DBSHUTDOWN_ABORT: int # Abort shutdown
317
DBSHUTDOWN_FINAL: int # Final shutdown
318
DBSHUTDOWN_IMMEDIATE: int # Immediate shutdown
319
DBSHUTDOWN_TRANSACTIONAL: int # Transactional shutdown
320
DBSHUTDOWN_TRANSACTIONAL_LOCAL: int # Local transactional shutdown
321
```
322
323
### Subscription and Event Constants
324
325
```python { .api }
326
# Subscription protocols
327
SUBSCR_PROTO_OCI: int # OCI protocol
328
SUBSCR_PROTO_MAIL: int # Mail protocol
329
SUBSCR_PROTO_SERVER: int # Server protocol
330
SUBSCR_PROTO_HTTP: int # HTTP protocol
331
332
# Quality of service flags
333
SUBSCR_QOS_RELIABLE: int # Reliable messaging
334
SUBSCR_QOS_DEREG_NFY: int # Deregistration notification
335
SUBSCR_QOS_ROWIDS: int # Include ROWIDs
336
SUBSCR_QOS_QUERY: int # Query-level notification
337
SUBSCR_QOS_BEST_EFFORT: int # Best effort delivery
338
SUBSCR_QOS_PURGE_ON_NTFN: int # Purge on notification
339
340
# Event types
341
EVENT_NONE: int # No event
342
EVENT_STARTUP: int # Database startup
343
EVENT_SHUTDOWN: int # Database shutdown
344
EVENT_SHUTDOWN_ANY: int # Any shutdown
345
EVENT_DROP_DB: int # Database drop
346
EVENT_DEREG: int # Deregistration
347
EVENT_OBJCHANGE: int # Object change
348
EVENT_QUERYCHANGE: int # Query change
349
350
# Operation codes
351
OPCODE_ALLOPS: int # All operations
352
OPCODE_ALLROWS: int # All rows
353
OPCODE_INSERT: int # Insert operation
354
OPCODE_UPDATE: int # Update operation
355
OPCODE_DELETE: int # Delete operation
356
OPCODE_ALTER: int # Alter operation
357
OPCODE_DROP: int # Drop operation
358
OPCODE_UNKNOWN: int # Unknown operation
359
```
360
361
### Advanced Queueing Constants
362
363
```python { .api }
364
# Dequeue options
365
DEQ_BROWSE: int # Browse mode
366
DEQ_LOCKED: int # Locked mode
367
DEQ_REMOVE: int # Remove mode
368
DEQ_REMOVE_NODATA: int # Remove without data
369
370
# Enqueue/Dequeue navigation
371
DEQ_FIRST_MSG: int # First message
372
DEQ_NEXT_MSG: int # Next message
373
DEQ_NEXT_TRANSACTION: int # Next transaction
374
375
# Message delivery modes
376
MSG_PERSISTENT: int # Persistent message
377
MSG_BUFFERED: int # Buffered message
378
MSG_PERSISTENT_OR_BUFFERED: int # Either mode
379
380
# Message states
381
MSG_WAITING: int # Waiting state
382
MSG_READY: int # Ready state
383
MSG_PROCESSED: int # Processed state
384
MSG_EXPIRED: int # Expired state
385
```
386
387
See individual capability documents for complete constant definitions and usage examples.