0
# Oracle Object Types
1

2
Support for Oracle user-defined types including object types, collections (VARRAYs and nested tables), and object attribute access for complex data structures and custom types.
3

4
## Capabilities
5

6
### Object Type Access
7

8
Retrieve and work with Oracle user-defined object types from the database.
9

10
```python { .api }
11
class Connection:
12
    def gettype(self, name: str) -> ObjectType:
13
        """
14
        Get Oracle object type by name.
15
        
16
        Parameters:
17
        - name (str): Object type name (schema.type_name or type_name)
18
        
19
        Returns:
20
        ObjectType object for creating instances
21
        """
22
```
23

24
```python { .api }
25
class ObjectType:
26
    @property
27
    def schema(self) -> str:
28
        """Object type schema name"""
29
        
30
    @property
31
    def name(self) -> str:
32
        """Object type name"""
33
        
34
    @property
35
    def attributes(self) -> list:
36
        """List of object attributes (ObjectAttr objects)"""
37
        
38
    @property
39
    def iscollection(self) -> bool:
40
        """Whether type is a collection (VARRAY or nested table)"""
41
        
42
    def newobject(self) -> Object:
43
        """
44
        Create new instance of object type.
45
        
46
        Returns:
47
        Object instance ready for attribute assignment
48
        """
49
```
50

51
Usage examples:
52

53
```python
54
# Database setup (run in SQL*Plus or similar):
55
# CREATE TYPE address_type AS OBJECT (
56
#   street VARCHAR2(100),
57
#   city VARCHAR2(50),
58
#   state VARCHAR2(20),
59
#   zip_code VARCHAR2(10)
60
# );
61
# 
62
# CREATE TYPE employee_type AS OBJECT (
63
#   emp_id NUMBER,
64
#   name VARCHAR2(100),
65
#   address address_type,
66
#   hire_date DATE
67
# );
68

69
# Get object types
70
address_type = connection.gettype("ADDRESS_TYPE")
71
employee_type = connection.gettype("EMPLOYEE_TYPE")
72

73
print(f"Employee type schema: {employee_type.schema}")
74
print(f"Employee type name: {employee_type.name}")
75
print(f"Is collection: {employee_type.iscollection}")
76

77
# List attributes
78
for attr in employee_type.attributes:
79
    print(f"Attribute: {attr.name}, Type: {attr.type}")
80
```
81

82
### Object Creation and Manipulation
83

84
Create and work with object instances.
85

86
```python { .api }
87
class Object:
88
    def copy(self) -> Object:
89
        """Create copy of object"""
90
        
91
    def aslist(self) -> list:
92
        """Convert object to list (for collections)"""
93
        
94
    def asdict(self) -> dict:
95
        """Convert object to dictionary"""
96
```
97

98
Usage examples:
99

100
```python
101
# Create address object
102
address = address_type.newobject()
103
address.STREET = "123 Main Street"
104
address.CITY = "Anytown"
105
address.STATE = "CA"
106
address.ZIP_CODE = "12345"
107

108
# Create employee object
109
employee = employee_type.newobject()
110
employee.EMP_ID = 1001
111
employee.NAME = "John Doe"
112
employee.ADDRESS = address
113
employee.HIRE_DATE = datetime.date(2023, 1, 15)
114

115
# Insert into database
116
cursor.execute("INSERT INTO employees VALUES (:1)", (employee,))
117
connection.commit()
118

119
# Copy object
120
employee_copy = employee.copy()
121
employee_copy.EMP_ID = 1002
122
employee_copy.NAME = "Jane Smith"
123

124
# Convert to dictionary
125
emp_dict = employee.asdict()
126
print(f"Employee data: {emp_dict}")
127
```
128

129
### Collection Operations
130

131
Work with Oracle collection types (VARRAYs and nested tables).
132

133
```python { .api }
134
class Object:
135
    def append(self, value) -> None:
136
        """Append element to collection"""
137
        
138
    def extend(self, sequence) -> None:
139
        """Extend collection with sequence of values"""
140
        
141
    def getelement(self, index: int):
142
        """Get collection element at index (1-based)"""
143
        
144
    def setelement(self, index: int, value) -> None:
145
        """Set collection element at index (1-based)"""
146
        
147
    def exists(self, index: int) -> bool:
148
        """Check if collection element exists at index"""
149
        
150
    def delete(self, index: int) -> None:
151
        """Delete collection element at index"""
152
        
153
    def trim(self, count: int) -> None:
154
        """Remove count elements from end of collection"""
155
        
156
    def size(self) -> int:
157
        """Get number of elements in collection"""
158
        
159
    def first(self) -> int:
160
        """Get first valid index in collection"""
161
        
162
    def last(self) -> int:
163
        """Get last valid index in collection"""
164
        
165
    def next(self, index: int) -> int:
166
        """Get next valid index after given index"""
167
        
168
    def prev(self, index: int) -> int:
169
        """Get previous valid index before given index"""
170
```
171

172
Usage examples:
173

