0
# Database Connections
1

2
Core connection management for Oracle Database access including direct connections, connection strings, authentication modes, and connection properties.
3

4
## Capabilities
5

6
### Creating Connections
7

8
Connect to Oracle Database using various connection methods and authentication modes.
9

10
```python { .api }
11
def connect(user=None, password=None, dsn=None, mode=DEFAULT_AUTH, handle=0, 
12
           pool=None, params=None, threaded=False, events=False, 
13
           purity=ATTR_PURITY_DEFAULT, newpassword=None, encoding=None, 
14
           nencoding=None, edition=None, appcontext=[], tag=None, 
15
           matchanytag=False, config_dir=None, driver_name=None, 
16
           shardingkey=[], supershardingkey=[], debug_jdwp=None, 
17
           connection_id_prefix=None, ssl_context=None, sdu=None, 
18
           pool_boundary=None, use_tcp_fast_open=None, ssl_version=None, 
19
           wallet_location=None, wallet_password=None, access_token=None, 
20
           external_handle=None, mode_from_py=None) -> Connection:
21
    """
22
    Create connection to Oracle Database.
23
    
24
    Parameters:
25
    - user (str): Username
26
    - password (str): Password  
27
    - dsn (str): Data source name/connect string
28
    - mode (int): Authentication mode (DEFAULT_AUTH, SYSDBA, SYSOPER, etc.)
29
    - threaded (bool): Enable thread safety
30
    - events (bool): Enable Oracle events
31
    - encoding (str): Character encoding (default: UTF-8)
32
    - nencoding (str): National character encoding
33
    - edition (str): Database edition
34
    - tag (str): Connection tag for pooling
35
    - config_dir (str): Oracle client configuration directory
36
    - ssl_context: SSL context for encrypted connections
37
    
38
    Returns:
39
    Connection object
40
    """
41
```
42

43
Usage examples:
44

45
```python
46
# Basic connection
47
conn = cx_Oracle.connect("scott", "tiger", "localhost:1521/XE")
48

49
# Connection with specific encoding
50
conn = cx_Oracle.connect("user", "pass", "dsn", encoding="UTF-8")
51

52
# Administrative connection
53
conn = cx_Oracle.connect("sys", "password", "dsn", mode=cx_Oracle.SYSDBA)
54

55
# Connection with SSL
56
import ssl
57
ssl_context = ssl.create_default_context()
58
conn = cx_Oracle.connect("user", "pass", "dsn", ssl_context=ssl_context)
59
```
60

61
### DSN Creation
62

63
Create properly formatted Data Source Names for Oracle connections.
64

65
```python { .api }
66
def makedsn(host: str, port: int, sid=None, service_name=None, region=None, 
67
           sharding_key=None, super_sharding_key=None) -> str:
68
    """
69
    Create Oracle DSN from connection components.
70
    
71
    Parameters:
72
    - host (str): Database server hostname or IP
73
    - port (int): Database server port (typically 1521)
74
    - sid (str): Oracle SID (deprecated, use service_name)
75
    - service_name (str): Oracle service name
76
    - region (str): Oracle Cloud region
77
    - sharding_key (list): Sharding key for sharded databases
78
    - super_sharding_key (list): Super sharding key
79
    
80
    Returns:
81
    Formatted DSN string
82
    """
83
```
84

85
Usage examples:
86

87
```python
88
# Service name (recommended)
89
dsn = cx_Oracle.makedsn("myhost", 1521, service_name="XEPDB1")
90

91
# Legacy SID format
92
dsn = cx_Oracle.makedsn("myhost", 1521, sid="XE")
93

94
# With sharding (Oracle 12.2+)
95
dsn = cx_Oracle.makedsn("myhost", 1521, service_name="service", 
96
                       sharding_key=[100], super_sharding_key=["region1"])
97
```
98

99
### Client Library Management
100

101
Initialize and manage Oracle client library configuration.
102

