0
# Data Types and Constants
1
2
Comprehensive support for all Oracle data types, authentication modes, and configuration constants. Oracle python-oracledb provides extensive type system coverage enabling seamless data exchange between Python and Oracle Database, including support for modern data types like JSON and vectors.
3
4
## Capabilities
5
6
### Database Type Constants
7
8
Constants representing all Oracle database types for use in variable declarations, type checking, and data conversion.
9
10
```python { .api }
11
# Character and String Types
12
DB_TYPE_CHAR: type # Fixed-length character data
13
DB_TYPE_VARCHAR: type # Variable-length character data
14
DB_TYPE_NCHAR: type # Fixed-length national character data
15
DB_TYPE_NVARCHAR: type # Variable-length national character data
16
DB_TYPE_LONG: type # Long character data (deprecated)
17
DB_TYPE_LONG_NVARCHAR: type # Long national character data
18
19
# Numeric Types
20
DB_TYPE_NUMBER: type # Oracle NUMBER type
21
DB_TYPE_BINARY_INTEGER: type # Binary integer
22
DB_TYPE_BINARY_FLOAT: type # Binary float (32-bit)
23
DB_TYPE_BINARY_DOUBLE: type # Binary double (64-bit)
24
25
# Date and Time Types
26
DB_TYPE_DATE: type # Date and time
27
DB_TYPE_TIMESTAMP: type # Timestamp
28
DB_TYPE_TIMESTAMP_TZ: type # Timestamp with timezone
29
DB_TYPE_TIMESTAMP_LTZ: type # Timestamp with local timezone
30
DB_TYPE_INTERVAL_DS: type # Interval day to second
31
DB_TYPE_INTERVAL_YM: type # Interval year to month
32
33
# Large Object Types
34
DB_TYPE_BLOB: type # Binary Large Object
35
DB_TYPE_CLOB: type # Character Large Object
36
DB_TYPE_NCLOB: type # National Character Large Object
37
DB_TYPE_BFILE: type # Binary File (external)
38
39
# Other Types
40
DB_TYPE_RAW: type # Raw binary data
41
DB_TYPE_LONG_RAW: type # Long raw binary data
42
DB_TYPE_ROWID: type # Physical row identifier
43
DB_TYPE_UROWID: type # Universal row identifier
44
DB_TYPE_CURSOR: type # Cursor/REF CURSOR
45
DB_TYPE_OBJECT: type # User-defined object type
46
DB_TYPE_BOOLEAN: type # Boolean (Oracle 12c+)
47
DB_TYPE_JSON: type # JSON data type (Oracle 21c+)
48
DB_TYPE_VECTOR: type # Vector data type (Oracle 23c+)
49
DB_TYPE_XMLTYPE: type # XMLType
50
DB_TYPE_UNKNOWN: type # Unknown type
51
52
# Legacy Type Aliases (for backward compatibility)
53
BFILE: type # Alias for DB_TYPE_BFILE
54
BLOB: type # Alias for DB_TYPE_BLOB
55
BOOLEAN: type # Alias for DB_TYPE_BOOLEAN
56
CLOB: type # Alias for DB_TYPE_CLOB
57
CURSOR: type # Alias for DB_TYPE_CURSOR
58
FIXED_CHAR: type # Alias for DB_TYPE_CHAR
59
FIXED_NCHAR: type # Alias for DB_TYPE_NCHAR
60
INTERVAL: type # Alias for DB_TYPE_INTERVAL_DS
61
LONG_BINARY: type # Alias for DB_TYPE_LONG_RAW
62
LONG_STRING: type # Alias for DB_TYPE_LONG
63
NATIVE_INT: type # Alias for DB_TYPE_BINARY_INTEGER
64
NATIVE_FLOAT: type # Alias for DB_TYPE_BINARY_DOUBLE
65
NCHAR: type # Alias for DB_TYPE_NVARCHAR
66
NCLOB: type # Alias for DB_TYPE_NCLOB
67
OBJECT: type # Alias for DB_TYPE_OBJECT
68
TIMESTAMP: type # Alias for DB_TYPE_TIMESTAMP
69
```
70
71
### API Type Constants
72
73
Python DB API 2.0 standard type constants for portable database programming.
74
75
```python { .api }
76
# Standard DB API Types
77
STRING: type # String data type
78
BINARY: type # Binary data type
79
NUMBER: type # Numeric data type
80
DATETIME: type # Date/time data type
81
ROWID: type # Row identifier type
82
```
83
84
### Authentication Mode Constants
85
86
Constants for specifying database authentication modes and privileges.
87
88
```python { .api }
89
# Authentication Modes
90
AUTH_MODE_DEFAULT: int # Default authentication
91
AUTH_MODE_SYSDBA: int # SYSDBA privilege
92
AUTH_MODE_SYSOPER: int # SYSOPER privilege
93
AUTH_MODE_SYSASM: int # SYSASM privilege (ASM)
94
AUTH_MODE_SYSBKP: int # SYSBKP privilege (backup)
95
AUTH_MODE_SYSDGD: int # SYSDGD privilege (Data Guard)
96
AUTH_MODE_SYSKMT: int # SYSKMT privilege (Key Management)
97
AUTH_MODE_SYSRAC: int # SYSRAC privilege (RAC)
98
AUTH_MODE_PRELIM: int # Preliminary authentication
99
100
# Legacy Authentication Aliases
101
DEFAULT_AUTH: int # Alias for AUTH_MODE_DEFAULT
102
SYSDBA: int # Alias for AUTH_MODE_SYSDBA
103
SYSOPER: int # Alias for AUTH_MODE_SYSOPER
104
SYSASM: int # Alias for AUTH_MODE_SYSASM
105
SYSBKP: int # Alias for AUTH_MODE_SYSBKP
106
SYSDGD: int # Alias for AUTH_MODE_SYSDGD
107
SYSKMT: int # Alias for AUTH_MODE_SYSKMT
108
SYSRAC: int # Alias for AUTH_MODE_SYSRAC
109
PRELIM_AUTH: int # Alias for AUTH_MODE_PRELIM
110
```
111
112
### Connection Pool Constants
113
114
Constants for configuring connection pool behavior and connection acquisition modes.
115
116
```python { .api }
117
# Pool Get Modes
118
POOL_GETMODE_WAIT: int # Wait for available connection
119
POOL_GETMODE_NOWAIT: int # Return immediately if no connection
120
POOL_GETMODE_FORCEGET: int # Create connection beyond max limit
121
POOL_GETMODE_TIMEDWAIT: int # Wait with timeout
122
123
# Pool Purity Levels
124
PURITY_DEFAULT: int # Default purity
125
PURITY_NEW: int # New session required
126
PURITY_SELF: int # Self-contained session
127
128
# Legacy Pool Aliases
129
SPOOL_ATTRVAL_WAIT: int # Alias for POOL_GETMODE_WAIT
130
SPOOL_ATTRVAL_NOWAIT: int # Alias for POOL_GETMODE_NOWAIT
131
SPOOL_ATTRVAL_FORCEGET: int # Alias for POOL_GETMODE_FORCEGET
132
SPOOL_ATTRVAL_TIMEDWAIT: int # Alias for POOL_GETMODE_TIMEDWAIT
133
ATTR_PURITY_DEFAULT: int # Alias for PURITY_DEFAULT
134
ATTR_PURITY_NEW: int # Alias for PURITY_NEW
135
ATTR_PURITY_SELF: int # Alias for PURITY_SELF
136
```
137
138
### Advanced Queuing (AQ) Constants
139
140
Constants for Oracle Advanced Queuing operations including message delivery, dequeue modes, and queue management.
141
142
```python { .api }
143
# Message Delivery Modes
144
MSG_BUFFERED: int # Buffered messages
145
MSG_PERSISTENT: int # Persistent messages
146
MSG_PERSISTENT_OR_BUFFERED: int # Persistent or buffered
147
148
# Dequeue Modes
149
DEQ_BROWSE: int # Browse without removing
150
DEQ_LOCKED: int # Lock message
151
DEQ_REMOVE: int # Remove message
152
DEQ_REMOVE_NODATA: int # Remove without returning data
153
154
# Dequeue Navigation
155
DEQ_FIRST_MSG: int # First message
156
DEQ_NEXT_MSG: int # Next message
157
DEQ_NEXT_TRANSACTION: int # Next transaction
158
159
# Visibility Modes
160
DEQ_IMMEDIATE: int # Immediate visibility
161
DEQ_ON_COMMIT: int # Visible on commit
162
ENQ_IMMEDIATE: int # Immediate enqueue
163
ENQ_ON_COMMIT: int # Enqueue on commit
164
165
# Wait Modes
166
DEQ_NO_WAIT: int # Don't wait
167
DEQ_WAIT_FOREVER: int # Wait indefinitely
168
169
# Message States
170
MSG_EXPIRED: int # Message expired
171
MSG_PROCESSED: int # Message processed
172
MSG_READY: int # Message ready
173
MSG_WAITING: int # Message waiting
174
175
# Message Options
176
MSG_NO_DELAY: int # No delay
177
MSG_NO_EXPIRATION: int # No expiration
178
```
179
180
### Database Administration Constants
181
182
Constants for database administration operations including shutdown modes and operation codes.
183
184
```python { .api }
185
# Database Shutdown Modes
186
DBSHUTDOWN_ABORT: int # Abort shutdown
187
DBSHUTDOWN_FINAL: int # Final shutdown phase
188
DBSHUTDOWN_IMMEDIATE: int # Immediate shutdown
189
DBSHUTDOWN_TRANSACTIONAL: int # Transactional shutdown
190
DBSHUTDOWN_TRANSACTIONAL_LOCAL: int # Local transactional shutdown
191
192
# Database Operation Codes
193
OPCODE_ALLOPS: int # All operations
194
OPCODE_ALLROWS: int # All rows
195
OPCODE_INSERT: int # Insert operations
196
OPCODE_UPDATE: int # Update operations
197
OPCODE_DELETE: int # Delete operations
198
OPCODE_ALTER: int # Alter operations
199
OPCODE_DROP: int # Drop operations
200
201
# Event Types
202
EVENT_NONE: int # No event
203
EVENT_STARTUP: int # Database startup
204
EVENT_SHUTDOWN: int # Database shutdown
205
EVENT_SHUTDOWN_ANY: int # Any shutdown event
206
EVENT_DEREG: int # Deregistration event
207
EVENT_OBJCHANGE: int # Object change event
208
EVENT_QUERYCHANGE: int # Query change event
209
EVENT_AQ: int # Advanced Queuing event
210
```
211
212
### Two-Phase Commit Constants
213
214
Constants for distributed transaction management using two-phase commit protocol.
215
216
```python { .api }
217
# TPC Begin Flags
218
TPC_BEGIN_JOIN: int # Join existing transaction
219
TPC_BEGIN_NEW: int # Start new transaction
220
TPC_BEGIN_PROMOTE: int # Promote to distributed
221
TPC_BEGIN_RESUME: int # Resume suspended transaction
222
223
# TPC End Flags
224
TPC_END_NORMAL: int # Normal end
225
TPC_END_SUSPEND: int # Suspend transaction
226
```
227
228
### Vector Format Constants
229
230
Constants for Oracle 23c vector data type format specifications.
231
232
```python { .api }
233
# Vector Formats
234
VECTOR_FORMAT_BINARY: int # Binary vector format
235
VECTOR_FORMAT_FLOAT32: int # 32-bit float vector format
236
VECTOR_FORMAT_FLOAT64: int # 64-bit float vector format
237
VECTOR_FORMAT_INT8: int # 8-bit integer vector format
238
```
239
240
### DB API Constants
241
242
Python Database API 2.0 mandated constants providing metadata about the driver capabilities.
243
244
```python { .api }
245
# DB API Metadata
246
apilevel: str # API level ("2.0")
247
threadsafety: int # Thread safety level (2)
248
paramstyle: str # Parameter style ("named")
249
```
250
251
## Type Usage Examples
252
253
### Working with Different Data Types
254
255
```python
256
import oracledb
257
from datetime import date, datetime
258
from decimal import Decimal
259
260
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
261
262
# Create table with various data types
263
with connection.cursor() as cursor:
264
cursor.execute("""
265
CREATE TABLE data_types_demo (
266
id NUMBER PRIMARY KEY,
267
text_data VARCHAR2(100),
268
number_data NUMBER(10,2),
269
date_data DATE,
270
timestamp_data TIMESTAMP,
271
boolean_data NUMBER(1) CHECK (boolean_data IN (0,1)),
272
raw_data RAW(50),
273
clob_data CLOB,
274
blob_data BLOB
275
)
276
""")
277
278
# Insert data with proper type handling
279
with connection.cursor() as cursor:
280
cursor.execute("""
281
INSERT INTO data_types_demo (
282
id, text_data, number_data, date_data, timestamp_data,
283
boolean_data, raw_data, clob_data, blob_data
284
) VALUES (
285
:1, :2, :3, :4, :5, :6, :7, :8, :9
286
)
287
""", [
288
1, # NUMBER
289
"Sample text", # VARCHAR2
290
Decimal('123.45'), # NUMBER with precision
291
date(2024, 1, 15), # DATE
292
datetime(2024, 1, 15, 10, 30, 45), # TIMESTAMP
293
1, # Boolean as NUMBER
294
b'\x01\x02\x03\x04', # RAW
295
"Large text content", # CLOB
296
b'Binary data content' # BLOB
297
])
298
299
connection.commit()
300
301
# Query with type introspection
302
with connection.cursor() as cursor:
303
cursor.execute("SELECT * FROM data_types_demo WHERE id = 1")
304
305
# Examine column metadata
306
print("Column Information:")
307
for i, desc in enumerate(cursor.description):
308
print(f" Column {i}: {desc[0]} - Type: {desc[1]}")
309
310
# Fetch and display data
311
row = cursor.fetchone()
312
print(f"\nRetrieved Data:")
313
for i, value in enumerate(row):
314
column_name = cursor.description[i][0]
315
print(f" {column_name}: {value} ({type(value).__name__})")
316
317
connection.close()
318
```
319
320
### Type Constants Usage
321
322
```python
323
import oracledb
324
325
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
326
327
# Using type constants for variable creation
328
with connection.cursor() as cursor:
329
# Create variables with specific types
330
string_var = cursor.var(oracledb.DB_TYPE_VARCHAR, size=100)
331
number_var = cursor.var(oracledb.DB_TYPE_NUMBER)
332
date_var = cursor.var(oracledb.DB_TYPE_DATE)
333
clob_var = cursor.var(oracledb.DB_TYPE_CLOB)
334
335
# Use in stored procedure call
336
cursor.callproc("some_procedure", [string_var, number_var, date_var, clob_var])
337
338
# Retrieve values with proper types
339
result_string = string_var.getvalue()
340
result_number = number_var.getvalue()
341
result_date = date_var.getvalue()
342
result_clob = clob_var.getvalue()
343
344
# Type checking in application logic
345
def process_oracle_value(value, oracle_type):
346
"""Process value based on Oracle type."""
347
348
if oracle_type == oracledb.DB_TYPE_VARCHAR:
349
return str(value) if value is not None else ""
350
elif oracle_type == oracledb.DB_TYPE_NUMBER:
351
return float(value) if value is not None else 0.0
352
elif oracle_type == oracledb.DB_TYPE_DATE:
353
return value.strftime('%Y-%m-%d') if value else None
354
elif oracle_type in (oracledb.DB_TYPE_CLOB, oracledb.DB_TYPE_BLOB):
355
return value.read() if value else None
356
else:
357
return value
358
359
connection.close()
360
```
361
362
### Authentication Modes
363
364
```python
365
import oracledb
366
367
# Connect with different authentication modes
368
try:
369
# Regular connection
370
regular_conn = oracledb.connect(
371
user="hr",
372
password="password",
373
dsn="localhost/xepdb1",
374
mode=oracledb.AUTH_MODE_DEFAULT
375
)
376
print("Regular connection successful")
377
regular_conn.close()
378
379
# SYSDBA connection (requires DBA privileges)
380
dba_conn = oracledb.connect(
381
user="sys",
382
password="system_password",
383
dsn="localhost/xepdb1",
384
mode=oracledb.AUTH_MODE_SYSDBA
385
)
386
print("SYSDBA connection successful")
387
388
# Check connection privileges
389
with dba_conn.cursor() as cursor:
390
cursor.execute("SELECT USER FROM DUAL")
391
user = cursor.fetchone()[0]
392
print(f"Connected as: {user}")
393
394
dba_conn.close()
395
396
except oracledb.DatabaseError as e:
397
print(f"Connection failed: {e}")
398
```
399
400
### Pool Configuration with Constants
401
402
```python
403
import oracledb
404
405
# Create pool with specific configuration
406
pool = oracledb.create_pool(
407
user="hr",
408
password="password",
409
dsn="localhost/xepdb1",
410
min=5,
411
max=20,
412
increment=3,
413
getmode=oracledb.POOL_GETMODE_WAIT, # Wait for available connection
414
homogeneous=True,
415
timeout=300 # 5 minutes
416
)
417
418
# Acquire connection with specific purity
419
connection = pool.acquire(
420
cclass="OLTP",
421
purity=oracledb.PURITY_SELF # Self-contained session
422
)
423
424
print(f"Pool status: {pool.busy}/{pool.opened} connections in use")
425
426
# Use connection
427
with connection.cursor() as cursor:
428
cursor.execute("SELECT COUNT(*) FROM employees")
429
count = cursor.fetchone()[0]
430
print(f"Employee count: {count}")
431
432
# Release back to pool
433
pool.release(connection)
434
435
# Try different get mode
436
try:
437
quick_conn = pool.acquire(
438
getmode=oracledb.POOL_GETMODE_NOWAIT # Don't wait
439
)
440
print("Got connection immediately")
441
pool.release(quick_conn)
442
except oracledb.DatabaseError as e:
443
print(f"No connection available: {e}")
444
445
pool.close()
446
```
447
448
### Working with Modern Data Types
449
450
```python
451
import oracledb
452
import json
453
454
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
455
456
# Working with JSON data type (Oracle 21c+)
457
with connection.cursor() as cursor:
458
try:
459
cursor.execute("""
460
CREATE TABLE json_demo (
461
id NUMBER PRIMARY KEY,
462
json_data JSON
463
)
464
""")
465
466
# Insert JSON data
467
json_content = {
468
"name": "John Doe",
469
"age": 30,
470
"department": "Engineering",
471
"skills": ["Python", "Oracle", "SQL"]
472
}
473
474
cursor.execute("""
475
INSERT INTO json_demo (id, json_data)
476
VALUES (:1, :2)
477
""", [1, json.dumps(json_content)])
478
479
connection.commit()
480
481
# Query JSON data
482
cursor.execute("SELECT json_data FROM json_demo WHERE id = 1")
483
result = cursor.fetchone()
484
retrieved_json = json.loads(result[0])
485
486
print(f"Retrieved JSON: {retrieved_json}")
487
488
except oracledb.DatabaseError as e:
489
if "ORA-00902" in str(e): # Invalid datatype
490
print("JSON datatype not available (requires Oracle 21c+)")
491
else:
492
print(f"JSON demo error: {e}")
493
494
# Working with Vector data type (Oracle 23c+)
495
with connection.cursor() as cursor:
496
try:
497
cursor.execute("""
498
CREATE TABLE vector_demo (
499
id NUMBER PRIMARY KEY,
500
embedding VECTOR(128, FLOAT32)
501
)
502
""")
503
504
# Vector operations would go here
505
print("Vector table created successfully")
506
507
except oracledb.DatabaseError as e:
508
if "ORA-00902" in str(e):
509
print("Vector datatype not available (requires Oracle 23c+)")
510
else:
511
print(f"Vector demo error: {e}")
512
513
connection.close()
514
```
515
516
### Type Introspection
517
518
```python
519
import oracledb
520
521
def analyze_table_types(connection, table_name):
522
"""Analyze data types in a table."""
523
524
with connection.cursor() as cursor:
525
cursor.execute(f"SELECT * FROM {table_name} WHERE ROWNUM <= 1")
526
cursor.fetchall() # Consume results to get metadata
527
528
print(f"Table: {table_name}")
529
print("Column Type Analysis:")
530
531
for i, desc in enumerate(cursor.description):
532
column_name = desc[0]
533
oracle_type = desc[1]
534
display_size = desc[2]
535
internal_size = desc[3]
536
precision = desc[4]
537
scale = desc[5]
538
null_ok = desc[6]
539
540
# Map Oracle type to readable name
541
type_names = {
542
oracledb.DB_TYPE_VARCHAR: "VARCHAR2",
543
oracledb.DB_TYPE_CHAR: "CHAR",
544
oracledb.DB_TYPE_NUMBER: "NUMBER",
545
oracledb.DB_TYPE_DATE: "DATE",
546
oracledb.DB_TYPE_TIMESTAMP: "TIMESTAMP",
547
oracledb.DB_TYPE_CLOB: "CLOB",
548
oracledb.DB_TYPE_BLOB: "BLOB",
549
oracledb.DB_TYPE_RAW: "RAW"
550
}
551
552
type_name = type_names.get(oracle_type, f"Unknown({oracle_type})")
553
nullable = "NULL" if null_ok else "NOT NULL"
554
555
size_info = ""
556
if precision:
557
if scale:
558
size_info = f"({precision},{scale})"
559
else:
560
size_info = f"({precision})"
561
elif display_size:
562
size_info = f"({display_size})"
563
564
print(f" {column_name}: {type_name}{size_info} {nullable}")
565
566
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
567
568
# Analyze built-in tables
569
analyze_table_types(connection, "employees")
570
print()
571
analyze_table_types(connection, "departments")
572
573
connection.close()
574
```