45
CHAPTER 13
U
SING
L
ARGE
O
BJECTS
IN
OCI8
Oracle Character Large Object (CLOB) and Binary Large Object (BLOB) types can be used for
very large amounts of data. They can be used for table columns and as PL/SQL variables. A
pre-supplied DBMS_LOB package makes manipulation in PL/SQL easy. OCI8 LOB methods
allow storing and fetching LOB data in PHP
Oracle also has a BFILE type for large objects stored outside the database.
Working With LOBs
In successive versions, the Oracle database has made it easier to work with LOBs. Along the
way “Temporary LOBs” were added, and some string-to-LOB conversions are now transparent
so data can be handled directly as strings. Develop and test your LOB application with the
Oracle client libraries and database that will be used for deployment so you can be sure all
the expected functionality is available.
When working with large amounts of data, set memory_limit appropriately in php.ini
otherwise PHP may terminate early. When reading or writing files to disk, check if
open_basedir allows file access.
These example show BLOBs. Using CLOBs is almost identical to using BLOBs: the
descriptor type becomes OCI_D_CLOB, the bind type becomes OCI_B_CLOB, and tables must
obviously contain a CLOB column.
The examples use a table created in SQL*Plus containing a BLOB column called BLOBDATA:
SQL> create table mybtab (blobid number primary key, blobdata blob);
Note querying BLOB columns in SQL*Plus is not possible unless SQL*Plus 11g is used, where it
will display a hexadecimal version of the data. Tables with CLOB columns can be queried in all
versions of SQL*Plus. The output of BLOB and CLOB data can be controlled in SQL*Plus with
the SET LONG command. The default value of 80 means that only the first 80 characters of
data will be displayed by a query.
LOB database storage and access options can be configured at table creation time, or with
ALTER TABLE. Frequently accessed LOBs will benefit from monitoring their use and adjusting
their configuration. One common tuning step is to turn on LOB caching:
SQL> alter table mybtab modify lob (blobdata) (cache);
In Oracle 11g you can optionally create LOBs with the storage option SECUREFILE to take
advantage of LOB SecureFile features such as deduplication and compression.
Inserting and Updating LOBs
In PHP, LOBs are generally manipulated using a descriptor. PHP code to insert into MYBTAB is:
Script 102: blobinsert.php
<?php
$c = oci_connect('hr', 'welcome', 'localhost/XE');
233