174
```python
175
# Database setup for collections:
176
# CREATE TYPE phone_list AS VARRAY(5) OF VARCHAR2(20);
177
# CREATE TYPE employee_with_phones AS OBJECT (
178
#   emp_id NUMBER,
179
#   name VARCHAR2(100),
180
#   phones phone_list
181
# );
182

183
# Get collection type
184
phone_list_type = connection.gettype("PHONE_LIST")
185
emp_phones_type = connection.gettype("EMPLOYEE_WITH_PHONES")
186

187
# Create collection
188
phones = phone_list_type.newobject()
189
phones.append("555-1234")
190
phones.append("555-5678")
191
phones.append("555-9012")
192

193
# Create employee with phone collection
194
emp_with_phones = emp_phones_type.newobject()
195
emp_with_phones.EMP_ID = 2001
196
emp_with_phones.NAME = "Alice Johnson"
197
emp_with_phones.PHONES = phones
198

199
# Work with collection elements
200
print(f"Number of phones: {phones.size()}")
201
print(f"First phone: {phones.getelement(1)}")  # Oracle uses 1-based indexing
202
print(f"Last phone: {phones.getelement(phones.size())}")
203

204
# Modify collection
205
phones.setelement(2, "555-WORK")  # Change second phone
206
phones.append("555-HOME")  # Add another phone
207

208
# Check if element exists
209
if phones.exists(1):
210
    print(f"First phone exists: {phones.getelement(1)}")
211

212
# Delete element
213
phones.delete(3)  # Remove third phone
214

215
# Convert collection to Python list
216
phone_list = phones.aslist()
217
print(f"Phone list: {phone_list}")
218

219
# Extend collection
220
additional_phones = ["555-CELL", "555-FAX"]
221
phones.extend(additional_phones)
222
```
223

224
### Object Attributes
225

226
Access and work with object attribute metadata.
227

228
```python { .api }
229
class ObjectAttr:
230
    @property
231
    def name(self) -> str:
232
        """Attribute name"""
233
        
234
    @property
235
    def type(self):
236
        """Attribute type information"""
237
```
238

239
Usage examples:
240

241
```python
242
# Examine object type attributes
243
for attr in employee_type.attributes:
244
    print(f"Attribute: {attr.name}")
245
    print(f"Type: {attr.type}")
246
    
247
    # Access nested object attributes
248
    if attr.name == "ADDRESS":
249
        address_attrs = attr.type.attributes if hasattr(attr.type, 'attributes') else []
250
        for nested_attr in address_attrs:
251
            print(f"  Nested: {nested_attr.name} - {nested_attr.type}")
252
```
253

254
## Working with Complex Object Hierarchies
255

256
### Nested Objects
257

258
Handle complex nested object structures:
259

260
```python
261
# Database setup for nested objects:
262
# CREATE TYPE department_type AS OBJECT (
263
#   dept_id NUMBER,
264
#   dept_name VARCHAR2(50),
265
#   manager employee_type
266
# );
267

268
dept_type = connection.gettype("DEPARTMENT_TYPE")
269

270
# Create nested object structure
271
manager = employee_type.newobject()
272
manager.EMP_ID = 5001
273
manager.NAME = "Jane Manager"
274
manager.ADDRESS = address  # Reuse address from previous example
275
manager.HIRE_DATE = datetime.date(2020, 1, 1)
276

277
department = dept_type.newobject()
278
department.DEPT_ID = 100
279
department.DEPT_NAME = "Engineering"
280
department.MANAGER = manager
281

282
# Insert complex object
283
cursor.execute("INSERT INTO departments VALUES (:1)", (department,))
284
connection.commit()
285

286
# Query and access nested data
287
cursor.execute("SELECT * FROM departments WHERE dept_id = :1", (100,))
288
dept_row = cursor.fetchone()[0]
289

290
print(f"Department: {dept_row.DEPT_NAME}")
291
print(f"Manager: {dept_row.MANAGER.NAME}")
292
print(f"Manager Address: {dept_row.MANAGER.ADDRESS.CITY}, {dept_row.MANAGER.ADDRESS.STATE}")
293
```
294

295
### Collection of Objects
296

297
Work with collections containing object instances:
298

299
```python
300
# Database setup for object collections:
301
# CREATE TYPE employee_list AS TABLE OF employee_type;
302
# CREATE TYPE project_type AS OBJECT (
303
#   project_id NUMBER,
304
#   project_name VARCHAR2(100),
305
#   team_members employee_list
306
# );
307

308
employee_list_type = connection.gettype("EMPLOYEE_LIST")
309
project_type = connection.gettype("PROJECT_TYPE")
310

311
# Create collection of employees
312
team = employee_list_type.newobject()
313

314
# Add team members
315
for i, name in enumerate(["Alice", "Bob", "Charlie"], 1):
316
    member = employee_type.newobject()
317
    member.EMP_ID = 3000 + i
318
    member.NAME = name
319
    member.ADDRESS = address  # Shared address for example
320
    member.HIRE_DATE = datetime.date(2023, 1, i)
321
    team.append(member)
322

323
# Create project with team
324
project = project_type.newobject()
325
project.PROJECT_ID = 1001
326
project.PROJECT_NAME = "Database Migration"
327
project.TEAM_MEMBERS = team
328

329
# Insert project
330
cursor.execute("INSERT INTO projects VALUES (:1)", (project,))
331
connection.commit()
332

333
# Query and process team members
334
cursor.execute("SELECT * FROM projects WHERE project_id = :1", (1001,))
335
proj_row = cursor.fetchone()[0]
336

337
print(f"Project: {proj_row.PROJECT_NAME}")
338
print("Team members:")
339
for i in range(1, proj_row.TEAM_MEMBERS.size() + 1):
340
    member = proj_row.TEAM_MEMBERS.getelement(i)
341
    print(f"  {member.EMP_ID}: {member.NAME}")
342
```
343

