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

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
BLOB, CLOB, and BFILE Classes......Page 65
JDBC Methods for Operating on LOBs......Page 66
JDBC oracle.sql.blob Methods To Read or Examine BLOB Values......Page 68
JDBC oracle.sql.CLOB Methods To Read or Examine CLOB Value......Page 69
JDBC oracle.sql.bfile Methods To Read or Examine External LOB (BFILE) Values......Page 70
JDBC Temporary LOB APIs......Page 71
JDBC: Opening and Closing LOBs......Page 72
Opening the BLOB......Page 73
JDBC: Opening and Closing CLOBs......Page 74
Checking if the CLOB is Open......Page 75
Opening BFILEs......Page 76
Closing the BFILE......Page 77
Usage Example (OpenCloseLob.java)......Page 78
JDBC: Trimming BLOBs......Page 80
New JDBC BLOB Streaming APIs......Page 81
New CLOB Streaming APIs......Page 82
New BFILE Streaming APIs......Page 83
JDBC BFILE Streaming Example (NewStreamLob.java)......Page 84
JDBC and Empty LOBs......Page 88
OLEDB (Oracle Provider for OLEDB — OraOLEDB)......Page 90
4 Managing LOBs......Page 91
Using SQL DML for Basic Operations on LOBs......Page 92
Oracle8 Release 8.0.4.3......Page 93
Managing Temporary LOBs......Page 94
Inline versus Out-of-Line LOBs......Page 95
SQL Loader Performance: Loading Into Internal LOBs......Page 96
Data File (sample.dat)......Page 97
Control File......Page 98
Loading Out-Of-Line LOB Data......Page 99
Secondary Data File (SecondStory.txt)......Page 100
Loading Out-of-Line LOB Data in Delimited Fields......Page 101
Control File......Page 102
SQL Loader LOB Loading Tips......Page 103
LOB Restrictions......Page 104
Removed Restrictions......Page 107
5 Large Objects: Advanced Topics......Page 109
A Selected Locator Becomes a Read Consistent Locator......Page 110
Read Consistent Locators Provide Same LOB Value Regardless of When the SELECT Occurs......Page 111
Example......Page 112
Example of Updating a LOB Using SQL DML and DBMS_LOB......Page 114
Example......Page 115
Example of Using One Locator to Update the Same LOB Value......Page 116
Example......Page 117
Example of Updating a LOB with a PL/SQL (DBMS_LOB) Bind Variable......Page 118
Example......Page 119
LOB Locators Cannot Span Transactions......Page 121
Example of Locator Not Spanning a Transaction......Page 122
Transaction IDs: Reading and Writing to a LOB Using Locators......Page 123
Case 2:......Page 124
Case 4:......Page 125
LOBs in the Object Cache......Page 126
Guidelines for Using LOB Buffering......Page 127
LOB Buffer Physical Structure......Page 129
Example of Using the LOB Buffering System (LBS)......Page 130
Flushing the LOB Buffer......Page 131
Flushing the Updated LOB......Page 132
Saving Locator State to Avoid a Reselect......Page 133
OCI Example of LOB Buffering......Page 134
Creating a Varray Containing LOB References: Example......Page 137
Example of LOB Columns in Partitioned Index-Organized Tables......Page 138
Non-Supported Column Types......Page 139
Hash Partitioned Index-Organized Table LOB Restrictions......Page 140
6 Frequently Asked Questions about LOBs......Page 141
Answer......Page 144
Question......Page 145
Answer......Page 146
Answer......Page 147
Answer......Page 148
Answer......Page 149
Answer......Page 150
Answer......Page 151
Question......Page 152
Answer......Page 153
Answer......Page 154
Answer......Page 155
Answer......Page 158
Question......Page 159
Answer......Page 160
Question......Page 161
Answer......Page 162
Question......Page 163
Answer......Page 164
Answer......Page 165
Question 1......Page 166
Answer 2......Page 167
Answer......Page 168
Answer......Page 169
Question......Page 170
Answer......Page 171
Answer......Page 172
7 Modeling and Design......Page 173
LOBs Compared to LONG and LONG RAW Types......Page 174
Selecting a Table Architecture......Page 175
EMPTY_CLOB() or EMPTY_BLOB() Column Storage: LOB Locator is Stored......Page 176
Defining Tablespace and Storage Example1......Page 177
TABLESPACE and LOB Index......Page 178
PCTVERSION......Page 179
CACHE / NOCACHE / CACHE READS......Page 180
LOBs Will Always Generate Undo for LOB Index Pages......Page 181
Set INITIAL and NEXT to Larger than CHUNK......Page 182
Guidelines for ENABLE or DISABLE STORAGE IN ROW......Page 183
Example 1: Creating a Tablespace and Table to Store Gigabyte LOBs......Page 184
How this Affects the Temporary LOB COPY or APPEND?......Page 185
Binds Greater than 4,000 Bytes are Now Allowed For LOB INSERTs and UPDATEs......Page 186
Binds of More Than 4,000 Bytes ... No HEX to RAW or RAW to HEX Conversion......Page 187
Example: PL/SQL - Using Binds of More Than 4,000 Bytes in INSERT and UPDATE......Page 188
Example: PL/SQL - 4,000 Byte Result Limit in Binds of More than 4,000 Bytes When Data Includes SQ.........Page 190
Example: C (OCI) - Binds of More than 4,000 Bytes For INSERT and UPDATE......Page 191
Close All Opened LOBs Before Committing the Transaction......Page 194
Example 1: Correct Use of OPEN/CLOSE Calls to LOBs in a Transaction......Page 195
LOBs in Index Organized Tables (IOT)......Page 196
Example of Index Organized Table (IOT) with LOB Columns......Page 197
Manipulating LOBs in Partitioned Tables......Page 198
Creating and Partitioning a Table Containing LOB Data......Page 200
Creating an Index on a Table Containing LOB Columns......Page 202
Splitting Partitions Containing LOBs......Page 203
Functional Indexes on LOB Columns......Page 204
Improved LOB Usability: You can Now Access LOBs Using SQL “Character” Functions......Page 205
PL/SQL Relational Operators Now Allowed for LOBs......Page 206
Using SQL Functions and Operators for VARCHAR2s on CLOBs......Page 207
UNICODE Support for VARCHAR2 and CLOB......Page 211
Defining CHAR Buffer on CLOB......Page 212
Returning VARCHAR2s......Page 213
Returned LOB is a Temporary LOB Locator......Page 214
IS [NOT] NULL in VARCHAR2s and CLOBs......Page 215
SQL DML Changes For LOBs......Page 216
PL/SQL Statements and Variables: New Semantics Changes......Page 217
PL/SQL Example 1: Prior Release SQL Interface for a CLOB/VARCHAR2 Application......Page 218
PL/SQL Example 3: Defining a CLOB Variable on a VARCHAR2......Page 219
VARCHAR2 and CLOB in PL/SQL Built-in Functions......Page 220
PL/SQL Example 5: Change in Locator-Data Linkage......Page 221
PL/SQL Example 6: Freeing Temporary LOBs Automatically and Manually......Page 222
Varying-Width Character Sets: VARCHAR2s and CLOBs......Page 223
Inserting More than 4K Bytes Data Into LOB Columns......Page 224
Performance Measurement......Page 225
User-Defined Aggregates and LOBs......Page 226
UDAGs: DML and Query Support......Page 227
8 Migrating From LONGs to LOBs......Page 229
Using the LONG-to-LOB API Results in an Easy Migration......Page 230
Binds in OCI......Page 231
OCI Functions Allow Piecewise and Array INSERT, UPDATE, or Fetch on LOBs......Page 232
Assignment and Parameters Passing (PL/SQL)......Page 233
Migrating LONGs to LOBs: Using ALTER TABLE to Change LONG Column to LOB Types......Page 234
All Constraints of LONG Column are Maintained......Page 236
Rebuilding Indexes After a LONG to LOB Migration......Page 237
Replication......Page 238
Triggers......Page 239
NULL LOBs Versus Zero Length LOBs......Page 240
Guidelines for Using LONG-to-LOB API for LOBs with OCI......Page 241
Using Piecewise INSERTs and UPDATEs with Polling......Page 242
Simple Fetch in One Piece......Page 243
Piecewise with Callback......Page 244
UPDATEs......Page 245
Variable Assignment Between CLOB/CHAR and BLOB/RAW......Page 246
VARCHAR2 and CLOB in PL/SQL Built-In Functions......Page 247
PL/SQL and C Binds from OCI......Page 248
From SQL......Page 249
From PL/SQL......Page 250
Overloading with Anchored Types......Page 251
Using utldtree.sql to Determine Where Your Application Needs Change......Page 252
To Convert LONG to CLOB, Use ALTER TABLE......Page 253
Converting LONG to LOB Example 1: More than 4K Binds and Simple INSERTs......Page 254
Converting LONG to LOB Example 2: Piecewise INSERT with Polling......Page 255
Converting LONG to LOB Example 3: Piecewise INSERT with Callback......Page 256
Converting LONG to LOB Example 4: Array insert......Page 258
Converting LONG to LOB Example 6: Piecewise Fetch with Polling......Page 260
Converting LONG to LOB Example 7: Piecewise Fetch with Callback......Page 261
Converting LONG to LOB Example 8: Array Fetch......Page 263
Converting LONG to LOB Example 9: Using PL/SQL in INSERT, UPDATE and SELECT......Page 264
Converting LONG to LOB Example 10: Assignments and Parameter Passing in PL/SQL......Page 265
Converting LONG to LOB Example 12: Using PL/SQL Binds from OCI on LOBs......Page 266
Calling PL/SQL Outbinds in the "call foo(:1);" Manner......Page 267
Converting LONG to LOB Example 13: Calling PL/SQL and C Procedures from PL/SQL......Page 268
PL/SQL Interface......Page 269
Compatibility and Migration......Page 270
Answer......Page 271
Answer......Page 272
Answer......Page 273
9 LOBS: Best Practices......Page 275
Loading XML Documents Into LOBs With SQL*Loader......Page 276
LOB Performance Guidelines......Page 279
Temporary LOB Performance Guidelines......Page 280
The Correct Procedure......Page 283
Preventing Generation of Redo Space During Migration......Page 284
10 Internal Persistent LOBs......Page 285
Use Case Model: Internal Persistent LOBs Operations......Page 286
Creating Tables Containing LOBs......Page 291
Creating a Table Containing One or More LOB Columns......Page 293
SQL: Create a Table Containing One or More LOB Columns......Page 295
Creating a Table Containing an Object Type with a LOB Attribute......Page 298
SQL: Creating a Table Containing an Object Type with a LOB Attribute......Page 300
Creating a Nested Table Containing a LOB......Page 303
SQL: Creating a Nested Table Containing a LOB......Page 305
Inserting One or More LOB Values into a Row......Page 306
Inserting a LOB Value using EMPTY_CLOB() or EMPTY_BLOB()......Page 308
Java (JDBC): Inserting a Value Using EMPTY_CLOB() / EMPTY_BLOB()......Page 310
Inserting a Row by Selecting a LOB From Another Table......Page 312
SQL: Inserting a Row by Selecting a LOB from Another Table......Page 313
Inserting a Row by Initializing a LOB Locator Bind Variable......Page 314
Java (JDBC): Inserting a Row by Initializing a LOB Locator Bind Variable......Page 315
Loading Initial Data into a BLOB, CLOB, or NCLOB......Page 317
Loading a LOB with BFILE Data......Page 319
Java (JDBC): Loading a LOB with Data from a BFILE......Page 321
Open: Checking If a LOB Is Open......Page 324
Checking If a BLOB is Opened......Page 325
LONGs to LOBs......Page 328
LONG to LOB Migration Using the LONG-to-LOB API......Page 329
To Convert LONG to CLOB, Use ALTER TABLE......Page 330
LONG to LOB Copying, Using the TO_LOB Operator......Page 331
SQL: Copying LONGs to LOBs Using TO_LOB Operator......Page 332
Checking Out a LOB......Page 335
Java (JDBC): Checking Out a LOB......Page 337
Checking In a LOB......Page 339
Java (JDBC): Checking in a LOB......Page 340
Displaying LOB Data......Page 343
Java (JDBC): Displaying LOB Data......Page 344
Reading Data from a LOB......Page 347
Java (JDBC): Reading Data from a LOB......Page 349
Reading a Portion of the LOB (substr)......Page 352
Java (JDBC): Reading a Portion of the LOB (substr)......Page 353
Comparing All or Part of Two LOBs......Page 356
Java (JDBC): Comparing All or Part of Two LOBs......Page 357
Patterns: Checking for Patterns in the LOB (instr)......Page 360
Java (JDBC): Checking for Patterns in the LOB (instr)......Page 361
Length: Determining the Length of a LOB......Page 364
Java (JDBC): Determining the Length of a LOB......Page 365
Copying All or Part of One LOB to Another LOB......Page 367
Java (JDBC): Copying All or Part of One LOB to Another LOB......Page 368
Copying a LOB Locator......Page 371
Java (JDBC): Copying a LOB Locator......Page 372
Equality: Checking If One LOB Locator Is Equal to Another......Page 374
Java (JDBC): Checking If One LOB Locator Is Equal to Another......Page 375
Initialized Locator: Checking If a LOB Locator Is Initialized......Page 377
Character Set ID: Determining Character Set ID......Page 379
Character Set Form: Determining Character Set Form......Page 381
Appending One LOB to Another......Page 383
Java (JDBC): Appending One LOB to Another......Page 385
Append-Writing to the End of a LOB......Page 387
Java (JDBC): Writing to the End of (Append-Write to) a LOB......Page 389
Writing Data to a LOB......Page 391
Java (JDBC): Writing Data to a LOB......Page 394
Trimming LOB Data......Page 396
Java (JDBC): Trimming LOB Data......Page 397
Erasing Part of a LOB......Page 402
Java (JDBC): Erasing Part of a LOB......Page 403
Enabling LOB Buffering......Page 406
Flushing the Buffer......Page 408
Disabling LOB Buffering......Page 410
Three Ways to Update a LOB or Entire LOB Data......Page 412
Updating a LOB with EMPTY_CLOB() or EMPTY_BLOB()......Page 413
For Binds of More Than 4,000 Bytes......Page 414
SQL: UPDATE a LOB with EMPTY_CLOB() or EMPTY_BLOB()......Page 415
Updating a Row by Selecting a LOB From Another Table......Page 416
SQL: Update a Row by Selecting a LOB From Another Table......Page 417
Updating by Initializing a LOB Locator Bind Variable......Page 418
Java (JDBC): Updating by Initializing a LOB Locator Bind Variable......Page 419
Deleting the Row of a Table Containing a LOB......Page 422
SQL: Delete a LOB......Page 423
11 Temporary LOBs......Page 425
Use Case Model: Internal Temporary LOBs......Page 426
Temporary LOB Locators Can be IN Values......Page 430
Temporary LOB Data is Stored in Temporary Tablespace......Page 431
Temporary Tablespace......Page 432
Locators and Semantics......Page 433
Features Specific to Temporary LOBs......Page 434
Security Issues with Temporary LOBs......Page 435
Using JDBC and Temporary BLOBs......Page 436
Using JDBC and Temporary CLOBs......Page 437
Creating a Temporary LOB......Page 438
Java (JDBC): Creating a Temporary BLOB......Page 439
Java (JDBC): Creating a Temporary CLOB......Page 440
Checking If a LOB is Temporary......Page 441
Java (JDBC): Checking if a BLOB is Temporary......Page 442
Java (JDBC): Checking if a CLOB is Temporary......Page 443
Freeing a Temporary LOB......Page 444
Java (JDBC): Freeing a Temporary BLOB......Page 445
Java (JDBC): Freeing a Temporary CLOB......Page 446
Java (JDBC): Creating and Freeing a Temporary CLOB Using TemporaryClob.java......Page 447
Loading a Temporary LOB with Data from a BFILE......Page 448
Determining If a Temporary LOB Is Open......Page 450
Displaying Temporary LOB Data......Page 452
Reading Data from a Temporary LOB......Page 454
Reading Portion of Temporary LOB (Substr)......Page 457
Comparing All or Part of Two (Temporary) LOBs......Page 459
Determining If a Pattern Exists in a Temporary LOB (instr)......Page 461
Finding the Length of a Temporary LOB......Page 463
Copying All or Part of One (Temporary) LOB to Another......Page 465
Copying a LOB Locator for a Temporary LOB......Page 467
Is One Temporary LOB Locator Equal to Another......Page 469
Determining if a LOB Locator for a Temporary LOB Is Initialized......Page 471
Finding Character Set ID of a Temporary LOB......Page 473
Finding Character Set Form of a Temporary LOB......Page 475
Appending One (Temporary) LOB to Another......Page 477
Write-Appending to a Temporary LOB......Page 479
Writing Data to a Temporary LOB......Page 481
Trimming Temporary LOB Data......Page 484
Erasing Part of a Temporary LOB......Page 486
Enabling LOB Buffering for a Temporary LOB......Page 488
Flushing Buffer for a Temporary LOB......Page 490
Disabling LOB Buffering for a Temporary LOB......Page 492
12 External LOBs (BFILEs)......Page 495
Use Case Model: External LOBs (BFILEs)......Page 496
Initializing a BFILE Locator......Page 499
Examples......Page 500
BFILENAME() and Initialization......Page 501
Ownership and Privileges......Page 502
SQL DDL for BFILE Security......Page 503
Guidelines for DIRECTORY Usage......Page 504
BFILEs in Shared Server (Multi-Threaded Server — MTS) Mode......Page 505
Guidelines......Page 506
General Rule......Page 507
Three Ways to Create a Table Containing a BFILE......Page 508
Creating a Table Containing One or More BFILE Columns......Page 509
SQL: Creating a Table Containing One or More BFILE Columns......Page 510
Creating a Table of an Object Type with a BFILE Attribute......Page 512
SQL: Creating a Table of an Object Type with a BFILE Attribute......Page 513
Creating a Table with a Nested Table Containing a BFILE......Page 515
SQL: Creating a Table with a Nested Table Containing a BFILE......Page 516
Three Ways to Insert a Row Containing a BFILE......Page 517
INSERT a Row Using BFILENAME()......Page 518
Ways BFILENAME() is Used to Initialize BFILE Column or Locator Variable......Page 519
SQL: Inserting a Row by means of BFILENAME()......Page 520
Java (JDBC): Inserting a Row by means of BFILENAME()......Page 521
INSERT a BFILE Row by Selecting a BFILE From Another Table......Page 523
SQL: Inserting a Row Containing a BFILE by Selecting a BFILE From Another Table......Page 524
Inserting a Row With BFILE by Initializing a BFILE Locator......Page 525
Java (JDBC): Inserting a Row Containing a BFILE by Initializing a BFILE Locator......Page 527
Loading Data Into External LOB (BFILE)......Page 529
Data file (sample9.dat)......Page 531
Data file (sample10.dat)......Page 532
Loading a LOB with BFILE Data......Page 533
Specify Amount Parameter to be Less than the Size of the BFILE!......Page 534
Java (JDBC): Loading a LOB with BFILE Data......Page 535
Two Ways to Open a BFILE......Page 536
Close Files After Use!......Page 537
Opening a BFILE with FILEOPEN......Page 538
Java (JDBC): Opening a BFILE with FILEOPEN......Page 539
Opening a BFILE with OPEN......Page 542
Java (JDBC): Opening a BFILE with OPEN......Page 543
Specify the Maximum Number of Open BFILEs: SESSION_MAX_OPEN_FILES......Page 546
Checking If the BFILE is Open with FILEISOPEN......Page 548
Java (JDBC): Checking If the BFILE is Open with FILEISOPEN......Page 549
Checking If a BFILE is Open Using ISOPEN......Page 551
Java (JDBC): Checking If the BFILE is Open with ISOPEN......Page 552
Displaying BFILE Data......Page 554
Java (JDBC): Displaying BFILE Data......Page 555
Reading Data from a BFILE......Page 558
The Amount Parameter......Page 559
Java (JDBC): Reading Data from a BFILE......Page 560
Reading a Portion of BFILE Data (substr)......Page 563
Java (JDBC): Reading a Portion of BFILE Data (substr)......Page 564
Comparing All or Parts of Two BFILES......Page 567
Java (JDBC): Comparing All or Parts of Two BFILES......Page 568
Checking If a Pattern Exists (instr) in the BFILE......Page 571
Java (JDBC): Checking If a Pattern Exists (instr) in the BFILE......Page 572
Checking If the BFILE Exists......Page 575
Java (JDBC): Checking If the BFILE Exists......Page 576
Getting the Length of a BFILE......Page 578
Java (JDBC): Getting the Length of a BFILE......Page 579
Copying a LOB Locator for a BFILE......Page 582
Java (JDBC): Copying a LOB Locator for a BFILE......Page 583
Determining If a LOB Locator for a BFILE Is Initialized......Page 585
Determining If One LOB Locator for a BFILE Is Equal to Another......Page 587
Java (JDBC): Determining If One LOB Locator for a BFILE Is Equal to Another......Page 588
Getting DIRECTORY Alias and Filename......Page 591
Java (JDBC): Getting Directory Alias and Filename......Page 592
Three Ways to Update a Row Containing a BFILE......Page 595
Updating a BFILE Using BFILENAME()......Page 596
SQL: Updating a BFILE by means of BFILENAME()......Page 598
Updating a BFILE by Selecting a BFILE From Another Table......Page 599
SQL: Updating a BFILE by Selecting a BFILE From Another Table......Page 600
Updating a BFILE by Initializing a BFILE Locator......Page 601
Java (JDBC): Updating a BFILE by Initializing a BFILE Locator......Page 602
Two Ways to Close a BFILE......Page 605
Closing a BFILE with FILECLOSE......Page 607
Java (JDBC): Closing a BFile with FILECLOSE......Page 608
Closing a BFILE with CLOSE......Page 611
Java (JDBC): Closing a BFile with CLOSE......Page 612
Closing All Open BFILEs with FILECLOSEALL......Page 615
Java (JDBC): Closing All Open BFiles with FILECLOSEALL......Page 616
Deleting the Row of a Table Containing a BFILE......Page 619
TRUNCATE......Page 620
13 Using OraOLEDB to Manipulate LOBs......Page 621
Rowset Object......Page 622
Writing Data to a LOB Column With AppendChunk()......Page 623
ADO and LOBs Example 1: Inserting LOB Data From a File......Page 624
14 LOBs Case Studies......Page 627
Building a Multimedia Repository......Page 628
How this Application Uses LOBs......Page 629
Populating the Repository......Page 630
Example 1: Inserting a Word document into a BLOB Column using PL/SQL......Page 631
How the Index Was Built on Table sam_emp, resume Column......Page 632
MyServletCtx.java......Page 633
Retrieving Data from the Repository......Page 635
MyServlet.java......Page 636
First Steps Solution......Page 638
Use Case Diagrams......Page 641
Hot Links in the Online Versions of this Document......Page 647
B The Multimedia Schema Used for Examples in This Manual......Page 649
A Typical Multimedia Application......Page 650
The Multimedia Schema......Page 651
Table Multimedia_Tab......Page 652
Script for Creating the Multimedia Schema......Page 654
Index......Page 659