Higgins S., Kotsovolos S., Raphaely D.
Table of contents :
Send Us Your Comments……Page 21
Preface……Page 23
Structure……Page 24
Related Documents……Page 27
Conventions……Page 29
Documentation Accessibility……Page 32
LOB Features Introduced with Oracle9i, Release 1 (9.0.1)……Page 33
LOB Features Introduced with Oracle8i, Release 8.1.5……Page 36
1 Introduction to LOBs……Page 37
Unstructured Data in System Files Need Accessing from the Database……Page 38
Oracle Text (interMedia Text) Indexing Supports Searching Content in XML Elements……Page 39
Why Not Use LONGs?……Page 40
SQL Semantics Support for LOBs……Page 41
Extensible Indexing on LOBs……Page 42
Extensible Optimizer……Page 43
LOB “Demo” Directory……Page 44
Compatibility and Migration Issues……Page 45
Examples in This Manual Use the Multimedia Schema……Page 46
2 Basic LOB Components……Page 47
External LOBs (BFILEs)……Page 48
Reference Semantics……Page 49
CLOB, NCLOB Values are Stored Using 2 Byte Unicode for Varying-Width Character Sets……Page 50
Converting Between Client Character Set and UCS-2……Page 51
Internal LOBs……Page 52
External LOBs……Page 53
SELECTing a LOB……Page 54
Setting an Internal LOB to NULL……Page 55
Initializing LOBs Example Using Table Multimedia_tab……Page 56
Initializing External LOBs to NULL or a File Name……Page 57
3 LOB Support in Different Programmatic Environments……Page 59
Eight Programmatic Environments Operate on LOBs……Page 60
Comparing the LOB Interfaces……Page 61
Further Information……Page 65
Example of OraBlob and OraBfile……Page 66
OO4O Methods and Properties to Access Data Stored in BLOBs, CLOBs, NCLOBs, and BFILEs……Page 67
OO4O Methods To Modify BLOB, CLOB, and NCLOB Values……Page 68
OO4O Methods To Open and Close External LOBs (BFILEs)……Page 69
OO4O Read-Only Methods For External Lobs (BFILEs)……Page 70
OO4O Properties For Operating on External LOBs (BFILEs)……Page 71
OLEDB (Oracle Provider for OLEDB — OraOLEDB)……Page 72
4 Managing LOBs……Page 73
Using SQL DML for Basic Operations on LOBs……Page 74
Oracle8 Release 8.0.4.3……Page 75
Managing Temporary LOBs……Page 76
Inline versus Out-of-Line LOBs……Page 77
SQL Loader Performance: Loading Into Internal LOBs……Page 78
Data File (sample.dat)……Page 79
Control File……Page 80
Loading Out-Of-Line LOB Data……Page 81
Secondary Data File (SecondStory.txt)……Page 82
Loading Out-of-Line LOB Data in Delimited Fields……Page 83
Control File……Page 84
SQL Loader LOB Loading Tips……Page 85
LOB Restrictions……Page 86
Removed Restrictions……Page 89
5 Large Objects: Advanced Topics……Page 91
A Selected Locator Becomes a Read Consistent Locator……Page 92
Read Consistent Locators Provide Same LOB Value Regardless of When the SELECT Occurs……Page 93
Example……Page 94
Example of Updating a LOB Using SQL DML and DBMS_LOB……Page 96
Example……Page 97
Example of Using One Locator to Update the Same LOB Value……Page 98
Example……Page 99
Example of Updating a LOB with a PL/SQL (DBMS_LOB) Bind Variable……Page 100
Example……Page 101
LOB Locators Cannot Span Transactions……Page 103
Example of Locator Not Spanning a Transaction……Page 104
Transaction IDs: Reading and Writing to a LOB Using Locators……Page 105
Case 2:……Page 106
Case 4:……Page 107
LOBs in the Object Cache……Page 108
Guidelines for Using LOB Buffering……Page 109
LOB Buffer Physical Structure……Page 111
Example of Using the LOB Buffering System (LBS)……Page 112
Flushing the LOB Buffer……Page 113
Flushing the Updated LOB……Page 114
Saving Locator State to Avoid a Reselect……Page 115
OCI Example of LOB Buffering……Page 116
Creating a Varray Containing LOB References: Example……Page 119
Example of LOB Columns in Partitioned Index-Organized Tables……Page 120
Non-Supported Column Types……Page 121
Hash Partitioned Index-Organized Table LOB Restrictions……Page 122
6 Frequently Asked Questions about LOBs……Page 123
Answer……Page 126
Question……Page 127
Answer……Page 128
Answer……Page 129
Answer……Page 130
Answer……Page 131
Answer……Page 132
Answer……Page 133
Question……Page 134
Answer……Page 135
Answer……Page 136
Answer……Page 137
Answer……Page 140
Question……Page 141
Answer……Page 142
Question……Page 143
Answer……Page 144
Question……Page 145
Answer……Page 146
Answer……Page 147
Question 1……Page 148
Answer 2……Page 149
Answer……Page 150
Answer……Page 151
Question……Page 152
Answer……Page 153
Answer……Page 154
7 Modeling and Design……Page 155
LOBs Compared to LONG and LONG RAW Types……Page 156
Selecting a Table Architecture……Page 157
EMPTY_CLOB() or EMPTY_BLOB() Column Storage: LOB Locator is Stored……Page 158
Defining Tablespace and Storage Example1……Page 159
TABLESPACE and LOB Index……Page 160
PCTVERSION……Page 161
CACHE / NOCACHE / CACHE READS……Page 162
LOBs Will Always Generate Undo for LOB Index Pages……Page 163
Set INITIAL and NEXT to Larger than CHUNK……Page 164
Guidelines for ENABLE or DISABLE STORAGE IN ROW……Page 165
Example 1: Creating a Tablespace and Table to Store Gigabyte LOBs……Page 166
How this Affects the Temporary LOB COPY or APPEND?……Page 167
Binds Greater than 4,000 Bytes are Now Allowed For LOB INSERTs and UPDATEs……Page 168
Binds of More Than 4,000 Bytes … No HEX to RAW or RAW to HEX Conversion……Page 169
Example: PL/SQL – Using Binds of More Than 4,000 Bytes in INSERT and UPDATE……Page 170
Example: PL/SQL – 4,000 Byte Result Limit in Binds of More than 4,000 Bytes When Data Includes SQ………Page 172
Example: C (OCI) – Binds of More than 4,000 Bytes For INSERT and UPDATE……Page 173
Close All Opened LOBs Before Committing the Transaction……Page 176
Example 1: Correct Use of OPEN/CLOSE Calls to LOBs in a Transaction……Page 177
LOBs in Index Organized Tables (IOT)……Page 178
Example of Index Organized Table (IOT) with LOB Columns……Page 179
Manipulating LOBs in Partitioned Tables……Page 180
Creating and Partitioning a Table Containing LOB Data……Page 182
Creating an Index on a Table Containing LOB Columns……Page 184
Splitting Partitions Containing LOBs……Page 185
Functional Indexes on LOB Columns……Page 186
Improved LOB Usability: You can Now Access LOBs Using SQL “Character” Functions……Page 187
PL/SQL Relational Operators Now Allowed for LOBs……Page 188
Using SQL Functions and Operators for VARCHAR2s on CLOBs……Page 189
UNICODE Support for VARCHAR2 and CLOB……Page 193
Defining CHAR Buffer on CLOB……Page 194
Returning VARCHAR2s……Page 195
Returned LOB is a Temporary LOB Locator……Page 196
IS [NOT] NULL in VARCHAR2s and CLOBs……Page 197
SQL DML Changes For LOBs……Page 198
PL/SQL Statements and Variables: New Semantics Changes……Page 199
PL/SQL Example 1: Prior Release SQL Interface for a CLOB/VARCHAR2 Application……Page 200
PL/SQL Example 3: Defining a CLOB Variable on a VARCHAR2……Page 201
VARCHAR2 and CLOB in PL/SQL Built-in Functions……Page 202
PL/SQL Example 5: Change in Locator-Data Linkage……Page 203
PL/SQL Example 6: Freeing Temporary LOBs Automatically and Manually……Page 204
Varying-Width Character Sets: VARCHAR2s and CLOBs……Page 205
Inserting More than 4K Bytes Data Into LOB Columns……Page 206
Performance Measurement……Page 207
User-Defined Aggregates and LOBs……Page 208
UDAGs: DML and Query Support……Page 209
8 Migrating From LONGs to LOBs……Page 211
Using the LONG-to-LOB API Results in an Easy Migration……Page 212
Binds in OCI……Page 213
OCI Functions Allow Piecewise and Array INSERT, UPDATE, or Fetch on LOBs……Page 214
Assignment and Parameters Passing (PL/SQL)……Page 215
Migrating LONGs to LOBs: Using ALTER TABLE to Change LONG Column to LOB Types……Page 216
All Constraints of LONG Column are Maintained……Page 218
Rebuilding Indexes After a LONG to LOB Migration……Page 219
Replication……Page 220
Triggers……Page 221
NULL LOBs Versus Zero Length LOBs……Page 222
Guidelines for Using LONG-to-LOB API for LOBs with OCI……Page 223
Using Piecewise INSERTs and UPDATEs with Polling……Page 224
Simple Fetch in One Piece……Page 225
Piecewise with Callback……Page 226
UPDATEs……Page 227
Variable Assignment Between CLOB/CHAR and BLOB/RAW……Page 228
VARCHAR2 and CLOB in PL/SQL Built-In Functions……Page 229
PL/SQL and C Binds from OCI……Page 230
From SQL……Page 231
From PL/SQL……Page 232
Overloading with Anchored Types……Page 233
Using utldtree.sql to Determine Where Your Application Needs Change……Page 234
To Convert LONG to CLOB, Use ALTER TABLE……Page 235
Converting LONG to LOB Example 1: More than 4K Binds and Simple INSERTs……Page 236
Converting LONG to LOB Example 2: Piecewise INSERT with Polling……Page 237
Converting LONG to LOB Example 3: Piecewise INSERT with Callback……Page 238
Converting LONG to LOB Example 4: Array insert……Page 240
Converting LONG to LOB Example 6: Piecewise Fetch with Polling……Page 242
Converting LONG to LOB Example 7: Piecewise Fetch with Callback……Page 243
Converting LONG to LOB Example 8: Array Fetch……Page 245
Converting LONG to LOB Example 9: Using PL/SQL in INSERT, UPDATE and SELECT……Page 246
Converting LONG to LOB Example 10: Assignments and Parameter Passing in PL/SQL……Page 247
Converting LONG to LOB Example 12: Using PL/SQL Binds from OCI on LOBs……Page 248
Calling PL/SQL Outbinds in the “call foo(:1);” Manner……Page 249
Converting LONG to LOB Example 13: Calling PL/SQL and C Procedures from PL/SQL……Page 250
PL/SQL Interface……Page 251
Compatibility and Migration……Page 252
Answer……Page 253
Answer……Page 254
Answer……Page 255
9 LOBS: Best Practices……Page 257
Loading XML Documents Into LOBs With SQL*Loader……Page 258
LOB Performance Guidelines……Page 261
Temporary LOB Performance Guidelines……Page 262
The Correct Procedure……Page 265
Preventing Generation of Redo Space During Migration……Page 266
10 Internal Persistent LOBs……Page 267
Use Case Model: Internal Persistent LOBs Operations……Page 268
Creating Tables Containing LOBs……Page 273
Creating a Table Containing One or More LOB Columns……Page 275
SQL: Create a Table Containing One or More LOB Columns……Page 277
Creating a Table Containing an Object Type with a LOB Attribute……Page 280
SQL: Creating a Table Containing an Object Type with a LOB Attribute……Page 282
Creating a Nested Table Containing a LOB……Page 285
SQL: Creating a Nested Table Containing a LOB……Page 287
Inserting One or More LOB Values into a Row……Page 288
Inserting a LOB Value using EMPTY_CLOB() or EMPTY_BLOB()……Page 290
SQL: Inserting a Value Using EMPTY_CLOB() / EMPTY_BLOB()……Page 292
Inserting a Row by Selecting a LOB From Another Table……Page 293
SQL: Inserting a Row by Selecting a LOB from Another Table……Page 294
Inserting a Row by Initializing a LOB Locator Bind Variable……Page 295
Visual Basic (OO4O): Inserting a Row by Initializing a LOB Locator Bind Variable……Page 296
Loading Initial Data into a BLOB, CLOB, or NCLOB……Page 297
Loading a LOB with BFILE Data……Page 299
Visual Basic (OO4O): Loading a LOB with Data from a BFILE……Page 301
Open: Checking If a LOB Is Open……Page 302
LONGs to LOBs……Page 304
LONG to LOB Migration Using the LONG-to-LOB API……Page 305
To Convert LONG to CLOB, Use ALTER TABLE……Page 306
LONG to LOB Copying, Using the TO_LOB Operator……Page 307
SQL: Copying LONGs to LOBs Using TO_LOB Operator……Page 308
Checking Out a LOB……Page 311
Visual Basic (OO4O): Checking Out a LOB……Page 313
Checking In a LOB……Page 314
Visual Basic (OO4O): Checking in a LOB……Page 315
Displaying LOB Data……Page 317
Visual Basic (OO4O): Displaying LOB Data……Page 318
Reading Data from a LOB……Page 320
Visual Basic (OO4O): Reading Data from a LOB……Page 322
Reading a Portion of the LOB (substr)……Page 324
Visual Basic (OO4O): Reading a Portion of the LOB (substr)……Page 326
Comparing All or Part of Two LOBs……Page 328
Visual Basic (OO4O): Comparing All or Part of Two LOBs……Page 329
Patterns: Checking for Patterns in the LOB (instr)……Page 330
Length: Determining the Length of a LOB……Page 332
Visual Basic (OO4O): Determining the Length of a LOB……Page 333
Copying All or Part of One LOB to Another LOB……Page 334
Visual Basic (OO4O): Copying All or Part of One LOB to Another LOB……Page 335
Copying a LOB Locator……Page 337
Visual Basic (OO4O: Copying a LOB Locator……Page 338
Equality: Checking If One LOB Locator Is Equal to Another……Page 339
Initialized Locator: Checking If a LOB Locator Is Initialized……Page 341
Character Set ID: Determining Character Set ID……Page 343
Character Set Form: Determining Character Set Form……Page 345
Appending One LOB to Another……Page 347
Visual Basic (OO4O): Appending One LOB to Another……Page 348
Append-Writing to the End of a LOB……Page 350
Writing Data to a LOB……Page 353
Visual Basic (OO4O):Writing Data to a LOB……Page 356
Trimming LOB Data……Page 358
Visual Basic (OO4O): Trimming LOB Data……Page 359
Erasing Part of a LOB……Page 361
Visual Basic (OO4O): Erasing Part of a LOB……Page 362
Enabling LOB Buffering……Page 364
Visual Basic (OO4O): Enabling LOB Buffering……Page 366
Flushing the Buffer……Page 367
Visual Basic (OO4O): Flushing the Buffer……Page 369
Disabling LOB Buffering……Page 370
Visual Basic (OO4O): Disabling LOB Buffering……Page 372
Three Ways to Update a LOB or Entire LOB Data……Page 373
Updating a LOB with EMPTY_CLOB() or EMPTY_BLOB()……Page 374
For Binds of More Than 4,000 Bytes……Page 375
SQL: UPDATE a LOB with EMPTY_CLOB() or EMPTY_BLOB()……Page 376
Updating a Row by Selecting a LOB From Another Table……Page 377
SQL: Update a Row by Selecting a LOB From Another Table……Page 378
Updating by Initializing a LOB Locator Bind Variable……Page 379
Visual Basic (OO4O): Updating by Initializing a LOB Locator Bind Variable……Page 380
Deleting the Row of a Table Containing a LOB……Page 382
SQL: Delete a LOB……Page 383
11 Temporary LOBs……Page 385
Use Case Model: Internal Temporary LOBs……Page 386
Temporary LOB Locators Can be IN Values……Page 390
Temporary LOB Data is Stored in Temporary Tablespace……Page 391
Temporary Tablespace……Page 392
Locators and Semantics……Page 393
Features Specific to Temporary LOBs……Page 394
Security Issues with Temporary LOBs……Page 395
Managing Temporary LOBs……Page 396
Creating a Temporary LOB……Page 397
Checking If a LOB is Temporary……Page 399
Freeing a Temporary LOB……Page 401
Loading a Temporary LOB with Data from a BFILE……Page 403
Determining If a Temporary LOB Is Open……Page 405
Displaying Temporary LOB Data……Page 407
Reading Data from a Temporary LOB……Page 409
Reading Portion of Temporary LOB (Substr)……Page 412
Comparing All or Part of Two (Temporary) LOBs……Page 414
Determining If a Pattern Exists in a Temporary LOB (instr)……Page 416
Finding the Length of a Temporary LOB……Page 418
Copying All or Part of One (Temporary) LOB to Another……Page 420
Copying a LOB Locator for a Temporary LOB……Page 422
Is One Temporary LOB Locator Equal to Another……Page 424
Determining if a LOB Locator for a Temporary LOB Is Initialized……Page 426
Finding Character Set ID of a Temporary LOB……Page 428
Finding Character Set Form of a Temporary LOB……Page 430
Appending One (Temporary) LOB to Another……Page 432
Write-Appending to a Temporary LOB……Page 434
Writing Data to a Temporary LOB……Page 436
Trimming Temporary LOB Data……Page 439
Erasing Part of a Temporary LOB……Page 441
Enabling LOB Buffering for a Temporary LOB……Page 443
Flushing Buffer for a Temporary LOB……Page 445
Disabling LOB Buffering for a Temporary LOB……Page 447
12 External LOBs (BFILEs)……Page 449
Use Case Model: External LOBs (BFILEs)……Page 450
Initializing a BFILE Locator……Page 453
Examples……Page 454
BFILENAME() and Initialization……Page 455
Ownership and Privileges……Page 456
SQL DDL for BFILE Security……Page 457
Guidelines for DIRECTORY Usage……Page 458
BFILEs in Shared Server (Multi-Threaded Server — MTS) Mode……Page 459
Guidelines……Page 460
General Rule……Page 461
Three Ways to Create a Table Containing a BFILE……Page 462
Creating a Table Containing One or More BFILE Columns……Page 463
SQL: Creating a Table Containing One or More BFILE Columns……Page 464
Creating a Table of an Object Type with a BFILE Attribute……Page 466
SQL: Creating a Table of an Object Type with a BFILE Attribute……Page 467
Creating a Table with a Nested Table Containing a BFILE……Page 469
SQL: Creating a Table with a Nested Table Containing a BFILE……Page 470
Three Ways to Insert a Row Containing a BFILE……Page 471
INSERT a Row Using BFILENAME()……Page 472
Ways BFILENAME() is Used to Initialize BFILE Column or Locator Variable……Page 473
SQL: Inserting a Row by means of BFILENAME()……Page 474
Visual Basic (OO4O): Inserting a Row by means of BFILENAME()……Page 475
INSERT a BFILE Row by Selecting a BFILE From Another Table……Page 476
SQL: Inserting a Row Containing a BFILE by Selecting a BFILE From Another Table……Page 477
Inserting a Row With BFILE by Initializing a BFILE Locator……Page 478
Visual Basic (OO4O): Inserting a Row Containing a BFILE by Initializing a BFILE Locator……Page 479
Loading Data Into External LOB (BFILE)……Page 481
Data file (sample9.dat)……Page 483
Data file (sample10.dat)……Page 484
Loading a LOB with BFILE Data……Page 485
Specify Amount Parameter to be Less than the Size of the BFILE!……Page 486
Visual Basic (OO4O): Loading a LOB with BFILE Data……Page 487
Two Ways to Open a BFILE……Page 488
Close Files After Use!……Page 489
Opening a BFILE with FILEOPEN……Page 490
Visual Basic (OO4O): Opening a BFILE with FILEOPEN……Page 491
Opening a BFILE with OPEN……Page 492
Visual Basic (OO4O) Opening a BFILE with OPEN……Page 493
Specify the Maximum Number of Open BFILEs: SESSION_MAX_OPEN_FILES……Page 494
Checking If the BFILE is Open with FILEISOPEN……Page 496
Visual Basic (OO4O): Checking If the BFILE is Open with FILEISOPEN……Page 497
Checking If a BFILE is Open Using ISOPEN……Page 498
Visual Basic (OO4O): Checking If the BFILE is Open with ISOPEN……Page 499
Displaying BFILE Data……Page 500
Visual Basic (OO4O): Displaying BFILE Data……Page 501
Reading Data from a BFILE……Page 503
The Amount Parameter……Page 504
Visual Basic (OO4O): Reading Data from a BFILE……Page 505
Reading a Portion of BFILE Data (substr)……Page 507
Visual Basic (OO4O): Reading a Portion of BFILE Data (substr)……Page 508
Comparing All or Parts of Two BFILES……Page 510
Visual Basic (OO4O): Comparing All or Parts of Two BFILES……Page 511
Checking If a Pattern Exists (instr) in the BFILE……Page 513
Checking If the BFILE Exists……Page 515
Visual Basic (OO4O): Checking If the BFILE Exists……Page 516
Getting the Length of a BFILE……Page 518
Visual Basic (OO4O): Getting the Length of a BFILE……Page 519
Copying a LOB Locator for a BFILE……Page 521
Determining If a LOB Locator for a BFILE Is Initialized……Page 523
Determining If One LOB Locator for a BFILE Is Equal to Another……Page 525
Getting DIRECTORY Alias and Filename……Page 527
Visual Basic (OO4O): Getting Directory Alias and Filename……Page 528
Three Ways to Update a Row Containing a BFILE……Page 530
Updating a BFILE Using BFILENAME()……Page 531
SQL: Updating a BFILE by means of BFILENAME()……Page 533
Updating a BFILE by Selecting a BFILE From Another Table……Page 534
SQL: Updating a BFILE by Selecting a BFILE From Another Table……Page 535
Updating a BFILE by Initializing a BFILE Locator……Page 536
Visual Basic (OO4O): Updating a BFILE by Initializing a BFILE Locator……Page 537
Two Ways to Close a BFILE……Page 539
Closing a BFILE with FILECLOSE……Page 541
Visual Basic (OO4O): Closing a BFile with FILECLOSE……Page 542
Closing a BFILE with CLOSE……Page 543
Visual Basic (OO4O): Closing a BFile with CLOSE……Page 544
Closing All Open BFILEs with FILECLOSEALL……Page 545
Visual Basic (OO4O): Closing All Open BFiles……Page 546
Deleting the Row of a Table Containing a BFILE……Page 548
TRUNCATE……Page 549
13 Using OraOLEDB to Manipulate LOBs……Page 551
Rowset Object……Page 552
Writing Data to a LOB Column With AppendChunk()……Page 553
ADO and LOBs Example 1: Inserting LOB Data From a File……Page 554
14 LOBs Case Studies……Page 557
Building a Multimedia Repository……Page 558
Populating the Repository……Page 560
Example 1: Inserting a Word document into a BLOB Column using PL/SQL……Page 561
Searching the Repository……Page 562
MyServletCtx Servlet……Page 563
MyServletCtx.java……Page 564
MyServlet.java……Page 566
First Steps Solution……Page 568
Use Case Diagrams……Page 571
Hot Links in the Online Versions of this Document……Page 577
B The Multimedia Schema Used for Examples in This Manual……Page 579
A Typical Multimedia Application……Page 580
The Multimedia Schema……Page 581
Table Multimedia_Tab……Page 582
Script for Creating the Multimedia Schema……Page 584
Index……Page 589
Reviews
There are no reviews yet.