103
```python { .api }
104
def init_oracle_client(lib_dir=None, config_dir=None, error_url=None, 
105
                      driver_name=None) -> None:
106
    """
107
    Initialize Oracle client library with custom configuration.
108
    
109
    Parameters:
110
    - lib_dir (str): Oracle client library directory path
111
    - config_dir (str): Oracle client configuration directory  
112
    - error_url (str): URL for additional error information
113
    - driver_name (str): Custom driver name
114
    
115
    Raises:
116
    InterfaceError: If initialization fails
117
    """
118

119
def clientversion() -> tuple:
120
    """
121
    Get Oracle client library version information.
122
    
123
    Returns:
124
    5-tuple: (version, release, update, port_release, port_update)
125
    """
126
```
127

128
Usage examples:
129

130
```python
131
# Initialize with custom library path
132
cx_Oracle.init_oracle_client(lib_dir="/opt/oracle/instantclient_19_8")
133

134
# Get client version
135
version = cx_Oracle.clientversion()
136
print(f"Client version: {version[0]}.{version[1]}.{version[2]}")
137
```
138

139
## Connection Class
140

141
Represents an active connection to Oracle Database with methods for transaction control and resource management.
142

143
### Connection Properties
144

145
```python { .api }
146
class Connection:
147
    @property
148
    def autocommit(self) -> bool:
149
        """Auto-commit mode flag"""
150
        
151
    @property  
152
    def username(self) -> str:
153
        """Connected username"""
154
        
155
    @property
156
    def dsn(self) -> str:
157
        """Data source name"""
158
        
159
    @property
160
    def tnsentry(self) -> str:
161
        """TNS entry (alias for dsn)"""
162
        
163
    @property
164
    def version(self) -> str:
165
        """Database version string"""
166
        
167
    @property
168
    def encoding(self) -> str:
169
        """Character encoding"""
170
        
171
    @property
172
    def nencoding(self) -> str:
173
        """National character encoding"""
174
        
175
    @property
176
    def maxBytesPerCharacter(self) -> int:
177
        """Maximum bytes per character"""
178
        
179
    @property
180
    def current_schema(self) -> str:
181
        """Current schema name"""
182
        
183
    @property
184
    def edition(self) -> str:
185
        """Database edition"""
186
        
187
    @property
188
    def ltxid(self) -> bytes:
189
        """Logical transaction ID"""
190
        
191
    @property
192
    def stmtcachesize(self) -> int:
193
        """Statement cache size"""
194
        
195
    @property
196
    def tag(self) -> str:
197
        """Connection tag"""
198
        
199
    @property
200
    def call_timeout(self) -> int:
201
        """Call timeout in milliseconds"""
202
        
203
    @property
204
    def client_identifier(self) -> str:
205
        """Client identifier for monitoring"""
206
        
207
    @property
208
    def clientinfo(self) -> str:
209
        """Client info string"""
210
        
211
    @property
212
    def module(self) -> str:
213
        """Module name for monitoring"""
214
        
215
    @property
216
    def action(self) -> str:
217
        """Action name for monitoring"""
218
        
219
    @property
220
    def dbop(self) -> str:
221
        """Database operation name"""
222
        
223
    @property
224
    def inputtypehandler(self):
225
        """Input type handler function"""
226
        
227
    @property
228
    def outputtypehandler(self):
229
        """Output type handler function"""
230
```
231

232
### Connection Methods
233

