0
# Large Objects (LOBs)
1

2
Handling of CLOB, BLOB, NCLOB, and BFILE objects with streaming read/write operations, temporary LOB creation, and file-based LOB operations for efficient processing of large data.
3

4
## Capabilities
5

6
### LOB Types and Creation
7

8
Oracle supports several Large Object types for different data requirements.
9

10
```python { .api }
11
# LOB type constants
12
DB_TYPE_CLOB: DbType      # Character LOB (up to 4GB)
13
DB_TYPE_BLOB: DbType      # Binary LOB (up to 4GB)  
14
DB_TYPE_NCLOB: DbType     # National Character LOB (up to 4GB)
15
DB_TYPE_BFILE: DbType     # Binary file LOB (external file reference)
16
```
17

18
Create temporary LOBs for data processing:
19

20
```python { .api }
21
class Connection:
22
    def createlob(self, lobtype) -> LOB:
23
        """
24
        Create temporary LOB object.
25
        
26
        Parameters:
27
        - lobtype: LOB type (DB_TYPE_CLOB, DB_TYPE_BLOB, DB_TYPE_NCLOB)
28
        
29
        Returns:
30
        Temporary LOB object
31
        """
32
```
33

34
Usage examples:
35

36
```python
37
# Create temporary LOBs
38
clob = connection.createlob(cx_Oracle.DB_TYPE_CLOB)
39
blob = connection.createlob(cx_Oracle.DB_TYPE_BLOB)
40
nclob = connection.createlob(cx_Oracle.DB_TYPE_NCLOB)
41

42
# Use in SQL operations
43
cursor.execute("INSERT INTO documents (id, content) VALUES (:1, :2)", 
44
               (1, clob))
45
```
46

47
### LOB Reading Operations
48

49
Read data from LOB objects with various methods and options.
50

51
```python { .api }
52
class LOB:
53
    def read(self, offset=1, amount=None) -> Union[str, bytes]:
54
        """
55
        Read data from LOB starting at specified offset.
56
        
57
        Parameters:
58
        - offset (int): Starting position (1-based)
59
        - amount (int): Number of characters/bytes to read (None for all)
60
        
61
        Returns:
62
        str for CLOB/NCLOB, bytes for BLOB/BFILE
63
        """
64
        
65
    def getvalue(self) -> Union[str, bytes]:
66
        """
67
        Read entire LOB value as string or bytes.
68
        
69
        Returns:
70
        Complete LOB content (str for CLOB/NCLOB, bytes for BLOB/BFILE)
71
        """
72
        
73
    @property
74
    def size(self) -> int:
75
        """
76
        Get LOB size in characters (CLOB/NCLOB) or bytes (BLOB/BFILE).
77
        
78
        Returns:
79
        Size of LOB data
80
        """
81
        
82
    @property 
83
    def chunksize(self) -> int:
84
        """
85
        Get optimal chunk size for reading/writing LOB data.
86
        
87
        Returns:
88
        Recommended chunk size in bytes
89
        """
90
```
91

92
Usage examples:
93

94
```python
95
# Fetch LOB from database
96
cursor.execute("SELECT document_content FROM documents WHERE id = :1", (1,))
97
lob = cursor.fetchone()[0]
98

99
# Read entire LOB
100
full_content = lob.getvalue()
101
print(f"Document size: {len(full_content)} characters")
102

103
# Read LOB in chunks
104
chunk_size = lob.chunksize
105
offset = 1
106
while offset <= lob.size:
107
    chunk = lob.read(offset, chunk_size)
108
    if not chunk:
109
        break
110
    # Process chunk...
111
    print(f"Read {len(chunk)} characters from offset {offset}")
112
    offset += len(chunk)
113

114
# Read specific portion
115
first_1000_chars = lob.read(1, 1000)
116
middle_section = lob.read(5000, 2000)
117
```
118

119
### LOB Writing Operations
120

121
Write data to LOB objects with streaming support for large data.
122

123
```python { .api }
124
class LOB:
125
    def write(self, data: Union[str, bytes], offset=1) -> int:
126
        """
127
        Write data to LOB at specified offset.
128
        
129
        Parameters:
130
        - data: Data to write (str for CLOB/NCLOB, bytes for BLOB)
131
        - offset (int): Starting position (1-based)
132
        
133
        Returns:
134
        Number of characters/bytes written
135
        """
136
        
137
    def trim(self, newSize=0) -> None:
138
        """
139
        Trim LOB to specified size.
140
        
141
        Parameters:
142
        - newSize (int): New size in characters/bytes (0 to empty)
143
        """
144
```
145

146
Usage examples:
147

