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
Set CSID Parameter To OCI_UCS2ID to Read/Write in UCS2……Page 64
Varying-Width Character Set Rules……Page 65
OCI LOB Examples……Page 66
OCI Functions that Operate on BLOBs, BLOBs, NCLOBs, and BFILEs……Page 67
OCI Functions For Temporary LOBs……Page 68
OCI LOB-Buffering Functions……Page 69
OCI Example — Is the LOB Open: main() and seeIfLOBIsOpen……Page 70
OLEDB (Oracle Provider for OLEDB — OraOLEDB)……Page 75
4 Managing LOBs……Page 77
Using SQL DML for Basic Operations on LOBs……Page 78
Oracle8 Release 8.0.4.3……Page 79
Managing Temporary LOBs……Page 80
Inline versus Out-of-Line LOBs……Page 81
SQL Loader Performance: Loading Into Internal LOBs……Page 82
Data File (sample.dat)……Page 83
Control File……Page 84
Loading Out-Of-Line LOB Data……Page 85
Secondary Data File (SecondStory.txt)……Page 86
Loading Out-of-Line LOB Data in Delimited Fields……Page 87
Control File……Page 88
SQL Loader LOB Loading Tips……Page 89
LOB Restrictions……Page 90
Removed Restrictions……Page 93
5 Large Objects: Advanced Topics……Page 95
A Selected Locator Becomes a Read Consistent Locator……Page 96
Read Consistent Locators Provide Same LOB Value Regardless of When the SELECT Occurs……Page 97
Example……Page 98
Example of Updating a LOB Using SQL DML and DBMS_LOB……Page 100
Example……Page 101
Example of Using One Locator to Update the Same LOB Value……Page 102
Example……Page 103
Example of Updating a LOB with a PL/SQL (DBMS_LOB) Bind Variable……Page 104
Example……Page 105
LOB Locators Cannot Span Transactions……Page 107
Example of Locator Not Spanning a Transaction……Page 108
Transaction IDs: Reading and Writing to a LOB Using Locators……Page 109
Case 2:……Page 110
Case 4:……Page 111
LOBs in the Object Cache……Page 112
Guidelines for Using LOB Buffering……Page 113
LOB Buffer Physical Structure……Page 115
Example of Using the LOB Buffering System (LBS)……Page 116
Flushing the LOB Buffer……Page 117
Flushing the Updated LOB……Page 118
Saving Locator State to Avoid a Reselect……Page 119
OCI Example of LOB Buffering……Page 120
Creating a Varray Containing LOB References: Example……Page 123
Example of LOB Columns in Partitioned Index-Organized Tables……Page 124
Non-Supported Column Types……Page 125
Hash Partitioned Index-Organized Table LOB Restrictions……Page 126
6 Frequently Asked Questions about LOBs……Page 127
Answer……Page 130
Question……Page 131
Answer……Page 132
Answer……Page 133
Answer……Page 134
Answer……Page 135
Answer……Page 136
Answer……Page 137
Question……Page 138
Answer……Page 139
Answer……Page 140
Answer……Page 141
Answer……Page 144
Question……Page 145
Answer……Page 146
Question……Page 147
Answer……Page 148
Question……Page 149
Answer……Page 150
Answer……Page 151
Question 1……Page 152
Answer 2……Page 153
Answer……Page 154
Answer……Page 155
Question……Page 156
Answer……Page 157
Answer……Page 158
7 Modeling and Design……Page 159
LOBs Compared to LONG and LONG RAW Types……Page 160
Selecting a Table Architecture……Page 161
EMPTY_CLOB() or EMPTY_BLOB() Column Storage: LOB Locator is Stored……Page 162
Defining Tablespace and Storage Example1……Page 163
TABLESPACE and LOB Index……Page 164
PCTVERSION……Page 165
CACHE / NOCACHE / CACHE READS……Page 166
LOBs Will Always Generate Undo for LOB Index Pages……Page 167
Set INITIAL and NEXT to Larger than CHUNK……Page 168
Guidelines for ENABLE or DISABLE STORAGE IN ROW……Page 169
Example 1: Creating a Tablespace and Table to Store Gigabyte LOBs……Page 170
How this Affects the Temporary LOB COPY or APPEND?……Page 171
Binds Greater than 4,000 Bytes are Now Allowed For LOB INSERTs and UPDATEs……Page 172
Binds of More Than 4,000 Bytes … No HEX to RAW or RAW to HEX Conversion……Page 173
Example: PL/SQL – Using Binds of More Than 4,000 Bytes in INSERT and UPDATE……Page 174
Example: PL/SQL – 4,000 Byte Result Limit in Binds of More than 4,000 Bytes When Data Includes SQ………Page 176
Example: C (OCI) – Binds of More than 4,000 Bytes For INSERT and UPDATE……Page 177
Close All Opened LOBs Before Committing the Transaction……Page 180
Example 1: Correct Use of OPEN/CLOSE Calls to LOBs in a Transaction……Page 181
LOBs in Index Organized Tables (IOT)……Page 182
Example of Index Organized Table (IOT) with LOB Columns……Page 183
Manipulating LOBs in Partitioned Tables……Page 184
Creating and Partitioning a Table Containing LOB Data……Page 186
Creating an Index on a Table Containing LOB Columns……Page 188
Splitting Partitions Containing LOBs……Page 189
Functional Indexes on LOB Columns……Page 190
Improved LOB Usability: You can Now Access LOBs Using SQL “Character” Functions……Page 191
PL/SQL Relational Operators Now Allowed for LOBs……Page 192
Using SQL Functions and Operators for VARCHAR2s on CLOBs……Page 193
UNICODE Support for VARCHAR2 and CLOB……Page 197
Defining CHAR Buffer on CLOB……Page 198
Returning VARCHAR2s……Page 199
Returned LOB is a Temporary LOB Locator……Page 200
IS [NOT] NULL in VARCHAR2s and CLOBs……Page 201
SQL DML Changes For LOBs……Page 202
PL/SQL Statements and Variables: New Semantics Changes……Page 203
PL/SQL Example 1: Prior Release SQL Interface for a CLOB/VARCHAR2 Application……Page 204
PL/SQL Example 3: Defining a CLOB Variable on a VARCHAR2……Page 205
VARCHAR2 and CLOB in PL/SQL Built-in Functions……Page 206
PL/SQL Example 5: Change in Locator-Data Linkage……Page 207
PL/SQL Example 6: Freeing Temporary LOBs Automatically and Manually……Page 208
Varying-Width Character Sets: VARCHAR2s and CLOBs……Page 209
Inserting More than 4K Bytes Data Into LOB Columns……Page 210
Performance Measurement……Page 211
User-Defined Aggregates and LOBs……Page 212
UDAGs: DML and Query Support……Page 213
8 Migrating From LONGs to LOBs……Page 215
Using the LONG-to-LOB API Results in an Easy Migration……Page 216
Binds in OCI……Page 217
OCI Functions Allow Piecewise and Array INSERT, UPDATE, or Fetch on LOBs……Page 218
Assignment and Parameters Passing (PL/SQL)……Page 219
Migrating LONGs to LOBs: Using ALTER TABLE to Change LONG Column to LOB Types……Page 220
All Constraints of LONG Column are Maintained……Page 222
Rebuilding Indexes After a LONG to LOB Migration……Page 223
Replication……Page 224
Triggers……Page 225
NULL LOBs Versus Zero Length LOBs……Page 226
Guidelines for Using LONG-to-LOB API for LOBs with OCI……Page 227
Using Piecewise INSERTs and UPDATEs with Polling……Page 228
Simple Fetch in One Piece……Page 229
Piecewise with Callback……Page 230
UPDATEs……Page 231
Variable Assignment Between CLOB/CHAR and BLOB/RAW……Page 232
VARCHAR2 and CLOB in PL/SQL Built-In Functions……Page 233
PL/SQL and C Binds from OCI……Page 234
From SQL……Page 235
From PL/SQL……Page 236
Overloading with Anchored Types……Page 237
Using utldtree.sql to Determine Where Your Application Needs Change……Page 238
To Convert LONG to CLOB, Use ALTER TABLE……Page 239
Converting LONG to LOB Example 1: More than 4K Binds and Simple INSERTs……Page 240
Converting LONG to LOB Example 2: Piecewise INSERT with Polling……Page 241
Converting LONG to LOB Example 3: Piecewise INSERT with Callback……Page 242
Converting LONG to LOB Example 4: Array insert……Page 244
Converting LONG to LOB Example 6: Piecewise Fetch with Polling……Page 246
Converting LONG to LOB Example 7: Piecewise Fetch with Callback……Page 247
Converting LONG to LOB Example 8: Array Fetch……Page 249
Converting LONG to LOB Example 9: Using PL/SQL in INSERT, UPDATE and SELECT……Page 250
Converting LONG to LOB Example 10: Assignments and Parameter Passing in PL/SQL……Page 251
Converting LONG to LOB Example 12: Using PL/SQL Binds from OCI on LOBs……Page 252
Calling PL/SQL Outbinds in the “call foo(:1);” Manner……Page 253
Converting LONG to LOB Example 13: Calling PL/SQL and C Procedures from PL/SQL……Page 254
PL/SQL Interface……Page 255
Compatibility and Migration……Page 256
Answer……Page 257
Answer……Page 258
Answer……Page 259
9 LOBS: Best Practices……Page 261
Loading XML Documents Into LOBs With SQL*Loader……Page 262
LOB Performance Guidelines……Page 265
Temporary LOB Performance Guidelines……Page 266
The Correct Procedure……Page 269
Preventing Generation of Redo Space During Migration……Page 270
10 Internal Persistent LOBs……Page 271
Use Case Model: Internal Persistent LOBs Operations……Page 272
Creating Tables Containing LOBs……Page 277
Creating a Table Containing One or More LOB Columns……Page 279
SQL: Create a Table Containing One or More LOB Columns……Page 281
Creating a Table Containing an Object Type with a LOB Attribute……Page 284
SQL: Creating a Table Containing an Object Type with a LOB Attribute……Page 286
Creating a Nested Table Containing a LOB……Page 289
SQL: Creating a Nested Table Containing a LOB……Page 291
Inserting One or More LOB Values into a Row……Page 292
Inserting a LOB Value using EMPTY_CLOB() or EMPTY_BLOB()……Page 294
SQL: Inserting a Value Using EMPTY_CLOB() / EMPTY_BLOB()……Page 296
Inserting a Row by Selecting a LOB From Another Table……Page 297
SQL: Inserting a Row by Selecting a LOB from Another Table……Page 298
Inserting a Row by Initializing a LOB Locator Bind Variable……Page 299
C (OCI): Inserting a Row by Initializing a LOB Locator Bind Variable……Page 300
Loading Initial Data into a BLOB, CLOB, or NCLOB……Page 303
Loading a LOB with BFILE Data……Page 305
C (OCI): Loading a LOB with Data from a BFILE……Page 307
Open: Checking If a LOB Is Open……Page 309
C (OCI): Checking if a LOB is Open……Page 310
LONGs to LOBs……Page 312
LONG to LOB Migration Using the LONG-to-LOB API……Page 313
To Convert LONG to CLOB, Use ALTER TABLE……Page 314
C (OCI): LONG to LOB Migration Using the LONG-to-LOB API: More than 4K Binds and Simple INSERTs……Page 315
LONG to LOB Copying, Using the TO_LOB Operator……Page 317
SQL: Copying LONGs to LOBs Using TO_LOB Operator……Page 318
Checking Out a LOB……Page 321
C (OCI): Checking Out a LOB……Page 322
Checking In a LOB……Page 326
C (OCI): Checking in a LOB……Page 327
Displaying LOB Data……Page 332
C (OCI): Displaying LOB Data……Page 333
Reading Data from a LOB……Page 337
C (OCI): Reading Data from a LOB……Page 339
Reading a Portion of the LOB (substr)……Page 343
Comparing All or Part of Two LOBs……Page 345
Patterns: Checking for Patterns in the LOB (instr)……Page 347
Length: Determining the Length of a LOB……Page 349
C (OCI): Determining the Length of a LOB……Page 350
Copying All or Part of One LOB to Another LOB……Page 352
C (OCI): Copying All or Part of One LOB to Another LOB……Page 353
Copying a LOB Locator……Page 356
C (OCI): Copying a LOB Locator……Page 357
Equality: Checking If One LOB Locator Is Equal to Another……Page 359
C (OCI): Checking If One LOB Locator Is Equal to Another……Page 360
Initialized Locator: Checking If a LOB Locator Is Initialized……Page 363
C (OCI): Checking If a LOB Locator Is Initialized……Page 364
Character Set ID: Determining Character Set ID……Page 366
C (OCI): Determining Character Set ID……Page 367
Character Set Form: Determining Character Set Form……Page 369
C (OCI): Determining Character Set Form……Page 370
Appending One LOB to Another……Page 372
C (OCI): Appending One LOB to Another……Page 373
Append-Writing to the End of a LOB……Page 376
C (OCI): Writing to the End of (Appending to) a LOB……Page 378
Writing Data to a LOB……Page 380
C (OCI): Writing Data to a LOB……Page 383
Trimming LOB Data……Page 387
C (OCI): Trimming LOB Data……Page 388
Erasing Part of a LOB……Page 391
C (OCI): Erasing Part of a LOB……Page 392
Enabling LOB Buffering……Page 395
C (OCI): Enabling LOB Buffering……Page 397
Flushing the Buffer……Page 398
C (OCI): Flushing the Buffer……Page 400
Disabling LOB Buffering……Page 401
C (OCI): Disabling LOB Buffering……Page 403
Three Ways to Update a LOB or Entire LOB Data……Page 406
Updating a LOB with EMPTY_CLOB() or EMPTY_BLOB()……Page 407
For Binds of More Than 4,000 Bytes……Page 408
SQL: UPDATE a LOB with EMPTY_CLOB() or EMPTY_BLOB()……Page 409
Updating a Row by Selecting a LOB From Another Table……Page 410
SQL: Update a Row by Selecting a LOB From Another Table……Page 411
Updating by Initializing a LOB Locator Bind Variable……Page 412
C (OCI): Updating by Initializing a LOB Locator Bind Variable……Page 413
Deleting the Row of a Table Containing a LOB……Page 416
SQL: Delete a LOB……Page 417
11 Temporary LOBs……Page 419
Use Case Model: Internal Temporary LOBs……Page 420
Temporary LOB Locators Can be IN Values……Page 424
Temporary LOB Data is Stored in Temporary Tablespace……Page 425
Temporary Tablespace……Page 426
Locators and Semantics……Page 427
Features Specific to Temporary LOBs……Page 428
Managing Temporary LOBs……Page 430
Creating a Temporary LOB……Page 432
C (OCI): Creating a Temporary LOB……Page 433
Checking If a LOB is Temporary……Page 436
C (OCI): Checking If a LOB is Temporary……Page 437
Freeing a Temporary LOB……Page 439
C (OCI): Freeing a Temporary LOB……Page 440
Loading a Temporary LOB with Data from a BFILE……Page 442
C (OCI): Loading a Temporary LOB with Data from a BFILE……Page 443
Determining If a Temporary LOB Is Open……Page 446
C (OCI) Determining if a Temporary LOB is Open……Page 447
Displaying Temporary LOB Data……Page 449
C (OCI): Displaying Temporary LOB Data……Page 450
Reading Data from a Temporary LOB……Page 454
C (OCI): Reading Data from a Temporary LOB……Page 456
Reading Portion of Temporary LOB (Substr)……Page 460
Comparing All or Part of Two (Temporary) LOBs……Page 462
Determining If a Pattern Exists in a Temporary LOB (instr)……Page 464
Finding the Length of a Temporary LOB……Page 466
C (OCI): Finding the Length of a Temporary LOB……Page 467
Copying All or Part of One (Temporary) LOB to Another……Page 470
C (OCI): Copying All or Part of One (Temporary) LOB to Another……Page 471
Copying a LOB Locator for a Temporary LOB……Page 475
C (OCI): Copying a LOB Locator for a Temporary LOB……Page 476
Is One Temporary LOB Locator Equal to Another……Page 479
C (OCI): Is One LOB Locator for a Temporary LOB Equal to Another……Page 480
Determining if a LOB Locator for a Temporary LOB Is Initialized……Page 482
C (OCI): Determining If a LOB Locator for a Temporary LOB Is Initialized……Page 483
Finding Character Set ID of a Temporary LOB……Page 485
C (OCI): Finding Character Set ID of a Temporary LOB……Page 486
Finding Character Set Form of a Temporary LOB……Page 488
C (OCI): Finding Character Set Form of a Temporary LOB……Page 489
Appending One (Temporary) LOB to Another……Page 491
C (OCI): Appending One (Temporary) LOB to Another……Page 492
Write-Appending to a Temporary LOB……Page 496
C (OCI): Writing-Appending to a Temporary LOB……Page 497
Writing Data to a Temporary LOB……Page 499
C (OCI): Writing Data to a Temporary LOB……Page 501
Trimming Temporary LOB Data……Page 505
C (OCI): Trimming Temporary LOB Data……Page 506
Erasing Part of a Temporary LOB……Page 509
(OCI): Erasing Part of a Temporary LOB……Page 510
Enabling LOB Buffering for a Temporary LOB……Page 513
C (OCI): Enabling LOB Buffering for a Temporary LOB……Page 514
Flushing Buffer for a Temporary LOB……Page 517
C (OCI): Flushing Buffer for a Temporary LOB……Page 518
Disabling LOB Buffering for a Temporary LOB……Page 521
C (OCI): Disabling LOB Buffering……Page 522
12 External LOBs (BFILEs)……Page 525
Use Case Model: External LOBs (BFILEs)……Page 526
Initializing a BFILE Locator……Page 529
Examples……Page 530
BFILENAME() and Initialization……Page 531
Ownership and Privileges……Page 532
SQL DDL for BFILE Security……Page 533
Guidelines for DIRECTORY Usage……Page 534
BFILEs in Shared Server (Multi-Threaded Server — MTS) Mode……Page 535
Guidelines……Page 536
General Rule……Page 537
Three Ways to Create a Table Containing a BFILE……Page 538
Creating a Table Containing One or More BFILE Columns……Page 539
SQL: Creating a Table Containing One or More BFILE Columns……Page 540
Creating a Table of an Object Type with a BFILE Attribute……Page 542
SQL: Creating a Table of an Object Type with a BFILE Attribute……Page 543
Creating a Table with a Nested Table Containing a BFILE……Page 545
SQL: Creating a Table with a Nested Table Containing a BFILE……Page 546
Three Ways to Insert a Row Containing a BFILE……Page 547
INSERT a Row Using BFILENAME()……Page 548
Ways BFILENAME() is Used to Initialize BFILE Column or Locator Variable……Page 549
C (OCI): Inserting a Row by means of BFILENAME()……Page 550
INSERT a BFILE Row by Selecting a BFILE From Another Table……Page 552
SQL: Inserting a Row Containing a BFILE by Selecting a BFILE From Another Table……Page 553
Inserting a Row With BFILE by Initializing a BFILE Locator……Page 554
C (OCI): Inserting a Row Containing a BFILE by Initializing a BFILE Locator……Page 555
Loading Data Into External LOB (BFILE)……Page 557
Data file (sample9.dat)……Page 559
Data file (sample10.dat)……Page 560
Loading a LOB with BFILE Data……Page 561
Specify Amount Parameter to be Less than the Size of the BFILE!……Page 562
C (OCI): Loading a LOB with BFILE Data……Page 563
Two Ways to Open a BFILE……Page 565
Close Files After Use!……Page 566
Opening a BFILE with FILEOPEN……Page 567
C (OCI): Opening a BFILE with FILEOPEN……Page 568
Opening a BFILE with OPEN……Page 570
C (OCI): Opening a BFILE with OPEN……Page 571
Specify the Maximum Number of Open BFILEs: SESSION_MAX_OPEN_FILES……Page 573
Checking If the BFILE is Open with FILEISOPEN……Page 575
C (OCI): Checking If the BFILE is Open with FILEISOPEN……Page 576
Checking If a BFILE is Open Using ISOPEN……Page 578
C (OCI): Checking If the BFILE is Open with ISOPEN……Page 579
Displaying BFILE Data……Page 581
C (OCI): Displaying BFILE Data……Page 582
Reading Data from a BFILE……Page 585
The Amount Parameter……Page 586
C (OCI): Reading Data from a BFILE……Page 587
Reading a Portion of BFILE Data (substr)……Page 590
Comparing All or Parts of Two BFILES……Page 592
Checking If a Pattern Exists (instr) in the BFILE……Page 594
Checking If the BFILE Exists……Page 596
C (OCI): Checking If the BFILE Exists……Page 597
Getting the Length of a BFILE……Page 599
C (OCI): Getting the Length of a BFILE……Page 600
Copying a LOB Locator for a BFILE……Page 602
C (OCI): Copying a LOB Locator for a BFILE……Page 603
Determining If a LOB Locator for a BFILE Is Initialized……Page 605
C (OCI): Determining If a LOB Locator for a BFILE Is Initialized……Page 606
Determining If One LOB Locator for a BFILE Is Equal to Another……Page 609
C (OCI): Determining If One LOB Locator for a BFILE Is Equal to Another……Page 610
Getting DIRECTORY Alias and Filename……Page 613
C (OCI): Getting Directory Alias and Filename……Page 614
Three Ways to Update a Row Containing a BFILE……Page 616
Updating a BFILE Using BFILENAME()……Page 617
SQL: Updating a BFILE by means of BFILENAME()……Page 619
Updating a BFILE by Selecting a BFILE From Another Table……Page 620
SQL: Updating a BFILE by Selecting a BFILE From Another Table……Page 621
Updating a BFILE by Initializing a BFILE Locator……Page 622
C (OCI): Updating a BFILE by Initializing a BFILE Locator……Page 624
Two Ways to Close a BFILE……Page 625
Closing a BFILE with FILECLOSE……Page 627
C (OCI): Closing a BFile with FILECLOSE……Page 628
Closing a BFILE with CLOSE……Page 630
C (OCI): Closing a BFile with CLOSE……Page 631
Closing All Open BFILEs with FILECLOSEALL……Page 633
C (OCI): Closing All Open BFiles……Page 634
Deleting the Row of a Table Containing a BFILE……Page 636
TRUNCATE……Page 637
13 Using OraOLEDB to Manipulate LOBs……Page 639
Rowset Object……Page 640
Writing Data to a LOB Column With AppendChunk()……Page 641
ADO and LOBs Example 1: Inserting LOB Data From a File……Page 642
14 LOBs Case Studies……Page 645
Building a Multimedia Repository……Page 646
How this Application Uses LOBs……Page 647
Populating the Repository……Page 648
Example 1: Inserting a Word document into a BLOB Column using PL/SQL……Page 649
How the Index Was Built on Table sam_emp, resume Column……Page 650
MyServletCtx.java……Page 651
Retrieving Data from the Repository……Page 653
MyServlet.java……Page 654
First Steps Solution……Page 656
Use Case Diagrams……Page 659
Hot Links in the Online Versions of this Document……Page 665
B The Multimedia Schema Used for Examples in This Manual……Page 667
A Typical Multimedia Application……Page 668
The Multimedia Schema……Page 669
Table Multimedia_Tab……Page 670
Script for Creating the Multimedia Schema……Page 672
Index……Page 677
Reviews
There are no reviews yet.