344
## Performance and Best Practices
345

346
### Efficient Object Handling
347

348
Optimize object operations for better performance:
349

350
```python
351
def bulk_object_operations(object_type, data_list):
352
    """Efficiently create and insert multiple objects"""
353
    objects = []
354
    
355
    for data in data_list:
356
        obj = object_type.newobject()
357
        # Set attributes from data dictionary
358
        for attr_name, value in data.items():
359
            setattr(obj, attr_name.upper(), value)
360
        objects.append(obj)
361
    
362
    # Bulk insert
363
    cursor = connection.cursor()
364
    cursor.executemany("INSERT INTO object_table VALUES (:1)", 
365
                      [(obj,) for obj in objects])
366
    connection.commit()
367
    cursor.close()
368

369
def cache_object_types(connection):
370
    """Cache frequently used object types"""
371
    type_cache = {}
372
    
373
    # Pre-load common types
374
    common_types = ["EMPLOYEE_TYPE", "ADDRESS_TYPE", "DEPARTMENT_TYPE"]
375
    for type_name in common_types:
376
        try:
377
            type_cache[type_name] = connection.gettype(type_name)
378
        except cx_Oracle.DatabaseError:
379
            print(f"Warning: Type {type_name} not found")
380
            
381
    return type_cache
382

383
# Usage
384
type_cache = cache_object_types(connection)
385
employee_type = type_cache.get("EMPLOYEE_TYPE")
386
```
387

388
### Object Serialization
389

390
Convert objects to/from standard Python data structures:
391

392
```python
393
def object_to_dict(obj):
394
    """Convert Oracle object to Python dictionary recursively"""
395
    if obj is None:
396
        return None
397
        
398
    result = {}
399
    obj_dict = obj.asdict()
400
    
401
    for attr_name, value in obj_dict.items():
402
        if hasattr(value, 'asdict'):  # Nested object
403
            result[attr_name] = object_to_dict(value)
404
        elif hasattr(value, 'aslist'):  # Collection
405
            result[attr_name] = [
406
                object_to_dict(item) if hasattr(item, 'asdict') else item
407
                for item in value.aslist()
408
            ]
409
        else:
410
            result[attr_name] = value
411
            
412
    return result
413

414
def dict_to_object(data_dict, object_type):
415
    """Convert Python dictionary to Oracle object"""
416
    obj = object_type.newobject()
417
    
418
    for attr_name, value in data_dict.items():
419
        if isinstance(value, dict):
420
            # Handle nested objects (requires type information)
421
            pass  # Implementation depends on specific schema
422
        elif isinstance(value, list):
423
            # Handle collections (requires type information)
424
            pass  # Implementation depends on specific schema
425
        else:
426
            setattr(obj, attr_name.upper(), value)
427
            
428
    return obj
429

430
# Usage
431
emp_dict = object_to_dict(employee)
432
print(f"Employee as dict: {emp_dict}")
433
```
434

435
## Error Handling
436

437
Handle object-related errors and exceptions:
438

439
```python
440
try:
441
    # Object operations
442
    obj_type = connection.gettype("NONEXISTENT_TYPE")
443
    
444
except cx_Oracle.DatabaseError as e:
445
    error_obj, = e.args
446
    if error_obj.code == 942:  # Object does not exist
447
        print("Object type not found")
448
    elif error_obj.code == 22303:  # Type not found
449
        print("Type not found in database")
450
    else:
451
        print(f"Object type error: {error_obj.message}")
452
        
453
try:
454
    # Object attribute access
455
    obj = obj_type.newobject()
456
    obj.NONEXISTENT_ATTR = "value"
457
    
458
except AttributeError as e:
459
    print(f"Attribute error: {e}")
460
    
461
except Exception as e:
462
    print(f"Unexpected error: {e}")
463
```
464

465
## Object Type Best Practices
466

467
1. **Cache object types**: Load frequently used types once and reuse
468
2. **Use asdict() for serialization**: Convert objects to dictionaries for JSON/REST APIs
469
3. **Handle nested structures carefully**: Be aware of deep object hierarchies' performance impact
470
4. **Validate object structure**: Check attribute existence before assignment
471
5. **Use collections efficiently**: Leverage collection methods for bulk operations
472
6. **Consider memory usage**: Large collections and deep nesting can consume significant memory
473
7. **Handle NULL values**: Check for None values in object attributes
474
8. **Use appropriate indexing**: Remember Oracle uses 1-based indexing for collections