148
```python
149
# Create and populate temporary CLOB
150
clob = connection.createlob(cx_Oracle.DB_TYPE_CLOB)
151
clob.write("Initial content at the beginning")
152

153
# Append more data
154
current_size = clob.size
155
clob.write(" Additional content appended", current_size + 1)
156

157
# Write large data in chunks
158
large_text = "Very large document content..." * 10000
159
chunk_size = 32768  # 32KB chunks
160

161
offset = 1
162
for i in range(0, len(large_text), chunk_size):
163
    chunk = large_text[i:i + chunk_size]
164
    bytes_written = clob.write(chunk, offset)
165
    offset += bytes_written
166

167
# Insert into database
168
cursor.execute("INSERT INTO documents (id, content) VALUES (:1, :2)", 
169
               (2, clob))
170

171
# Update existing LOB in database
172
cursor.execute("SELECT content FROM documents WHERE id = :1 FOR UPDATE", (1,))
173
existing_lob = cursor.fetchone()[0]
174
existing_lob.write("Updated content", 1)
175
connection.commit()
176
```
177

178
### LOB File Operations
179

180
Manage LOB lifecycle and external file references.
181

182
```python { .api }
183
class LOB:
184
    def open(self) -> None:
185
        """Open LOB for reading or writing"""
186
        
187
    def close(self) -> None:
188
        """Close LOB and free resources"""
189
        
190
    def isopen(self) -> bool:
191
        """Check if LOB is currently open"""
192
```
193

194
BFILE-specific operations for external file references:
195

196
```python { .api }
197
class LOB:
198
    def fileexists(self) -> bool:
199
        """
200
        Check if BFILE external file exists (BFILE only).
201
        
202
        Returns:
203
        True if file exists, False otherwise
204
        """
205
        
206
    def getfilename(self) -> tuple:
207
        """
208
        Get BFILE directory alias and filename (BFILE only).
209
        
210
        Returns:
211
        Tuple of (directory_alias, filename)
212
        """
213
        
214
    def setfilename(self, dir_alias: str, filename: str) -> None:
215
        """
216
        Set BFILE directory alias and filename (BFILE only).
217
        
218
        Parameters:
219
        - dir_alias (str): Oracle directory alias
220
        - filename (str): File name within directory
221
        """
222
```
223

224
Usage examples:
225

226
```python
227
# Work with BFILEs (external files)
228
cursor.execute("SELECT file_content FROM file_references WHERE id = :1", (1,))
229
bfile = cursor.fetchone()[0]
230

231
# Check if external file exists
232
if bfile.fileexists():
233
    dir_alias, filename = bfile.getfilename()
234
    print(f"File: {filename} in directory {dir_alias}")
235
    
236
    # Read external file content
237
    bfile.open()
238
    try:
239
        content = bfile.read()
240
        print(f"File size: {len(content)} bytes")
241
    finally:
242
        bfile.close()
243
else:
244
    print("External file not found")
245

246
# Create new BFILE reference
247
new_bfile = connection.createlob(cx_Oracle.DB_TYPE_BFILE)
248
new_bfile.setfilename("DATA_DIR", "document.pdf")
249
cursor.execute("INSERT INTO file_references (id, file_content) VALUES (:1, :2)",
250
               (2, new_bfile))
251
```
252

253
### LOB Context Management
254

255
LOBs support Python context managers for automatic resource cleanup:
256

257
```python
258
# Automatic LOB cleanup
259
cursor.execute("SELECT content FROM documents WHERE id = :1", (1,))
260
with cursor.fetchone()[0] as lob:
261
    content = lob.read()
262
    # LOB automatically closed when exiting context
263

264
# Temporary LOB with context management
265
with connection.createlob(cx_Oracle.DB_TYPE_CLOB) as temp_lob:
266
    temp_lob.write("Temporary content")
267
    cursor.execute("INSERT INTO temp_docs VALUES (:1, :2)", (1, temp_lob))
268
    # Temporary LOB cleaned up automatically
269
```
270

271
## Working with Large Data
272

273
### Streaming Large Files
274

275
Efficiently handle very large files using streaming operations:
276

277
```python
278
def stream_file_to_lob(filename, lob):
279
    """Stream file content to LOB in chunks"""
280
    chunk_size = lob.chunksize
281
    
282
    with open(filename, 'rb') as file:
283
        offset = 1
284
        while True:
285
            chunk = file.read(chunk_size)
286
            if not chunk:
287
                break
288
            lob.write(chunk, offset)
289
            offset += len(chunk)
290

291
def stream_lob_to_file(lob, filename):
292
    """Stream LOB content to file in chunks"""
293
    chunk_size = lob.chunksize
294
    
295
    with open(filename, 'wb') as file:
296
        offset = 1
297
        while offset <= lob.size:
298
            chunk = lob.read(offset, chunk_size)
299
            if not chunk:
300
                break
301
            file.write(chunk)
302
            offset += len(chunk)
303

304
# Usage
305
blob = connection.createlob(cx_Oracle.DB_TYPE_BLOB)
306
stream_file_to_lob("large_document.pdf", blob)
307

308
cursor.execute("INSERT INTO documents (id, content) VALUES (:1, :2)", 
309
               (1, blob))
310
connection.commit()
311

312
# Later, retrieve and save to file
313
cursor.execute("SELECT content FROM documents WHERE id = :1", (1,))
314
retrieved_blob = cursor.fetchone()[0]
315
stream_lob_to_file(retrieved_blob, "retrieved_document.pdf")
316
```
317

