0
# Large Objects (LOBs)
1
2
Handle Binary Large Objects (BLOB), Character Large Objects (CLOB), National Character Large Objects (NCLOB), and Binary Files (BFILE) with streaming read/write operations. LOBs provide efficient storage and manipulation of large data including documents, images, videos, and unstructured text.
3
4
## Capabilities
5
6
### LOB Class
7
8
Handle large object operations with streaming read/write capabilities for efficient memory usage.
9
10
```python { .api }
11
class LOB:
12
"""Large Object for handling BLOB, CLOB, NCLOB, and BFILE data."""
13
14
# Properties
15
type: type # DB_TYPE_BLOB, DB_TYPE_CLOB, DB_TYPE_NCLOB, or DB_TYPE_BFILE
16
17
def read(self, offset=1, amount=None) -> bytes | str:
18
"""
19
Read data from the LOB.
20
21
Parameters:
22
- offset (int): Starting position (1-based)
23
- amount (int): Number of bytes/characters to read (None for all)
24
25
Returns:
26
bytes (for BLOB/BFILE) or str (for CLOB/NCLOB): LOB data
27
"""
28
29
def write(self, data, offset=1) -> None:
30
"""
31
Write data to the LOB.
32
33
Parameters:
34
- data (bytes|str): Data to write
35
- offset (int): Starting position (1-based)
36
"""
37
38
def size(self) -> int:
39
"""
40
Get the size of the LOB.
41
42
Returns:
43
int: Size in bytes (BLOB/BFILE) or characters (CLOB/NCLOB)
44
"""
45
46
def trim(self, new_size) -> None:
47
"""
48
Trim the LOB to the specified size.
49
50
Parameters:
51
- new_size (int): New size in bytes or characters
52
"""
53
54
def getchunksize(self) -> int:
55
"""
56
Get the chunk size for optimal I/O operations.
57
58
Returns:
59
int: Optimal chunk size for read/write operations
60
"""
61
62
def open(self) -> None:
63
"""Open the LOB for read/write operations."""
64
65
def close(self) -> None:
66
"""Close the LOB and release resources."""
67
68
def getfilename(self) -> tuple:
69
"""
70
Get the directory alias and filename for BFILE LOBs.
71
72
Returns:
73
tuple: (directory_alias, filename) for BFILE LOBs
74
75
Raises:
76
TypeError: If LOB is not a BFILE
77
"""
78
79
def setfilename(self, directory_alias, filename) -> None:
80
"""
81
Set the directory alias and filename for BFILE LOBs.
82
83
Parameters:
84
- directory_alias (str): Oracle directory object name
85
- filename (str): File name within the directory
86
87
Raises:
88
TypeError: If LOB is not a BFILE
89
"""
90
91
def fileexists(self) -> bool:
92
"""
93
Check if the BFILE exists on the file system.
94
95
Returns:
96
bool: True if file exists
97
98
Raises:
99
TypeError: If LOB is not a BFILE
100
"""
101
102
def isopen(self) -> bool:
103
"""
104
Check if the LOB is currently open.
105
106
Returns:
107
bool: True if LOB is open
108
"""
109
```
110
111
### AsyncLOB Class
112
113
Asynchronous version of LOB class with async/await support for all operations.
114
115
```python { .api }
116
class AsyncLOB:
117
"""Asynchronous Large Object for handling BLOB, CLOB, NCLOB, and BFILE data."""
118
119
# Properties (same as LOB)
120
type: type
121
122
async def read(self, offset=1, amount=None) -> bytes | str:
123
"""
124
Read data from the LOB asynchronously.
125
126
Parameters:
127
- offset (int): Starting position (1-based)
128
- amount (int): Number of bytes/characters to read (None for all)
129
130
Returns:
131
bytes (for BLOB/BFILE) or str (for CLOB/NCLOB): LOB data
132
"""
133
134
async def write(self, data, offset=1) -> None:
135
"""
136
Write data to the LOB asynchronously.
137
138
Parameters:
139
- data (bytes|str): Data to write
140
- offset (int): Starting position (1-based)
141
"""
142
143
async def size(self) -> int:
144
"""
145
Get the size of the LOB asynchronously.
146
147
Returns:
148
int: Size in bytes (BLOB/BFILE) or characters (CLOB/NCLOB)
149
"""
150
151
async def trim(self, new_size) -> None:
152
"""
153
Trim the LOB to the specified size asynchronously.
154
155
Parameters:
156
- new_size (int): New size in bytes or characters
157
"""
158
159
async def open(self) -> None:
160
"""Open the LOB for read/write operations asynchronously."""
161
162
async def close(self) -> None:
163
"""Close the LOB and release resources asynchronously."""
164
```
165
166
### LOB Type Constants
167
168
Constants for identifying different LOB types.
169
170
```python { .api }
171
# LOB Type Constants
172
DB_TYPE_BLOB: type # Binary Large Object
173
DB_TYPE_CLOB: type # Character Large Object
174
DB_TYPE_NCLOB: type # National Character Large Object
175
DB_TYPE_BFILE: type # Binary File (external file reference)
176
177
# Legacy aliases
178
BLOB: type # Alias for DB_TYPE_BLOB
179
CLOB: type # Alias for DB_TYPE_CLOB
180
NCLOB: type # Alias for DB_TYPE_NCLOB
181
BFILE: type # Alias for DB_TYPE_BFILE
182
```
183
184
## Usage Examples
185
186
### Working with BLOBs
187
188
```python
189
import oracledb
190
191
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
192
193
with connection.cursor() as cursor:
194
# Create a table with BLOB column
195
cursor.execute("""
196
CREATE TABLE documents (
197
id NUMBER PRIMARY KEY,
198
name VARCHAR2(100),
199
content BLOB
200
)
201
""")
202
203
# Insert binary data
204
with open("document.pdf", "rb") as f:
205
pdf_data = f.read()
206
207
cursor.execute("""
208
INSERT INTO documents (id, name, content)
209
VALUES (:1, :2, :3)
210
""", [1, "Sample Document", pdf_data])
211
212
connection.commit()
213
214
# Read BLOB data
215
cursor.execute("SELECT content FROM documents WHERE id = :1", [1])
216
lob = cursor.fetchone()[0]
217
218
# Read entire BLOB
219
blob_data = lob.read()
220
print(f"BLOB size: {len(blob_data)} bytes")
221
222
# Write BLOB data to file
223
with open("downloaded_document.pdf", "wb") as f:
224
f.write(blob_data)
225
226
connection.close()
227
```
228
229
### Working with CLOBs
230
231
```python
232
import oracledb
233
234
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
235
236
with connection.cursor() as cursor:
237
# Create table with CLOB column
238
cursor.execute("""
239
CREATE TABLE articles (
240
id NUMBER PRIMARY KEY,
241
title VARCHAR2(200),
242
content CLOB
243
)
244
""")
245
246
# Insert large text content
247
large_text = "This is a very long article content..." * 1000
248
249
cursor.execute("""
250
INSERT INTO articles (id, title, content)
251
VALUES (:1, :2, :3)
252
""", [1, "Sample Article", large_text])
253
254
connection.commit()
255
256
# Create a temporary CLOB
257
temp_clob = connection.createlob(oracledb.DB_TYPE_CLOB)
258
temp_clob.write("Temporary CLOB content")
259
260
cursor.execute("""
261
INSERT INTO articles (id, title, content)
262
VALUES (:1, :2, :3)
263
""", [2, "Temp Article", temp_clob])
264
265
connection.commit()
266
267
# Read CLOB data in chunks
268
cursor.execute("SELECT content FROM articles WHERE id = :1", [1])
269
clob = cursor.fetchone()[0]
270
271
chunk_size = clob.getchunksize()
272
print(f"Optimal chunk size: {chunk_size}")
273
274
# Read CLOB in chunks
275
offset = 1
276
total_size = clob.size()
277
278
while offset <= total_size:
279
chunk = clob.read(offset, chunk_size)
280
print(f"Read chunk of {len(chunk)} characters starting at {offset}")
281
offset += len(chunk)
282
283
connection.close()
284
```
285
286
### Streaming LOB Operations
287
288
```python
289
import oracledb
290
291
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
292
293
with connection.cursor() as cursor:
294
# Create empty BLOB
295
cursor.execute("""
296
INSERT INTO documents (id, name, content)
297
VALUES (:1, :2, EMPTY_BLOB())
298
""", [2, "Large File"])
299
300
connection.commit()
301
302
# Get the BLOB for writing
303
cursor.execute("""
304
SELECT content FROM documents WHERE id = :1 FOR UPDATE
305
""", [2])
306
307
blob = cursor.fetchone()[0]
308
309
# Stream write large file
310
with open("large_file.bin", "rb") as f:
311
chunk_size = blob.getchunksize()
312
offset = 1
313
314
while True:
315
chunk = f.read(chunk_size)
316
if not chunk:
317
break
318
319
blob.write(chunk, offset)
320
offset += len(chunk)
321
print(f"Written {len(chunk)} bytes at offset {offset - len(chunk)}")
322
323
connection.commit()
324
print(f"Final BLOB size: {blob.size()} bytes")
325
326
connection.close()
327
```
328
329
### Working with BFILEs
330
331
```python
332
import oracledb
333
334
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
335
336
with connection.cursor() as cursor:
337
# Create directory object (requires DBA privileges)
338
cursor.execute("CREATE OR REPLACE DIRECTORY FILE_DIR AS '/path/to/files'")
339
340
# Create table with BFILE column
341
cursor.execute("""
342
CREATE TABLE file_references (
343
id NUMBER PRIMARY KEY,
344
name VARCHAR2(100),
345
file_ref BFILE
346
)
347
""")
348
349
# Create BFILE reference
350
cursor.execute("""
351
INSERT INTO file_references (id, name, file_ref)
352
VALUES (:1, :2, BFILENAME('FILE_DIR', 'example.txt'))
353
""", [1, "External File"])
354
355
connection.commit()
356
357
# Read BFILE
358
cursor.execute("SELECT file_ref FROM file_references WHERE id = :1", [1])
359
bfile = cursor.fetchone()[0]
360
361
# Check if file exists
362
if bfile.fileexists():
363
# Get file information
364
directory, filename = bfile.getfilename()
365
print(f"File: {directory}/{filename}")
366
367
# Open and read BFILE
368
bfile.open()
369
file_content = bfile.read()
370
print(f"File content: {file_content.decode('utf-8')}")
371
bfile.close()
372
else:
373
print("File does not exist")
374
375
connection.close()
376
```
377
378
### Async LOB Operations
379
380
```python
381
import asyncio
382
import oracledb
383
384
async def main():
385
connection = await oracledb.connect_async(user="hr", password="password", dsn="localhost/xepdb1")
386
387
async with connection.cursor() as cursor:
388
# Create table
389
await cursor.execute("""
390
CREATE TABLE async_docs (
391
id NUMBER PRIMARY KEY,
392
content CLOB
393
)
394
""")
395
396
# Insert large text
397
large_text = "Async CLOB content " * 10000
398
await cursor.execute("""
399
INSERT INTO async_docs (id, content) VALUES (:1, :2)
400
""", [1, large_text])
401
402
await connection.commit()
403
404
# Read CLOB asynchronously
405
await cursor.execute("SELECT content FROM async_docs WHERE id = :1", [1])
406
result = await cursor.fetchone()
407
clob = result[0]
408
409
# Async read
410
content = await clob.read()
411
print(f"Async read CLOB size: {len(content)} characters")
412
413
# Async size
414
size = await clob.size()
415
print(f"CLOB size: {size}")
416
417
await connection.close()
418
419
asyncio.run(main())
420
```
421
422
### LOB Manipulation
423
424
```python
425
import oracledb
426
427
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
428
429
with connection.cursor() as cursor:
430
# Create table with CLOB
431
cursor.execute("""
432
CREATE TABLE text_docs (
433
id NUMBER PRIMARY KEY,
434
content CLOB
435
)
436
""")
437
438
# Insert initial content
439
cursor.execute("""
440
INSERT INTO text_docs (id, content)
441
VALUES (:1, :2)
442
""", [1, "Initial content for manipulation"])
443
444
connection.commit()
445
446
# Get CLOB for manipulation
447
cursor.execute("""
448
SELECT content FROM text_docs WHERE id = :1 FOR UPDATE
449
""", [1])
450
451
clob = cursor.fetchone()[0]
452
453
# Append content
454
current_size = clob.size()
455
clob.write("\nAppended content", current_size + 1)
456
457
# Insert content at specific position
458
clob.write(" [INSERTED] ", 10)
459
460
# Read modified content
461
final_content = clob.read()
462
print(f"Modified content: {final_content}")
463
464
# Trim CLOB
465
clob.trim(50) # Keep only first 50 characters
466
trimmed_content = clob.read()
467
print(f"Trimmed content: {trimmed_content}")
468
469
connection.commit()
470
471
connection.close()
472
```
473
474
### LOB Best Practices
475
476
```python
477
import oracledb
478
479
connection = oracledb.connect(user="hr", password="password", dsn="localhost/xepdb1")
480
481
def efficient_lob_processing():
482
"""Demonstrate efficient LOB processing techniques."""
483
484
with connection.cursor() as cursor:
485
# Use optimal chunk size for I/O
486
cursor.execute("SELECT content FROM large_documents WHERE id = :1", [1])
487
blob = cursor.fetchone()[0]
488
489
# Get optimal chunk size
490
chunk_size = blob.getchunksize()
491
492
# Process BLOB in chunks to manage memory
493
total_size = blob.size()
494
processed = 0
495
offset = 1
496
497
while processed < total_size:
498
# Read chunk
499
chunk = blob.read(offset, chunk_size)
500
501
# Process chunk (example: calculate checksum)
502
# process_chunk(chunk)
503
504
processed += len(chunk)
505
offset += len(chunk)
506
507
print(f"Processed {processed}/{total_size} bytes ({processed/total_size*100:.1f}%)")
508
509
def temp_lob_usage():
510
"""Demonstrate temporary LOB creation and usage."""
511
512
# Create temporary BLOB
513
temp_blob = connection.createlob(oracledb.DB_TYPE_BLOB)
514
515
try:
516
# Write data to temporary BLOB
517
temp_blob.write(b"Temporary binary data")
518
519
# Use temporary BLOB in SQL
520
with connection.cursor() as cursor:
521
cursor.execute("""
522
INSERT INTO temp_storage (id, data) VALUES (:1, :2)
523
""", [1, temp_blob])
524
525
connection.commit()
526
527
finally:
528
# Always close temporary LOBs
529
temp_blob.close()
530
531
# Run examples
532
efficient_lob_processing()
533
temp_lob_usage()
534
535
connection.close()
536
```