Oracle 9i. Application Developer's Guide - Large Objects (LOBs) using OCI (Oracle Call Interface)

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