318
### LOB Performance Optimization
319

320
Tips for optimal LOB performance:
321

322
```python
323
# Use appropriate chunk sizes
324
def optimal_lob_copy(source_lob, target_lob):
325
    """Copy LOB using optimal chunk size"""
326
    chunk_size = max(source_lob.chunksize, target_lob.chunksize)
327
    
328
    offset = 1
329
    while offset <= source_lob.size:
330
        chunk = source_lob.read(offset, chunk_size)
331
        if not chunk:
332
            break
333
        target_lob.write(chunk, offset)
334
        offset += len(chunk)
335

336
# Batch LOB operations
337
def batch_lob_inserts(lob_data_list):
338
    """Insert multiple LOBs efficiently"""
339
    cursor = connection.cursor()
340
    
341
    # Prepare statement once
342
    cursor.prepare("INSERT INTO documents (id, content) VALUES (:1, :2)")
343
    
344
    for doc_id, content in lob_data_list:
345
        lob = connection.createlob(cx_Oracle.DB_TYPE_CLOB)
346
        lob.write(content)
347
        cursor.execute(None, (doc_id, lob))
348
    
349
    connection.commit()
350
    cursor.close()
351
```
352

353
### LOB Data Type Conversion
354

355
Convert between different LOB types and Python data types:
356

357
```python
358
# Convert string to CLOB
359
def string_to_clob(text_content):
360
    """Convert string to CLOB"""
361
    clob = connection.createlob(cx_Oracle.DB_TYPE_CLOB)
362
    clob.write(text_content)
363
    return clob
364

365
# Convert bytes to BLOB  
366
def bytes_to_blob(binary_content):
367
    """Convert bytes to BLOB"""
368
    blob = connection.createlob(cx_Oracle.DB_TYPE_BLOB)
369
    blob.write(binary_content)
370
    return blob
371

372
# Convert CLOB to string
373
def clob_to_string(clob_obj):
374
    """Convert CLOB to Python string"""
375
    return clob_obj.getvalue() if clob_obj else None
376

377
# Convert BLOB to bytes
378
def blob_to_bytes(blob_obj):
379
    """Convert BLOB to Python bytes"""
380
    return blob_obj.getvalue() if blob_obj else None
381

382
# Usage examples
383
text_data = "Large text document content..."
384
clob = string_to_clob(text_data)
385

386
with open("image.jpg", "rb") as f:
387
    binary_data = f.read()
388
blob = bytes_to_blob(binary_data)
389

390
# Insert both LOBs
391
cursor.execute("INSERT INTO mixed_content (id, text_data, binary_data) VALUES (:1, :2, :3)",
392
               (1, clob, blob))
393
```
394

395
## Error Handling
396

397
Common LOB error handling patterns:
398

399
```python
400
try:
401
    # LOB operations that might fail
402
    lob = connection.createlob(cx_Oracle.DB_TYPE_CLOB)
403
    lob.write(content)
404
    
405
except cx_Oracle.DatabaseError as e:
406
    error_obj, = e.args
407
    if error_obj.code == 22275:  # LOB write past end
408
        print("Attempted to write past end of LOB")
409
    elif error_obj.code == 1403:  # No data found
410
        print("LOB not found")
411
    else:
412
        print(f"LOB error: {error_obj.message}")
413
        
414
except IOError as e:
415
    print(f"File I/O error: {e}")
416
    
417
finally:
418
    # Always clean up LOB resources
419
    if 'lob' in locals() and lob:
420
        try:
421
            lob.close()
422
        except:
423
            pass  # Ignore cleanup errors
424
```
425

426
## LOB Best Practices
427

428
1. **Use streaming for large data**: Always process large LOBs in chunks
429
2. **Clean up resources**: Use context managers or explicit close() calls
430
3. **Optimize chunk sizes**: Use LOB.chunksize for optimal performance
431
4. **Handle encoding properly**: Ensure correct character encoding for CLOBs
432
5. **Use temporary LOBs**: Create temporary LOBs for intermediate processing
433
6. **Batch operations**: Group LOB operations for better performance
434
7. **Monitor memory usage**: Be aware of memory consumption with large LOBs