Oracle 9i. Application Developers Guide - Large Objects (LOBs)

Send Us Your Comments......Page 31
Preface......Page 33
Structure......Page 34
Related Documents......Page 37
Conventions......Page 39
Documentation Accessibility......Page 42
LOB Features Introduced with Oracle9i, Release 1 (9.0.1)......Page 43
LOB Features Introduced with Oracle8i, Release 8.1.5......Page 46
1 Introduction to LOBs......Page 47
Unstructured Data in System Files Need Accessing from the Database......Page 48
Oracle Text (interMedia Text) Indexing Supports Searching Content in XML Elements......Page 49
Why Not Use LONGs?......Page 50
SQL Semantics Support for LOBs......Page 51
Extensible Indexing on LOBs......Page 52
Extensible Optimizer......Page 53
LOB "Demo" Directory......Page 54
Compatibility and Migration Issues......Page 55
Examples in This Manual Use the Multimedia Schema......Page 56
2 Basic LOB Components......Page 57
External LOBs (BFILEs)......Page 58
Reference Semantics......Page 59
CLOB, NCLOB Values are Stored Using 2 Byte Unicode for Varying-Width Character Sets......Page 60
Converting Between Client Character Set and UCS-2......Page 61
Internal LOBs......Page 62
External LOBs......Page 63
SELECTing a LOB......Page 64
Setting an Internal LOB to NULL......Page 65
Initializing LOBs Example Using Table Multimedia_tab......Page 66
Initializing External LOBs to NULL or a File Name......Page 67
3 LOB Support in Different Programmatic Environments......Page 69
Eight Programmatic Environments Operate on LOBs......Page 70
Comparing the LOB Interfaces......Page 71
Provide a LOB Locator Before Invoking the DBMS_LOB Routine......Page 74
PL/SQL Functions and Procedures that Operate on BLOBs, CLOBs, NCLOBs, and BFILEs......Page 75
PL/SQL Functions/Procedures To Read or Examine Internal and External LOB Values......Page 76
PL/SQL Functions/Procedures To Open and Close Internal and External LOBs......Page 77
Set CSID Parameter To OCI_UCS2ID to Read/Write in UCS2......Page 78
Varying-Width Character Set Rules......Page 79
OCI LOB Examples......Page 80
OCI Functions that Operate on BLOBs, BLOBs, NCLOBs, and BFILEs......Page 81
OCI Functions For Temporary LOBs......Page 82
OCI LOB-Buffering Functions......Page 83
OCI Example — Is the LOB Open: main() and seeIfLOBIsOpen......Page 84
Using C++ (OCCI) To Work With LOBs......Page 89
OCCIBlob Class......Page 90
Varying-Width Character Set Rules......Page 91
Loading from Files with OCCIClob.copy() and OCCIBlob.copy(): Specify the Amount Parameter to be L.........Page 92
OCCI Methods To Modify Internal LOB (BLOB, CLOB, and NCLOB) Values......Page 93
OCCI Read-Only Methods For BFILEs......Page 94
OCCI Methods To Open and Close Internal and External LOBs......Page 95
Pro*C/C++ Statements that Operate on BLOBs, CLOBs, NCLOBs, and BFILEs......Page 96
Pro*C/C++ Embedded SQL Statements To Read or Examine Internal and External LOB Values......Page 97
Pro*C/C++ Embedded SQL Statements For LOB Locators......Page 98
Pro*C/C++ Embedded SQL Statements To Open and Close Internal LOBs and External LOBs (BFILEs)......Page 99
Pro*COBOL Statements that Operate on BLOBs, CLOBs, NCLOBs, and BFILEs......Page 100
Pro*COBOL Embedded SQL Statements To Modify Internal BLOB, CLOB, and NCLOB Values......Page 101
Pro*COBOL Embedded SQL Statements For LOB Locators......Page 102
Pro*COBOL Embedded SQL Statements To Open and Close Internal LOBs and BFILEs......Page 103
Further Information......Page 104
Example of OraBlob and OraBfile......Page 105
OO4O Methods and Properties to Access Data Stored in BLOBs, CLOBs, NCLOBs, and BFILEs......Page 106
OO4O Methods To Modify BLOB, CLOB, and NCLOB Values......Page 108
OO4O Methods To Open and Close External LOBs (BFILEs)......Page 109
OO4O Read-Only Methods For External Lobs (BFILEs)......Page 110
OO4O Properties For Operating on External LOBs (BFILEs)......Page 111
BLOB, CLOB, and BFILE Classes......Page 112
JDBC Methods for Operating on LOBs......Page 113
JDBC oracle.sql.blob Methods To Read or Examine BLOB Values......Page 115
JDBC oracle.sql.CLOB Methods To Read or Examine CLOB Value......Page 116
JDBC oracle.sql.bfile Methods To Read or Examine External LOB (BFILE) Values......Page 117
JDBC Temporary LOB APIs......Page 118
JDBC: Opening and Closing LOBs......Page 119
Opening the BLOB......Page 120
JDBC: Opening and Closing CLOBs......Page 121
Checking if the CLOB is Open......Page 122
Opening BFILEs......Page 123
Closing the BFILE......Page 124
Usage Example (OpenCloseLob.java)......Page 125
JDBC: Trimming BLOBs......Page 127
New JDBC BLOB Streaming APIs......Page 128
New CLOB Streaming APIs......Page 129
New BFILE Streaming APIs......Page 130
JDBC BFILE Streaming Example (NewStreamLob.java)......Page 131
JDBC and Empty LOBs......Page 135
OLEDB (Oracle Provider for OLEDB — OraOLEDB)......Page 137
4 Managing LOBs......Page 139
Using SQL DML for Basic Operations on LOBs......Page 140
Oracle8 Release 8.0.4.3......Page 141
Managing Temporary LOBs......Page 142
Inline versus Out-of-Line LOBs......Page 143
SQL*Loader Performance: Loading Into Internal LOBs......Page 144
Data File (sample.dat)......Page 145
Control File......Page 146
Loading Out-Of-Line LOB Data......Page 147
Secondary Data File (SecondStory.txt)......Page 148
Loading Out-of-Line LOB Data in Delimited Fields......Page 149
Control File......Page 150
SQL*Loader LOB Loading Tips......Page 151
LOB Restrictions......Page 152
Removed Restrictions......Page 155
5 Large Objects: Advanced Topics......Page 157
A Selected Locator Becomes a Read Consistent Locator......Page 158
Read Consistent Locators Provide Same LOB Value Regardless of When the SELECT Occurs......Page 159
Example......Page 160
Example of Updating a LOB Using SQL DML and DBMS_LOB......Page 162
Example......Page 163
Example of Using One Locator to Update the Same LOB Value......Page 164
Example......Page 165
Example of Updating a LOB with a PL/SQL (DBMS_LOB) Bind Variable......Page 166
Example......Page 167
LOB Locators Cannot Span Transactions......Page 169
Example of Locator Not Spanning a Transaction......Page 170
Transaction IDs: Reading and Writing to a LOB Using Locators......Page 171
Case 2:......Page 172
Case 4:......Page 173
LOBs in the Object Cache......Page 174
Guidelines for Using LOB Buffering......Page 175
LOB Buffer Physical Structure......Page 177
Example of Using the LOB Buffering System (LBS)......Page 178
Flushing the LOB Buffer......Page 179
Flushing the Updated LOB......Page 180
Saving Locator State to Avoid a Reselect......Page 181
OCI Example of LOB Buffering......Page 182
Creating a Varray Containing LOB References: Example......Page 185
Example of LOB Columns in Partitioned Index-Organized Tables......Page 186
Non-Supported Column Types......Page 187
Hash Partitioned Index-Organized Table LOB Restrictions......Page 188
6 Frequently Asked Questions about LOBs......Page 189
Answer......Page 192
Question......Page 193
Answer......Page 194
Answer......Page 195
Answer......Page 196
Answer......Page 197
Answer......Page 198
Answer......Page 199
Question......Page 200
Answer......Page 201
Answer......Page 202
Answer......Page 203
Answer......Page 206
Question......Page 207
Answer......Page 208
Question......Page 209
Answer......Page 210
Question......Page 211
Answer......Page 212
Answer......Page 213
Question 1......Page 214
Answer 2......Page 215
Answer......Page 216
Answer......Page 217
Question......Page 218
Answer......Page 219
Answer......Page 220
7 Modeling and Design......Page 221
LOBs Compared to LONG and LONG RAW Types......Page 222
Selecting a Table Architecture......Page 223
EMPTY_CLOB() or EMPTY_BLOB() Column Storage: LOB Locator is Stored......Page 224
Defining Tablespace and Storage Example1......Page 225
TABLESPACE and LOB Index......Page 226
PCTVERSION......Page 227
CACHE / NOCACHE / CACHE READS: LOB Values and Buffer Cache......Page 228
LOBs Will Always Generate Undo for LOB Index Pages......Page 229
Set INITIAL and NEXT to Larger than CHUNK......Page 230
Guidelines for ENABLE or DISABLE STORAGE IN ROW......Page 231
Example 1: Creating a Tablespace and Table to Store Gigabyte LOBs......Page 232
LOB Locators and Transaction Boundaries......Page 233
Binds of More Than 4,000 Bytes ... No HEX to RAW or RAW to HEX Conversion......Page 234
4,000 Byte Limit On Results of SQL Operator......Page 235
Example: PL/SQL - Using Binds of More Than 4,000 Bytes in INSERT and UPDATE......Page 236
Example: PL/SQL - Binds of More Than 4,000 Bytes -- Inserts Not Supported Because Hex to Raw/Raw .........Page 237
Example: C (OCI) - Binds of More than 4,000 Bytes For INSERT and UPDATE......Page 238
Wrap LOB Operations Inside an OPEN / CLOSE Call......Page 241
Do Not Open or Close Same LOB Twice!......Page 242
Example 2: Incorrect Use of OPEN/CLOSE Calls to a LOB in a Transaction......Page 243
Example of Index Organized Table (IOT) with LOB Columns......Page 244
Manipulating LOBs in Partitioned Tables......Page 246
Creating and Partitioning a Table Containing LOB Data......Page 248
Creating an Index on a Table Containing LOB Columns......Page 250
Splitting Partitions Containing LOBs......Page 251
Functional Indexes on LOB Columns......Page 252
Improved LOB Usability: You can Now Access LOBs Using SQL “Character” Functions......Page 253
PL/SQL Relational Operators Now Allowed for LOBs......Page 254
Using SQL Functions and Operators for VARCHAR2s on CLOBs......Page 255
UNICODE Support for VARCHAR2 and CLOB......Page 259
Defining CHAR Buffer on CLOB......Page 260
Returning VARCHAR2s......Page 261
Returned LOB is a Temporary LOB Locator......Page 262
IS [NOT] NULL in VARCHAR2s and CLOBs......Page 263
SQL DML Changes For LOBs......Page 264
PL/SQL Statements and Variables: New Semantics Changes......Page 265
PL/SQL Example 1: Prior Release SQL Interface for a CLOB/VARCHAR2 Application......Page 266
PL/SQL Example 3: Defining a CLOB Variable on a VARCHAR2......Page 267
VARCHAR2 and CLOB in PL/SQL Built-in Functions......Page 268
PL/SQL Example 5: Change in Locator-Data Linkage......Page 269
PL/SQL Example 6: Freeing Temporary LOBs Automatically and Manually......Page 270
Varying-Width Character Sets: VARCHAR2s and CLOBs......Page 271
Inserting More than 4K Bytes Data Into LOB Columns......Page 272
Performance Measurement......Page 273
User-Defined Aggregates and LOBs......Page 274
UDAGs: DML and Query Support......Page 275
8 Migrating From LONGs to LOBs......Page 277
Using the LONG-to-LOB API Results in an Easy Migration......Page 278
Binds in OCI......Page 279
OCI Functions Allow Piecewise and Array INSERT, UPDATE, or Fetch on LOBs......Page 280
Assignment and Parameters Passing (PL/SQL)......Page 281
Migrating LONGs to LOBs: Using ALTER TABLE to Change LONG Column to LOB Types......Page 282
All Constraints of LONG Column are Maintained......Page 284
Rebuilding Indexes After a LONG to LOB Migration......Page 285
Replication......Page 286
Triggers......Page 287
NULL LOBs Versus Zero Length LOBs......Page 288
Guidelines for Using LONG-to-LOB API for LOBs with OCI......Page 289
Using Piecewise INSERTs and UPDATEs with Polling......Page 290
Simple Fetch in One Piece......Page 291
Piecewise with Callback......Page 292
UPDATEs......Page 293
Variable Assignment Between CLOB/CHAR and BLOB/RAW......Page 294
VARCHAR2 and CLOB in PL/SQL Built-In Functions......Page 295
PL/SQL and C Binds from OCI......Page 296
From SQL......Page 297
From PL/SQL......Page 298
Overloading with Anchored Types......Page 299
Using utldtree.sql to Determine Where Your Application Needs Change......Page 300
To Convert LONG to CLOB, Use ALTER TABLE......Page 301
Converting LONG to LOB Example 1: More than 4K Binds and Simple INSERTs......Page 302
Converting LONG to LOB Example 2: Piecewise INSERT with Polling......Page 303
Converting LONG to LOB Example 3: Piecewise INSERT with Callback......Page 304
Converting LONG to LOB Example 4: Array insert......Page 306
Converting LONG to LOB Example 6: Piecewise Fetch with Polling......Page 308
Converting LONG to LOB Example 7: Piecewise Fetch with Callback......Page 309
Converting LONG to LOB Example 8: Array Fetch......Page 311
Converting LONG to LOB Example 9: Using PL/SQL in INSERT, UPDATE and SELECT......Page 312
Converting LONG to LOB Example 10: Assignments and Parameter Passing in PL/SQL......Page 313
Converting LONG to LOB Example 12: Using PL/SQL Binds from OCI on LOBs......Page 314
Calling PL/SQL Outbinds in the "call foo(:1);" Manner......Page 315
Converting LONG to LOB Example 13: Calling PL/SQL and C Procedures from PL/SQL......Page 316
PL/SQL Interface......Page 317
Compatibility and Migration......Page 318
Answer......Page 319
Answer......Page 320
Answer......Page 321
9 LOBS: Best Practices......Page 323
Loading XML Documents Into LOBs With SQL*Loader......Page 324
LOB Performance Guidelines......Page 327
Temporary LOB Performance Guidelines......Page 328
The Correct Procedure......Page 331
Preventing Generation of Redo Space During Migration......Page 332
10 Internal Persistent LOBs......Page 333
Use Case Model: Internal Persistent LOBs Operations......Page 334
Creating Tables Containing LOBs......Page 339
Creating a Table Containing One or More LOB Columns......Page 341
SQL: Create a Table Containing One or More LOB Columns......Page 343
Creating a Table Containing an Object Type with a LOB Attribute......Page 346
SQL: Creating a Table Containing an Object Type with a LOB Attribute......Page 348
Creating a Nested Table Containing a LOB......Page 351
SQL: Creating a Nested Table Containing a LOB......Page 353
Inserting One or More LOB Values into a Row......Page 354
Inserting a LOB Value using EMPTY_CLOB() or EMPTY_BLOB()......Page 356
SQL: Inserting a Value Using EMPTY_CLOB() / EMPTY_BLOB()......Page 358
Java (JDBC): Inserting a Value Using EMPTY_CLOB() / EMPTY_BLOB()......Page 359
Inserting a Row by Selecting a LOB From Another Table......Page 360
SQL: Inserting a Row by Selecting a LOB from Another Table......Page 361
Inserting a Row by Initializing a LOB Locator Bind Variable......Page 362
C (OCI): Inserting a Row by Initializing a LOB Locator Bind Variable......Page 364
COBOL (Pro*COBOL): Inserting a Row by Initializing a LOB Locator Bind Variable......Page 366
C/C++ (ProC/C++): Inserting a Row by Initializing a LOB Locator Bind Variable......Page 367
Visual Basic (OO4O): Inserting a Row by Initializing a LOB Locator Bind Variable......Page 368
Java (JDBC): Inserting a Row by Initializing a LOB Locator Bind Variable......Page 369
Loading Initial Data into a BLOB, CLOB, or NCLOB......Page 371
Loading a LOB with BFILE Data......Page 373
PL/SQL (DBMS_LOB Package): Loading a LOB with Data from a BFILE......Page 375
C (OCI): Loading a LOB with Data from a BFILE......Page 376
COBOL (Pro*COBOL): Loading a LOB with Data from a BFILE......Page 378
C/C++ (ProC/C++): Loading a LOB with Data from a BFILE......Page 379
Java (JDBC): Loading a LOB with Data from a BFILE......Page 381
Open: Checking If a LOB Is Open......Page 384
PL/SQL (DBMS_LOB Package): Checking if a LOB is Open......Page 385
C (OCI): Checking if a LOB is Open......Page 386
COBOL (Pro*COBOL): Checking if a LOB is Open......Page 387
C/C++ (ProC/C++): Checking if a LOB is Open......Page 388
Checking If a BLOB is Opened......Page 390
LONGs to LOBs......Page 393
LONG to LOB Migration Using the LONG-to-LOB API......Page 394
To Convert LONG to CLOB, Use ALTER TABLE......Page 395
C (OCI): LONG to LOB Migration Using the LONG-to-LOB API: More than 4K Binds and Simple INSERTs......Page 396
LONG to LOB Copying, Using the TO_LOB Operator......Page 398
SQL: Copying LONGs to LOBs Using TO_LOB Operator......Page 399
Checking Out a LOB......Page 402
PL/SQL (DBMS_LOB Package): Checking Out a LOB......Page 404
C (OCI): Checking Out a LOB......Page 405
COBOL (Pro*COBOL): Checking Out a LOB......Page 407
C/C++ (ProC/C++): Checking Out a LOB......Page 409
Java (JDBC): Checking Out a LOB......Page 411
Checking In a LOB......Page 414
PL/SQL (DBMS_LOB Package): Checking in a LOB......Page 416
C (OCI): Checking in a LOB......Page 417
COBOL (Pro*COBOL): Checking in a LOB......Page 420
C/C++ (ProC/C++): Checking in a LOB......Page 423
Visual Basic (OO4O): Checking in a LOB......Page 425
Java (JDBC): Checking in a LOB......Page 426
Displaying LOB Data......Page 429
PL/SQL (DBMS_LOB Package): Displaying LOB Data......Page 431
C (OCI): Displaying LOB Data......Page 432
COBOL (Pro*COBOL): Displaying LOB Data......Page 434
C/C++ (ProC/C++): Displaying LOB Data......Page 435
Java (JDBC): Displaying LOB Data......Page 437
Reading Data from a LOB......Page 440
PL/SQL (DBMS_LOB Package): Reading Data from a LOB......Page 443
C (OCI): Reading Data from a LOB......Page 444
COBOL (Pro*COBOL): Reading Data from a LOB......Page 446
C/C++ (Pro*C/C++): Reading Data from a LOB......Page 447
Java (JDBC): Reading Data from a LOB......Page 449
Reading a Portion of the LOB (substr)......Page 451
PL/SQL (DBMS_LOB Package): Reading a Portion of the LOB (substr)......Page 453
COBOL (Pro*COBOL): Reading a Portion of the LOB (substr)......Page 454
C/C++ (Pro*C/C++): Reading a Portion of the LOB (substr)......Page 455
Visual Basic (OO4O): Reading a Portion of the LOB (substr)......Page 456
Java (JDBC): Reading a Portion of the LOB (substr)......Page 457
Comparing All or Part of Two LOBs......Page 459
COBOL (Pro*COBOL): Comparing All or Part of Two LOBs......Page 461
C/C++ (Pro*C/C++): Comparing All or Part of Two LOBs......Page 463
Visual Basic (OO4O): Comparing All or Part of Two LOBs......Page 464
Java (JDBC): Comparing All or Part of Two LOBs......Page 465
Patterns: Checking for Patterns in the LOB (instr)......Page 467
PL/SQL (DBMS_LOB Package): Checking for Pattern in the LOB (instr)......Page 469
COBOL (Pro*COBOL): Checking for Patterns in the LOB (instr)......Page 470
C/C++ (Pro*C/C++): Checking for Patterns in the LOB (instr)......Page 471
Java (JDBC): Checking for Patterns in the LOB (instr)......Page 472
Length: Determining the Length of a LOB......Page 475
C (OCI): Determining the Length of a LOB......Page 477
COBOL (Pro*COBOL): Determining the Length of a LOB......Page 479
C/C++ (Pro*C/C++): Determining the Length of a LOB......Page 480
Java (JDBC): Determining the Length of a LOB......Page 481
Copying All or Part of One LOB to Another LOB......Page 484
PL/SQL (DBMS_LOB Package): Copying All or Part of One LOB to Another LOB......Page 486
C (OCI): Copying All or Part of One LOB to Another LOB......Page 487
COBOL (Pro*COBOL): Copying All or Part of One LOB to Another LOB......Page 489
C/C++ (Pro*C/C++): Copy All or Part of a LOB to Another LOB......Page 491
Visual Basic (OO4O): Copying All or Part of One LOB to Another LOB......Page 492
Java (JDBC): Copying All or Part of One LOB to Another LOB......Page 493
Copying a LOB Locator......Page 495
C (OCI): Copying a LOB Locator......Page 497
COBOL (Pro*COBOL): Copying a LOB Locator......Page 499
C/C++ (Pro*C/C++): Copying a LOB Locator......Page 500
Java (JDBC): Copying a LOB Locator......Page 501
Equality: Checking If One LOB Locator Is Equal to Another......Page 504
C (OCI): Checking If One LOB Locator Is Equal to Another......Page 505
C/C++ (Pro*C/C++): Checking If One LOB Locator Is Equal to Another......Page 507
Java (JDBC): Checking If One LOB Locator Is Equal to Another......Page 508
Initialized Locator: Checking If a LOB Locator Is Initialized......Page 511
C (OCI): Checking If a LOB Locator Is Initialized......Page 512
C/C++ (Pro*C/C++): Checking If a LOB Locator Is Initialized......Page 514
Character Set ID: Determining Character Set ID......Page 516
C (OCI): Determining Character Set ID......Page 517
Character Set Form: Determining Character Set Form......Page 520
C (OCI): Determining Character Set Form......Page 521
Appending One LOB to Another......Page 524
PL/SQL (DBMS_LOB Package): Appending One LOB to Another......Page 526
C (OCI): Appending One LOB to Another......Page 527
COBOL (Pro*COBOL): Appending One LOB to Another......Page 529
C/C++ (Pro*C/C++): Appending One LOB to Another......Page 530
Visual Basic (OO4O): Appending One LOB to Another......Page 531
Java (JDBC): Appending One LOB to Another......Page 532
Append-Writing to the End of a LOB......Page 534
PL/SQL (DBMS_LOB Package): Writing to the End of (Appending to) a LOB......Page 536
C (OCI): Writing to the End of (Appending to) a LOB......Page 537
COBOL (Pro*COBOL): Writing to the End of (Appending to) a LOB......Page 539
C/C++ (Pro*C/C++): Writing to the End of (Appending to) a LOB......Page 540
Java (JDBC): Writing to the End of (Append-Write to) a LOB......Page 541
Writing Data to a LOB......Page 543
PL/SQL (DBMS_LOB Package): Writing Data to a LOB......Page 546
C (OCI): Writing Data to a LOB......Page 548
COBOL (Pro*COBOL): Writing Data to a LOB......Page 551
C/C++ (Pro*C/C++): Writing Data to a LOB......Page 554
Visual Basic (OO4O):Writing Data to a LOB......Page 556
Java (JDBC): Writing Data to a LOB......Page 557
Trimming LOB Data......Page 560
PL/SQL (DBMS_LOB Package): Trimming LOB Data......Page 562
C (OCI): Trimming LOB Data......Page 563
COBOL (Pro*COBOL): Trimming LOB Data......Page 564
C/C++ (Pro*C/C++): Trimming LOB Data......Page 565
Visual Basic (OO4O): Trimming LOB Data......Page 567
Java (JDBC): Trimming LOB Data......Page 568
Erasing Part of a LOB......Page 573
PL/SQL (DBMS_LOB Package): Erasing Part of a LOB......Page 575
C (OCI): Erasing Part of a LOB......Page 576
COBOL (Pro*COBOL): Erasing Part of a LOB......Page 577
C/C++ (Pro*C/C++): Erasing Part of a LOB......Page 578
Visual Basic (OO4O): Erasing Part of a LOB......Page 579
Java (JDBC): Erasing Part of a LOB......Page 580
Enabling LOB Buffering......Page 583
COBOL (Pro*COBOL): Enabling LOB Buffering......Page 585
C/C++ (Pro*C/C++): Enabling LOB Buffering......Page 587
Visual Basic (OO4O): Enabling LOB Buffering......Page 588
Flushing the Buffer......Page 589
COBOL (Pro*COBOL): Flushing the Buffer......Page 591
C/C++ (Pro*C/C++): Flushing the Buffer......Page 593
Visual Basic (OO4O): Flushing the Buffer......Page 594
Disabling LOB Buffering......Page 595
C (OCI): Disabling LOB Buffering......Page 597
COBOL (Pro*COBOL): Disabling LOB Buffering......Page 599
C/C++ (Pro*C/C++): Disabling LOB Buffering......Page 601
Visual Basic (OO4O): Disabling LOB Buffering......Page 602
Three Ways to Update a LOB or Entire LOB Data......Page 603
Updating a LOB with EMPTY_CLOB() or EMPTY_BLOB()......Page 604
For Binds of More Than 4,000 Bytes......Page 605
SQL: UPDATE a LOB with EMPTY_CLOB() or EMPTY_BLOB()......Page 606
Updating a Row by Selecting a LOB From Another Table......Page 607
SQL: Update a Row by Selecting a LOB From Another Table......Page 608
Updating by Initializing a LOB Locator Bind Variable......Page 609
SQL: Updating by Initializing a LOB Locator Bind Variable......Page 610
C (OCI): Updating by Initializing a LOB Locator Bind Variable......Page 611
COBOL (Pro*COBOL): Updating by Initializing a LOB Locator Bind Variable......Page 612
C/C++ (Pro*C/C++): Updating by Initializing a LOB Locator Bind Variable......Page 614
Java (JDBC): Updating by Initializing a LOB Locator Bind Variable......Page 615
Deleting the Row of a Table Containing a LOB......Page 618
SQL: Delete a LOB......Page 619
11 Temporary LOBs......Page 621
Use Case Model: Internal Temporary LOBs......Page 622
Temporary LOB Locators Can be IN Values......Page 626
Temporary LOB Data is Stored in Temporary Tablespace......Page 627
Temporary Tablespace......Page 628
Locators and Semantics......Page 629
Features Specific to Temporary LOBs......Page 630
NOCOPY Restrictions......Page 632
Using JDBC and Temporary CLOBs......Page 633
Creating a Temporary LOB......Page 635
C (OCI): Creating a Temporary LOB......Page 637
COBOL (Pro*COBOL): Creating a Temporary LOB......Page 639
C/C++ (Pro*C/C++): Creating a Temporary LOB......Page 641
Java (JDBC): Creating a Temporary BLOB......Page 642
Java (JDBC): Creating a Temporary CLOB......Page 643
Checking If a LOB is Temporary......Page 644
PL/SQL (DBMS_LOB Package): Checking If a LOB is Temporary......Page 645
C (OCI): Checking If a LOB is Temporary......Page 646
COBOL (Pro*COBOL): Checking If a LOB is Temporary......Page 647
C/C++ (Pro*C/C++): Checking If a LOB is Temporary......Page 648
Java (JDBC): Checking if a BLOB is Temporary......Page 649
Java (JDBC): Checking if a CLOB is Temporary......Page 650
Freeing a Temporary LOB......Page 652
PL/SQL (DBMS_LOB Package): Freeing a Temporary LOB......Page 653
C (OCI): Freeing a Temporary LOB......Page 654
COBOL (Pro*COBOL): Freeing a Temporary LOB......Page 655
C/C++ (Pro*C/C++): Freeing a Temporary LOB......Page 656
Java (JDBC): Freeing a Temporary BLOB......Page 657
Java (JDBC): Creating and Freeing a Temporary CLOB Using TemporaryClob.java......Page 658
Loading a Temporary LOB with Data from a BFILE......Page 659
C (OCI): Loading a Temporary LOB with Data from a BFILE......Page 661
COBOL (Pro*COBOL): Loading a Temporary LOB with Data from a BFILE......Page 663
C/C++ (Pro*C/C++): Loading a Temporary LOB with Data from a BFILE......Page 665
Determining If a Temporary LOB Is Open......Page 667
PL/SQL: Determining if a Temporary LOB is Open......Page 668
C (OCI) Determining if a Temporary LOB is Open......Page 669
COBOL (Pro*COBOL): Determining if a Temporary LOB is Open......Page 670
C/C++ (Pro*C/C++): Determining if a Temporary LOB is Open......Page 672
Displaying Temporary LOB Data......Page 674
PL/SQL (DBMS_LOB Package): Displaying Temporary LOB Data......Page 676
C (OCI): Displaying Temporary LOB Data......Page 677
COBOL (Pro*COBOL): Displaying Temporary LOB Data......Page 680
C/C++ (Pro*C/C++): Displaying Temporary LOB Data......Page 682
Reading Data from a Temporary LOB......Page 684
PL/SQL (DBMS_LOB Package): Reading Data from a Temporary LOB......Page 686
C (OCI): Reading Data from a Temporary LOB......Page 687
COBOL (Pro*COBOL): Reading Data from a Temporary LOB......Page 690
C/C++ (Pro*C/C++): Reading Data from a Temporary LOB......Page 692
Reading Portion of Temporary LOB (Substr)......Page 695
COBOL (Pro*COBOL): Reading a Portion of Temporary LOB (substr)......Page 697
C/C++ (Pro*C/C++): Reading a Portion of Temporary LOB (substr)......Page 699
Comparing All or Part of Two (Temporary) LOBs......Page 702
PL/SQL (DBMS_LOB Package): Comparing All or Part of Two (Temporary) LOBs......Page 704
COBOL (Pro*COBOL): Comparing All or Part of Two (Temporary) LOBs......Page 705
C/C++ (Pro*C/C++): Comparing All or Part of Two (Temporary) LOBs......Page 707
Determining If a Pattern Exists in a Temporary LOB (instr)......Page 709
COBOL (Pro*COBOL): Determining If a Pattern Exists in a Temporary LOB (instr)......Page 711
C/C++ (Pro*C/C++): Determining If a Pattern Exists in a Temporary LOB (instr)......Page 714
Finding the Length of a Temporary LOB......Page 716
C (OCI): Finding the Length of a Temporary LOB......Page 718
COBOL (Pro*COBOL): Finding the Length of a Temporary LOB......Page 720
C/C++ (Pro*C/C++): Finding the Length of a Temporary LOB......Page 722
Copying All or Part of One (Temporary) LOB to Another......Page 725
PL/SQL (DBMS_LOB Package): Copying All or Part of One (Temporary) LOB to Another......Page 727
C (OCI): Copying All or Part of One (Temporary) LOB to Another......Page 728
COBOL (Pro*COBOL): Copying All or Part of One (Temporary) LOB to Another......Page 731
C/C++ (Pro*C/C++): Copying All or Part of One (Temporary) LOB to Another......Page 733
Copying a LOB Locator for a Temporary LOB......Page 735
PL/SQL (DBMS_LOB Package): Copying a LOB Locator for a Temporary LOB......Page 737
C (OCI): Copying a LOB Locator for a Temporary LOB......Page 738
COBOL (Pro*COBOL): Copying a LOB Locator for a Temporary LOB......Page 740
C/C++ (Pro*C/C++): Copying a LOB Locator for a Temporary LOB......Page 742
Is One Temporary LOB Locator Equal to Another......Page 745
C (OCI): Is One LOB Locator for a Temporary LOB Equal to Another......Page 746
C/C++ (Pro*C/C++): Is One LOB Locator for a Temporary LOB Equal to Another......Page 748
Determining if a LOB Locator for a Temporary LOB Is Initialized......Page 750
C (OCI): Determining If a LOB Locator for a Temporary LOB Is Initialized......Page 751
C/C++ (Pro*C/C++): Determining If a LOB Locator for a Temporary LOB Is Initialized......Page 752
Finding Character Set ID of a Temporary LOB......Page 754
C (OCI): Finding Character Set ID of a Temporary LOB......Page 755
Finding Character Set Form of a Temporary LOB......Page 757
C (OCI): Finding Character Set Form of a Temporary LOB......Page 758
Appending One (Temporary) LOB to Another......Page 760
C (OCI): Appending One (Temporary) LOB to Another......Page 762
COBOL (Pro*COBOL): Appending One (Temporary) LOB to Another......Page 765
C/C++ (Pro*C/C++): Appending One (Temporary) LOB to Another......Page 767
Write-Appending to a Temporary LOB......Page 770
C (OCI): Writing-Appending to a Temporary LOB......Page 772
COBOL (Pro*COBOL): Write-Appending to a Temporary LOB......Page 774
C/C++ (Pro*C/C++): Write-Appending to a Temporary LOB......Page 776
Writing Data to a Temporary LOB......Page 778
C (OCI): Writing Data to a Temporary LOB......Page 781
COBOL (Pro*COBOL): Writing Data to a Temporary LOB......Page 784
C/C++ (Pro*C/C++): Writing Data to a Temporary LOB......Page 786
Trimming Temporary LOB Data......Page 789
C (OCI): Trimming Temporary LOB Data......Page 791
COBOL (Pro*COBOL): Trimming Temporary LOB Data......Page 794
C/C++ (Pro*C/C++): Trimming Temporary LOB Data......Page 795
Erasing Part of a Temporary LOB......Page 798
(OCI): Erasing Part of a Temporary LOB......Page 800
COBOL (Pro*COBOL): Erasing Part of a Temporary LOB......Page 803
C/C++ (Pro*C/C++): Erasing Part of a Temporary LOB......Page 805
Enabling LOB Buffering for a Temporary LOB......Page 807
C (OCI): Enabling LOB Buffering for a Temporary LOB......Page 809
COBOL (Pro*COBOL): Enabling LOB Buffering for a Temporary LOB......Page 810
C/C++ (Pro*C/C++): Enabling LOB Buffering for a Temporary LOB......Page 812
Flushing Buffer for a Temporary LOB......Page 814
C (OCI): Flushing Buffer for a Temporary LOB......Page 815
COBOL (Pro*COBOL): Flushing Buffer for a Temporary LOB......Page 817
C/C++ (Pro*C/C++): Flushing Buffer for a Temporary LOB......Page 818
Disabling LOB Buffering for a Temporary LOB......Page 821
C (OCI): Disabling LOB Buffering......Page 823
COBOL (Pro*COBOL): Disabling LOB Buffering for a Temporary LOB......Page 824
C/C++ (Pro*C/C++): Disabling LOB Buffering for a Temporary LOB......Page 826
12 External LOBs (BFILEs)......Page 829
Use Case Model: External LOBs (BFILEs)......Page 830
Initializing a BFILE Locator......Page 833
Examples......Page 834
BFILENAME() and Initialization......Page 835
Ownership and Privileges......Page 836
SQL DDL for BFILE Security......Page 837
Guidelines for DIRECTORY Usage......Page 838
BFILEs in Shared Server (Multi-Threaded Server — MTS) Mode......Page 839
Guidelines......Page 840
General Rule......Page 841
Three Ways to Create a Table Containing a BFILE......Page 842
Creating a Table Containing One or More BFILE Columns......Page 843
SQL: Creating a Table Containing One or More BFILE Columns......Page 844
Creating a Table of an Object Type with a BFILE Attribute......Page 846
SQL: Creating a Table of an Object Type with a BFILE Attribute......Page 847
Creating a Table with a Nested Table Containing a BFILE......Page 849
SQL: Creating a Table with a Nested Table Containing a BFILE......Page 850
Three Ways to Insert a Row Containing a BFILE......Page 851
INSERT a Row Using BFILENAME()......Page 852
Ways BFILENAME() is Used to Initialize BFILE Column or Locator Variable......Page 853
C (OCI): Inserting a Row by means of BFILENAME()......Page 855
COBOL (Pro*COBOL): Inserting a Row by means of BFILENAME()......Page 856
C/C++ (Pro*C/C++): Inserting a Row by means of BFILENAME()......Page 857
Java (JDBC): Inserting a Row by means of BFILENAME()......Page 858
INSERT a BFILE Row by Selecting a BFILE From Another Table......Page 860
SQL: Inserting a Row Containing a BFILE by Selecting a BFILE From Another Table......Page 861
Inserting a Row With BFILE by Initializing a BFILE Locator......Page 862
C (OCI): Inserting a Row Containing a BFILE by Initializing a BFILE Locator......Page 864
COBOL (Pro*COBOL): Inserting a Row Containing a BFILE by Initializing a BFILE Locator......Page 865
C/C++ (Pro*C/C++): Inserting a Row Containing a BFILE by Initializing a BFILE Locator......Page 867
Java (JDBC): Inserting a Row Containing a BFILE by Initializing a BFILE Locator......Page 868
Loading Data Into External LOB (BFILE)......Page 870
Data file (sample9.dat)......Page 872
Data file (sample10.dat)......Page 873
Loading a LOB with BFILE Data......Page 874
Specify Amount Parameter to be Less than the Size of the BFILE!......Page 875
C (OCI): Loading a LOB with BFILE Data......Page 877
COBOL (Pro*COBOL): Loading a LOB with BFILE Data......Page 879
C/C++ (Pro*C/C++): Loading a LOB with BFILE Data......Page 880
Java (JDBC): Loading a LOB with BFILE Data......Page 882
Two Ways to Open a BFILE......Page 883
Close Files After Use!......Page 884
Opening a BFILE with FILEOPEN......Page 885
C (OCI): Opening a BFILE with FILEOPEN......Page 887
Java (JDBC): Opening a BFILE with FILEOPEN......Page 888
Opening a BFILE with OPEN......Page 890
C (OCI): Opening a BFILE with OPEN......Page 892
COBOL (Pro*COBOL): Opening a BFILE with OPEN......Page 893
C/C++ (Pro*C/C++): Opening a BFILE with OPEN......Page 894
Visual Basic (OO4O) Opening a BFILE with OPEN......Page 895
Java (JDBC): Opening a BFILE with OPEN......Page 896
Specify the Maximum Number of Open BFILEs: SESSION_MAX_OPEN_FILES......Page 898
Checking If the BFILE is Open with FILEISOPEN......Page 900
PL/SQL (DBMS_LOB Package): Checking If the BFILE is Open with FILEISOPEN......Page 901
C (OCI): Checking If the BFILE is Open with FILEISOPEN......Page 902
Java (JDBC): Checking If the BFILE is Open with FILEISOPEN......Page 903
Checking If a BFILE is Open Using ISOPEN......Page 906
PL/SQL (DBMS_LOB Package): Checking If the BFILE is Open with ISOPEN......Page 907
C (OCI): Checking If the BFILE is Open with ISOPEN......Page 908
COBOL (Pro*COBOL): Checking If the BFILE is Open with ISOPEN......Page 910
C/C++ (Pro*C/C++): Checking If the BFILE is Open with ISOPEN......Page 911
Visual Basic (OO4O): Checking If the BFILE is Open with ISOPEN......Page 912
Java (JDBC): Checking If the BFILE is Open with ISOPEN......Page 913
Displaying BFILE Data......Page 915
C (OCI): Displaying BFILE Data......Page 917
COBOL (Pro*COBOL): Displaying BFILE Data......Page 920
C/C++ (Pro*C/C++): Displaying BFILE Data......Page 922
Visual Basic (OO4O): Displaying BFILE Data......Page 923
Java (JDBC): Displaying BFILE Data......Page 924
Reading Data from a BFILE......Page 927
The Amount Parameter......Page 928
C (OCI): Reading Data from a BFILE......Page 930
COBOL (Pro*COBOL): Reading Data from a BFILE......Page 932
C/C++ (Pro*C/C++): Reading Data from a BFILE......Page 933
Visual Basic (OO4O): Reading Data from a BFILE......Page 934
Java (JDBC): Reading Data from a BFILE......Page 935
Reading a Portion of BFILE Data (substr)......Page 937
COBOL (Pro*COBOL): Reading a Portion of BFILE Data (substr)......Page 939
C/C++ (Pro*C/C++): Reading a Portion of BFILE Data (substr)......Page 941
Java (JDBC): Reading a Portion of BFILE Data (substr)......Page 942
Comparing All or Parts of Two BFILES......Page 945
PL/SQL (DBMS_LOB Package): Comparing All or Parts of Two BFILES......Page 947
COBOL (Pro*COBOL): Comparing All or Parts of Two BFILES......Page 948
C/C++ (Pro*C/C++): Comparing All or Parts of Two BFILES......Page 950
Visual Basic (OO4O): Comparing All or Parts of Two BFILES......Page 951
Java (JDBC): Comparing All or Parts of Two BFILES......Page 952
Checking If a Pattern Exists (instr) in the BFILE......Page 955
PL/SQL (DBMS_LOB Package): Checking If a Pattern Exists (instr) in the BFILE......Page 957
COBOL (Pro*COBOL): Checking If a Pattern Exists (instr) in the BFILE......Page 958
C/C++ (Pro*C/C++): Checking If a Pattern Exists (instr) in the BFILE......Page 959
Java (JDBC): Checking If a Pattern Exists (instr) in the BFILE......Page 961
Checking If the BFILE Exists......Page 963
C (OCI): Checking If the BFILE Exists......Page 965
COBOL (Pro*COBOL): Checking If the BFILE Exists......Page 967
C/C++ (Pro*C/C++): Checking If the BFILE Exists......Page 968
Visual Basic (OO4O): Checking If the BFILE Exists......Page 969
Java (JDBC): Checking If the BFILE Exists......Page 970
Getting the Length of a BFILE......Page 972
C (OCI): Getting the Length of a BFILE......Page 974
COBOL (Pro*COBOL): Getting the Length of a BFILE......Page 976
C/C++ (Pro*C/C++): Getting the Length of a BFILE......Page 977
Visual Basic (OO4O): Getting the Length of a BFILE......Page 978
Java (JDBC): Getting the Length of a BFILE......Page 979
Copying a LOB Locator for a BFILE......Page 981
C (OCI): Copying a LOB Locator for a BFILE......Page 983
COBOL (Pro*COBOL): Copying a LOB Locator for a BFILE......Page 985
C/C++ (Pro*C/C++): Copying a LOB Locator for a BFILE......Page 986
Java (JDBC): Copying a LOB Locator for a BFILE......Page 987
Determining If a LOB Locator for a BFILE Is Initialized......Page 989
C (OCI): Determining If a LOB Locator for a BFILE Is Initialized......Page 991
C/C++ (Pro*C/C++): Determining If a LOB Locator for a BFILE Is Initialized......Page 992
Determining If One LOB Locator for a BFILE Is Equal to Another......Page 994
C (OCI): Determining If One LOB Locator for a BFILE Is Equal to Another......Page 996
C/C++ (Pro*C/C++): Determining If One LOB Locator for a BFILE Is Equal to Another......Page 998
Java (JDBC): Determining If One LOB Locator for a BFILE Is Equal to Another......Page 999
Getting DIRECTORY Alias and Filename......Page 1001
C (OCI): Getting Directory Alias and Filename......Page 1003
COBOL (Pro*COBOL): Getting Directory Alias and Filename......Page 1004
C/C++ (Pro*C/C++): Getting Directory Alias and Filename......Page 1006
Visual Basic (OO4O): Getting Directory Alias and Filename......Page 1007
Java (JDBC): Getting Directory Alias and Filename......Page 1008
Three Ways to Update a Row Containing a BFILE......Page 1010
Updating a BFILE Using BFILENAME()......Page 1011
SQL: Updating a BFILE by means of BFILENAME()......Page 1013
Updating a BFILE by Selecting a BFILE From Another Table......Page 1014
SQL: Updating a BFILE by Selecting a BFILE From Another Table......Page 1015
Updating a BFILE by Initializing a BFILE Locator......Page 1016
PL/SQL: Updating a BFILE by Initializing a BFILE Locator......Page 1018
C (OCI): Updating a BFILE by Initializing a BFILE Locator......Page 1019
COBOL (Pro*COBOL): Updating a BFILE by Initializing a BFILE Locator......Page 1020
C/C++ (Pro*C/C++): Updating a BFILE by Initializing a BFILE Locator......Page 1021
Visual Basic (OO4O): Updating a BFILE by Initializing a BFILE Locator......Page 1022
Java (JDBC): Updating a BFILE by Initializing a BFILE Locator......Page 1023
Two Ways to Close a BFILE......Page 1025
Closing a BFILE with FILECLOSE......Page 1027
C (OCI): Closing a BFile with FILECLOSE......Page 1029
Java (JDBC): Closing a BFile with FILECLOSE......Page 1030
Closing a BFILE with CLOSE......Page 1032
C (OCI): Closing a BFile with CLOSE......Page 1034
COBOL (Pro*COBOL): Closing a BFILE with CLOSE......Page 1035
C/C++ (Pro*C/C++): Closing a BFile with CLOSE......Page 1036
Visual Basic (OO4O): Closing a BFile with CLOSE......Page 1037
Java (JDBC): Closing a BFile with CLOSE......Page 1038
Closing All Open BFILEs with FILECLOSEALL......Page 1040
C (OCI): Closing All Open BFiles......Page 1042
COBOL (Pro*COBOL): Closing All Open BFiles......Page 1043
C/C++ (Pro*C/C++): Closing All Open BFiles......Page 1044
Java (JDBC): Closing All Open BFiles with FILECLOSEALL......Page 1046
Deleting the Row of a Table Containing a BFILE......Page 1049
TRUNCATE......Page 1050
13 Using OraOLEDB to Manipulate LOBs......Page 1051
Rowset Object......Page 1052
Writing Data to a LOB Column With AppendChunk()......Page 1053
ADO and LOBs Example 1: Inserting LOB Data From a File......Page 1054
14 LOBs Case Studies......Page 1057
Building a Multimedia Repository......Page 1058
Populating the Repository......Page 1060
Example 1: Inserting a Word document into a BLOB Column using PL/SQL......Page 1061
Searching the Repository......Page 1062
MyServletCtx Servlet......Page 1063
MyServletCtx.java......Page 1064
MyServlet.java......Page 1066
First Steps Solution......Page 1068
Use Case Diagrams......Page 1071
Hot Links in the Online Versions of this Document......Page 1077
B The Multimedia Schema Used for Examples in This Manual......Page 1079
A Typical Multimedia Application......Page 1080
The Multimedia Schema......Page 1081
Table Multimedia_Tab......Page 1083
Script for Creating the Multimedia Schema......Page 1085
Index......Page 1089