234
```python { .api }
235
class Connection:
236
    def cursor(self) -> Cursor:
237
        """Create new cursor for executing SQL statements"""
238
        
239
    def commit(self) -> None:
240
        """Commit current transaction"""
241
        
242
    def rollback(self) -> None:
243
        """Rollback current transaction"""
244
        
245
    def begin(self, formatId=None, transactionId=None, branchId=None) -> None:
246
        """Begin new transaction (optionally distributed transaction)"""
247
        
248
    def prepare(self) -> bool:
249
        """Prepare transaction for two-phase commit"""
250
        
251
    def close(self) -> None:
252
        """Close connection and free resources"""
253
        
254
    def ping(self) -> None:
255
        """Test connection liveness"""
256
        
257
    def cancel(self) -> None:
258
        """Cancel currently executing operation"""
259
        
260
    def changepassword(self, oldpassword: str, newpassword: str) -> None:
261
        """Change user password"""
262
        
263
    def createlob(self, lobtype) -> LOB:
264
        """Create temporary LOB object"""
265
        
266
    def gettype(self, name: str) -> ObjectType:
267
        """Get Oracle object type by name"""
268
        
269
    def getSodaDatabase(self) -> SodaDatabase:
270
        """Get SODA database object for document operations"""
271
        
272
    def deqoptions(self) -> DeqOptions:
273
        """Create dequeue options object"""
274
        
275
    def enqoptions(self) -> EnqOptions:
276
        """Create enqueue options object"""
277
        
278
    def msgproperties(self, **kwargs) -> MessageProperties:
279
        """Create message properties object"""
280
        
281
    def deq(self, **kwargs) -> MessageProperties:
282
        """Dequeue message"""
283
        
284
    def enq(self, **kwargs) -> None:
285
        """Enqueue message"""
286
        
287
    def queue(self, name: str, **kwargs) -> Queue:
288
        """Get or create queue object"""
289
        
290
    def shutdown(self, mode=None, **kwargs) -> None:
291
        """Shutdown database (requires DBA privileges)"""
292
        
293
    def startup(self, force=False, restrict=False, pfile=None, **kwargs) -> None:
294
        """Startup database (requires DBA privileges)"""
295
        
296
    def __enter__(self) -> Connection:
297
        """Context manager entry"""
298
        
299
    def __exit__(self, exc_type, exc_val, exc_tb) -> None:
300
        """Context manager exit with automatic cleanup"""
301
```
302

303
### Transaction Management
304

305
```python { .api }
306
class Connection:
307
    def tpc_begin(self, xid) -> None:
308
        """Begin distributed transaction"""
309
        
310
    def tpc_prepare(self) -> None:
311
        """Prepare distributed transaction"""
312
        
313
    def tpc_commit(self, xid=None) -> None:
314
        """Commit distributed transaction"""
315
        
316
    def tpc_rollback(self, xid=None) -> None:
317
        """Rollback distributed transaction"""
318
        
319
    def tpc_recover(self) -> list:
320
        """Get list of pending distributed transactions"""
321
```
322

323
### Subscription Management
324

325
```python { .api }
326
class Connection:
327
    def subscribe(self, callback, sql=None, operations=None, qos=None, 
328
                 timeout=0, **kwargs) -> Subscription:
329
        """
330
        Create subscription for database change notifications.
331
        
332
        Parameters:
333
        - callback: Function to call when events occur
334
        - sql (str): SQL statement to monitor
335
        - operations (int): Operations to monitor (OPCODE_* constants)
336
        - qos (int): Quality of service flags
337
        - timeout (int): Subscription timeout in seconds
338
        
339
        Returns:
340
        Subscription object
341
        """
342
        
343
    def unsubscribe(self, subscription: Subscription) -> None:
344
        """Remove database change notification subscription"""
345
```
346

347
## Authentication Modes
348

349
```python { .api }
350
DEFAULT_AUTH: int       # Default authentication
351
SYSASM: int            # SYSASM administrative privilege  
352
SYSBKP: int            # SYSBKP administrative privilege
353
SYSDBA: int            # SYSDBA administrative privilege
354
SYSDGD: int            # SYSDGD administrative privilege  
355
SYSKMT: int            # SYSKMT administrative privilege
356
SYSOPER: int           # SYSOPER administrative privilege
357
SYSRAC: int            # SYSRAC administrative privilege
358
PRELIM_AUTH: int       # Preliminary authentication for startup/shutdown
359
```
360

361
## Connection Context Management
362

363
cx_Oracle connections support Python's context manager protocol:
364

365
```python
366
# Automatic connection cleanup
367
with cx_Oracle.connect("user", "pass", "dsn") as conn:
368
    cursor = conn.cursor()
369
    cursor.execute("SELECT * FROM employees")
370
    # Connection automatically closed when exiting context
371
```
372

373
## Error Handling
374

375
Connection-related errors raise specific exception types:
376

377
- `InterfaceError`: Connection interface problems
378
- `DatabaseError`: Database connection issues  
379
- `OperationalError`: Database operational problems
380

381
```python
382
try:
383
    conn = cx_Oracle.connect("user", "wrongpass", "dsn")
384
except cx_Oracle.DatabaseError as e:
385
    error_obj, = e.args
386
    print(f"Oracle error {error_obj.code}: {error_obj.message}")
387
```