0
# Database Objects
1
2
Work with Oracle user-defined types including objects, collections, and nested tables. Provides object type metadata and instance manipulation for complex data structures, enabling seamless integration between Python and Oracle's object-relational features.
3
4
## Capabilities
5
6
### DbObject Class
7
8
Represent and manipulate Oracle database objects with full support for object attributes and collection operations.
9
10
```python { .api }
11
class DbObject:
12
"""Represent Oracle database objects including object types and collections."""
13
14
# Properties
15
type: DbObjectType # Object type metadata
16
17
def asdict(self) -> dict:
18
"""
19
Convert object to dictionary representation.
20
21
Returns:
22
dict: Object attributes as key-value pairs
23
"""
24
25
def aslist(self) -> list:
26
"""
27
Convert collection object to list representation.
28
29
Returns:
30
list: Collection elements as list
31
32
Raises:
33
TypeError: If object is not a collection
34
"""
35
36
def copy(self) -> DbObject:
37
"""
38
Create a copy of the object.
39
40
Returns:
41
DbObject: Copy of the object
42
"""
43
44
def append(self, element) -> None:
45
"""
46
Append element to collection object.
47
48
Parameters:
49
- element: Element to append
50
51
Raises:
52
TypeError: If object is not a collection
53
"""
54
55
def extend(self, sequence) -> None:
56
"""
57
Extend collection with sequence of elements.
58
59
Parameters:
60
- sequence: Sequence of elements to add
61
62
Raises:
63
TypeError: If object is not a collection
64
"""
65
66
def delete(self, index) -> None:
67
"""
68
Delete element at specified index from collection.
69
70
Parameters:
71
- index (int): Index of element to delete
72
73
Raises:
74
TypeError: If object is not a collection
75
IndexError: If index is out of range
76
"""
77
78
def exists(self, index) -> bool:
79
"""
80
Check if element exists at specified index in collection.
81
82
Parameters:
83
- index (int): Index to check
84
85
Returns:
86
bool: True if element exists at index
87
88
Raises:
89
TypeError: If object is not a collection
90
"""
91
92
def getelement(self, index):
93
"""
94
Get element at specified index from collection.
95
96
Parameters:
97
- index (int): Index of element to retrieve
98
99
Returns:
100
Element at specified index
101
102
Raises:
103
TypeError: If object is not a collection
104
IndexError: If index is out of range
105
"""
106
107
def setelement(self, index, value) -> None:
108
"""
109
Set element at specified index in collection.
110
111
Parameters:
112
- index (int): Index to set
113
- value: Value to set
114
115
Raises:
116
TypeError: If object is not a collection
117
IndexError: If index is out of range
118
"""
119
120
def size(self) -> int:
121
"""
122
Get size of collection object.
123
124
Returns:
125
int: Number of elements in collection
126
127
Raises:
128
TypeError: If object is not a collection
129
"""
130
131
def trim(self, size) -> None:
132
"""
133
Trim collection to specified size.
134
135
Parameters:
136
- size (int): New size for collection
137
138
Raises:
139
TypeError: If object is not a collection
140
"""
141
142
def first(self) -> int:
143
"""
144
Get index of first element in collection.
145
146
Returns:
147
int: Index of first element
148
149
Raises:
150
TypeError: If object is not a collection
151
"""
152
153
def last(self) -> int:
154
"""
155
Get index of last element in collection.
156
157
Returns:
158
int: Index of last element
159
160
Raises:
161
TypeError: If object is not a collection
162
"""
163
164
def next(self, index) -> int:
165
"""
166
Get index of next element after specified index.
167
168
Parameters:
169
- index (int): Current index
170
171
Returns:
172
int: Index of next element or None if no next element
173
174
Raises:
175
TypeError: If object is not a collection
176
"""
177
178
def prev(self, index) -> int:
179
"""
180
Get index of previous element before specified index.
181
182
Parameters:
183
- index (int): Current index
184
185
Returns:
186
int: Index of previous element or None if no previous element
187
188
Raises:
189
TypeError: If object is not a collection
190
"""
191
```
192
193
### DbObjectType Class
194
195
Metadata and factory for database object types, providing introspection and instance creation capabilities.
196
197
```python { .api }
198
class DbObjectType:
199
"""Metadata for database object types."""
200
201
# Properties
202
name: str # Type name
203
schema: str # Schema name
204
package_name: str # Package name (for package types)
205
attributes: list # List of DbObjectAttr objects
206
iscollection: bool # True if type is a collection
207
element_type: DbObjectType # Element type for collections
208
209
def newobject(self, value=None) -> DbObject:
210
"""
211
Create new object instance of this type.
212
213
Parameters:
214
- value: Initial value for object (dict, list, or compatible value)
215
216
Returns:
217
DbObject: New object instance
218
"""
219
```
220
221
### DbObjectAttr Class
222
223
Attribute metadata for database object types, providing detailed information about object attributes.
224
225
```python { .api }
226
class DbObjectAttr:
227
"""Attribute metadata for database objects."""
228
229
# Properties
230
name: str # Attribute name
231
type: type # Attribute data type
232
precision: int # Numeric precision (for numeric types)
233
scale: int # Numeric scale (for numeric types)
234
max_size: int # Maximum size (for string/raw types)
235
type_code: int # Internal type code
236
null_ok: bool # True if attribute can be NULL
237
```
238
239
## Usage Examples
240
241
### Working with Object Types
242
243
```python
244
import oracledb
245
246
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
247
248
# Create object type in database
249
with connection.cursor() as cursor:
250
cursor.execute("""
251
CREATE OR REPLACE TYPE address_type AS OBJECT (
252
street VARCHAR2(100),
253
city VARCHAR2(50),
254
state VARCHAR2(20),
255
zip_code VARCHAR2(10)
256
)
257
""")
258
259
cursor.execute("""
260
CREATE OR REPLACE TYPE person_type AS OBJECT (
261
first_name VARCHAR2(50),
262
last_name VARCHAR2(50),
263
age NUMBER,
264
address address_type
265
)
266
""")
267
268
cursor.execute("""
269
CREATE TABLE people_obj (
270
id NUMBER PRIMARY KEY,
271
person person_type
272
)
273
""")
274
275
# Get object type
276
person_type = connection.gettype("PERSON_TYPE")
277
address_type = connection.gettype("ADDRESS_TYPE")
278
279
print(f"Type: {person_type.name}")
280
print(f"Schema: {person_type.schema}")
281
print(f"Is collection: {person_type.iscollection}")
282
283
# Examine attributes
284
print("Attributes:")
285
for attr in person_type.attributes:
286
print(f" {attr.name}: {attr.type} (max_size: {attr.max_size})")
287
288
# Create object instances
289
address = address_type.newobject()
290
address.STREET = "123 Main St"
291
address.CITY = "Anytown"
292
address.STATE = "CA"
293
address.ZIP_CODE = "12345"
294
295
person = person_type.newobject()
296
person.FIRST_NAME = "John"
297
person.LAST_NAME = "Doe"
298
person.AGE = 30
299
person.ADDRESS = address
300
301
# Insert object into table
302
with connection.cursor() as cursor:
303
cursor.execute("""
304
INSERT INTO people_obj (id, person) VALUES (:1, :2)
305
""", [1, person])
306
307
connection.commit()
308
309
# Query and work with objects
310
with connection.cursor() as cursor:
311
cursor.execute("SELECT person FROM people_obj WHERE id = :1", [1])
312
result = cursor.fetchone()
313
person_obj = result[0]
314
315
print(f"Name: {person_obj.FIRST_NAME} {person_obj.LAST_NAME}")
316
print(f"Age: {person_obj.AGE}")
317
print(f"Address: {person_obj.ADDRESS.STREET}, {person_obj.ADDRESS.CITY}")
318
319
# Convert to dictionary
320
person_dict = person_obj.asdict()
321
print(f"As dict: {person_dict}")
322
323
connection.close()
324
```
325
326
### Working with Collections
327
328
```python
329
import oracledb
330
331
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
332
333
# Create collection types
334
with connection.cursor() as cursor:
335
cursor.execute("""
336
CREATE OR REPLACE TYPE string_array AS VARRAY(100) OF VARCHAR2(100)
337
""")
338
339
cursor.execute("""
340
CREATE OR REPLACE TYPE number_table AS TABLE OF NUMBER
341
""")
342
343
cursor.execute("""
344
CREATE TABLE collections_demo (
345
id NUMBER PRIMARY KEY,
346
tags string_array,
347
scores number_table
348
) NESTED TABLE scores STORE AS scores_tab
349
""")
350
351
# Get collection types
352
string_array_type = connection.gettype("STRING_ARRAY")
353
number_table_type = connection.gettype("NUMBER_TABLE")
354
355
print(f"String array is collection: {string_array_type.iscollection}")
356
print(f"Element type: {string_array_type.element_type}")
357
358
# Create collection instances
359
tags = string_array_type.newobject()
360
tags.append("python")
361
tags.append("oracle")
362
tags.append("database")
363
364
scores = number_table_type.newobject()
365
scores.extend([95, 87, 92, 89, 94])
366
367
print(f"Tags size: {tags.size()}")
368
print(f"Scores size: {scores.size()}")
369
370
# Insert collections
371
with connection.cursor() as cursor:
372
cursor.execute("""
373
INSERT INTO collections_demo (id, tags, scores)
374
VALUES (:1, :2, :3)
375
""", [1, tags, scores])
376
377
connection.commit()
378
379
# Query and manipulate collections
380
with connection.cursor() as cursor:
381
cursor.execute("SELECT tags, scores FROM collections_demo WHERE id = :1", [1])
382
result = cursor.fetchone()
383
tags_obj, scores_obj = result
384
385
# Work with VARRAY
386
print("Tags:")
387
for i in range(tags_obj.size()):
388
print(f" {tags_obj.getelement(i)}")
389
390
# Convert to list
391
tags_list = tags_obj.aslist()
392
print(f"Tags as list: {tags_list}")
393
394
# Work with nested table
395
print("Scores:")
396
for i in range(scores_obj.size()):
397
print(f" Score {i}: {scores_obj.getelement(i)}")
398
399
# Add new score
400
scores_obj.append(96)
401
402
# Update in database
403
cursor.execute("""
404
UPDATE collections_demo SET scores = :1 WHERE id = :2
405
""", [scores_obj, 1])
406
407
connection.commit()
408
409
connection.close()
410
```
411
412
### Advanced Object Manipulation
413
414
```python
415
import oracledb
416
417
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
418
419
# Create complex object hierarchy
420
with connection.cursor() as cursor:
421
cursor.execute("""
422
CREATE OR REPLACE TYPE phone_type AS OBJECT (
423
area_code VARCHAR2(3),
424
number VARCHAR2(8),
425
type VARCHAR2(10)
426
)
427
""")
428
429
cursor.execute("""
430
CREATE OR REPLACE TYPE phone_list AS TABLE OF phone_type
431
""")
432
433
cursor.execute("""
434
CREATE OR REPLACE TYPE contact_type AS OBJECT (
435
name VARCHAR2(100),
436
email VARCHAR2(100),
437
phones phone_list
438
)
439
""")
440
441
cursor.execute("""
442
CREATE TABLE contacts (
443
id NUMBER PRIMARY KEY,
444
contact contact_type
445
) NESTED TABLE contact.phones STORE AS contact_phones_tab
446
""")
447
448
# Get types
449
contact_type = connection.gettype("CONTACT_TYPE")
450
phone_type = connection.gettype("PHONE_TYPE")
451
phone_list_type = connection.gettype("PHONE_LIST")
452
453
# Create complex object
454
contact = contact_type.newobject()
455
contact.NAME = "Jane Smith"
456
contact.EMAIL = "jane.smith@example.com"
457
458
# Create phone list
459
phones = phone_list_type.newobject()
460
461
# Create individual phones
462
home_phone = phone_type.newobject()
463
home_phone.AREA_CODE = "555"
464
home_phone.NUMBER = "1234567"
465
home_phone.TYPE = "home"
466
467
work_phone = phone_type.newobject()
468
work_phone.AREA_CODE = "555"
469
work_phone.NUMBER = "7654321"
470
work_phone.TYPE = "work"
471
472
mobile_phone = phone_type.newobject()
473
mobile_phone.AREA_CODE = "555"
474
mobile_phone.NUMBER = "9876543"
475
mobile_phone.TYPE = "mobile"
476
477
# Add phones to collection
478
phones.append(home_phone)
479
phones.append(work_phone)
480
phones.append(mobile_phone)
481
482
# Assign phones to contact
483
contact.PHONES = phones
484
485
# Insert complex object
486
with connection.cursor() as cursor:
487
cursor.execute("""
488
INSERT INTO contacts (id, contact) VALUES (:1, :2)
489
""", [1, contact])
490
491
connection.commit()
492
493
# Query and navigate complex object
494
with connection.cursor() as cursor:
495
cursor.execute("SELECT contact FROM contacts WHERE id = :1", [1])
496
result = cursor.fetchone()
497
contact_obj = result[0]
498
499
print(f"Contact: {contact_obj.NAME}")
500
print(f"Email: {contact_obj.EMAIL}")
501
print("Phones:")
502
503
phones_obj = contact_obj.PHONES
504
for i in range(phones_obj.size()):
505
phone = phones_obj.getelement(i)
506
print(f" {phone.TYPE}: ({phone.AREA_CODE}) {phone.NUMBER}")
507
508
# Modify collection
509
print(f"\nOriginal phone count: {phones_obj.size()}")
510
511
# Remove work phone (index 1)
512
phones_obj.delete(1)
513
print(f"After deletion: {phones_obj.size()}")
514
515
# Add new phone
516
fax_phone = phone_type.newobject()
517
fax_phone.AREA_CODE = "555"
518
fax_phone.NUMBER = "1111111"
519
fax_phone.TYPE = "fax"
520
phones_obj.append(fax_phone)
521
522
print(f"After addition: {phones_obj.size()}")
523
524
# Update in database
525
cursor.execute("""
526
UPDATE contacts SET contact = :1 WHERE id = :2
527
""", [contact_obj, 1])
528
529
connection.commit()
530
531
connection.close()
532
```
533
534
### Object Introspection
535
536
```python
537
import oracledb
538
539
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
540
541
def inspect_object_type(connection, type_name):
542
"""Inspect an object type and display its metadata."""
543
544
try:
545
obj_type = connection.gettype(type_name)
546
547
print(f"Object Type: {obj_type.name}")
548
print(f"Schema: {obj_type.schema}")
549
print(f"Package: {obj_type.package_name or 'None'}")
550
print(f"Is Collection: {obj_type.iscollection}")
551
552
if obj_type.iscollection:
553
print(f"Element Type: {obj_type.element_type.name if obj_type.element_type else 'Unknown'}")
554
else:
555
print("Attributes:")
556
for attr in obj_type.attributes:
557
nullable = "NULL" if attr.null_ok else "NOT NULL"
558
size_info = f"({attr.max_size})" if attr.max_size else ""
559
precision_info = f"({attr.precision},{attr.scale})" if attr.precision else ""
560
561
print(f" {attr.name}: {attr.type.__name__}{size_info}{precision_info} {nullable}")
562
563
except Exception as e:
564
print(f"Error inspecting {type_name}: {e}")
565
566
# Create sample types for inspection
567
with connection.cursor() as cursor:
568
cursor.execute("""
569
CREATE OR REPLACE TYPE employee_type AS OBJECT (
570
emp_id NUMBER(6) NOT NULL,
571
first_name VARCHAR2(50),
572
last_name VARCHAR2(50) NOT NULL,
573
email VARCHAR2(100),
574
hire_date DATE,
575
salary NUMBER(8,2),
576
commission_pct NUMBER(2,2)
577
)
578
""")
579
580
cursor.execute("""
581
CREATE OR REPLACE TYPE dept_employees AS TABLE OF employee_type
582
""")
583
584
# Inspect different object types
585
inspect_object_type(connection, "EMPLOYEE_TYPE")
586
print()
587
inspect_object_type(connection, "DEPT_EMPLOYEES")
588
589
connection.close()
590
```
591
592
### Converting Between Python and Oracle Objects
593
594
```python
595
import oracledb
596
from datetime import date, datetime
597
598
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
599
600
# Create object type
601
with connection.cursor() as cursor:
602
cursor.execute("""
603
CREATE OR REPLACE TYPE product_type AS OBJECT (
604
product_id NUMBER,
605
name VARCHAR2(100),
606
price NUMBER(10,2),
607
launch_date DATE,
608
features SYS.ODCIVARCHAR2LIST
609
)
610
""")
611
612
product_type = connection.gettype("PRODUCT_TYPE")
613
string_list_type = connection.gettype("SYS.ODCIVARCHAR2LIST")
614
615
# Create from Python dictionary
616
product_data = {
617
'PRODUCT_ID': 1001,
618
'NAME': 'Laptop Computer',
619
'PRICE': 1299.99,
620
'LAUNCH_DATE': date(2024, 1, 15),
621
'FEATURES': ['16GB RAM', '512GB SSD', 'Backlit Keyboard', 'Webcam']
622
}
623
624
# Method 1: Create object and set attributes
625
product = product_type.newobject()
626
product.PRODUCT_ID = product_data['PRODUCT_ID']
627
product.NAME = product_data['NAME']
628
product.PRICE = product_data['PRICE']
629
product.LAUNCH_DATE = product_data['LAUNCH_DATE']
630
631
# Create features collection
632
features = string_list_type.newobject()
633
features.extend(product_data['FEATURES'])
634
product.FEATURES = features
635
636
# Method 2: Create object with initial value (dictionary)
637
product2 = product_type.newobject(product_data)
638
639
print("Product 1:")
640
print(f" ID: {product.PRODUCT_ID}")
641
print(f" Name: {product.NAME}")
642
print(f" Price: {product.PRICE}")
643
print(f" Launch Date: {product.LAUNCH_DATE}")
644
print(f" Features: {product.FEATURES.aslist()}")
645
646
print("\nProduct 2:")
647
product2_dict = product2.asdict()
648
print(f" As dict: {product2_dict}")
649
650
# Use in SQL
651
with connection.cursor() as cursor:
652
cursor.execute("""
653
CREATE TABLE products_obj (
654
id NUMBER PRIMARY KEY,
655
product product_type
656
) NESTED TABLE product.features STORE AS product_features_tab
657
""")
658
659
cursor.execute("""
660
INSERT INTO products_obj (id, product) VALUES (:1, :2)
661
""", [1, product])
662
663
connection.commit()
664
665
connection.close()
666
```