Storing LOB Greater Than 4kb in Oracle Database using PHP Page 2

The PHP way to overcome the Oracle restriction is to store the LOB data which is greater in size i.e. if the size of the LOB data > 4KB, the approach for storing the data is bit different compare to the existing storage procedure for normal data. It requires a LOB locator to be initialized and the access of LOB data is controlled using LOB locator. PHP provides the simplest approach to handle storage/retrieval of LOB > 4KB. Please find below the step-by-step explanation of how to store the LOB data of size > 4kb in the Oracle database using PHP.

Step1: create a sample table with any of the fields as CLOB or BLOB. Create a Sample table: TEST_TABLE

CREATE TABLE TEST_TABLE
( 
 UNIQUE_KEY VARCHAR2(15 BYTE),
 CLOB_DATA CLOB,
 )

Step2: Initialize LOB Locator. To initialize the LOB Locator we can use the Oracle function EMPTY_CLOB() or EMPTY_BLOB() based upon the LOB data type. Once initialized then we can insert/update the LOB of size more than 4KB.

CREATE TABLE TEST_TABLE
INSERT INTO TEST_TABLE (UNIQUE_KEY, CLOB_DATA) VALUES (1, empty_clob());
 Or 
INSERT INTO TEST_TABLE (UNIQUE_KEY, BLOB_DATA) VALUES (1, empty_blob());

Step3: update the LOB locator with LOB data using PHP functions updateClob() or updateBlob(). PS: Here we are discussing the approach of PHP but the concept is same for all technologies. There are 2 functions to perform this update operation in PHP using the ADODB abstract class library.

UpdateClob(): This allows to store a CLOB value in the table. There are various parameter passed to the function.

Function Definition

UpdateClob($TableName,$ColumnName,$CLOBValue,$UniqueIdentifier)
$ TableName: name of the table in which the LOB data to be stored
$ColumnName: name of the column in which LOB data to be stored
$LOBValue: Actual LOB data
$UniqueIdentifier: unique identifier to be used while updating the LOB data in the table.
This to be used in where condition while retrieving the LOB data.

UpdateBlob(): This allows to store a BLOB value in the table. There are various parameter passed to the function.

Function Declaration

UpdateBlob($TableName,$ColumnName,$BLOBValue,$UniqueIdentifier)
$ TableName: name of the table in which the LOB data to be stored
$ColumnName: name of the column in which LOB data to be stored
$BLOBValue: Actual LOB data
$UniqueIdentifier: unique identifier to be used while updating the LOB data in the table.

Example to store CLOB of size > 4kb

<?php

require_once("adodb.inc.php");

$dbuser = "Scott";
$dbpassword = "tiger";
$sid ='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST=<Host_name>)(PORT=1521)))(CONNECT_DATA = (SID=<SID>)))';

// using OCI8 extension to connect to database
$conn = &ADONewConnection('oci8');

$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
$conn -> NConnect(false, $dbuser, $dbpassword, $sid);

if (FALSE == $conn)
        {
     
                $this->ErrorMsg="Could not connect to database. Database error was:n".$GLOBALS["conn"]->ErrorMsg();
                return false;
        }
// $lobdata have a lob data up to 4GB
$lobData = "Data_more_than_4kb";

// Inserted the record in database with after initializing lob locator using empty_clob()
$conn->Execute('INSERT INTO TEST_TABLE (UNIQUE_KEY , CLOB_DATA ) VALUES (1, empty_clob())');

// updating the already inserted lob locator to point to correct lob data in table space
$conn->UpdateClob('TEST_TABLE','CLOB_DATA',$lobData,'UNIQUE_KEY = 1');

//retrieving Lob Data from table which was inserted above
 
$sql="SELECT * FROM TEST_TABLE WHERE UNIQUE_KEY='1' ";

$rs=$conn->Execute($sql);

?>

The above example shows how to store a CLOB > 4KB up to 4GB to the Oracle database using the updateClob() function provided by the ADODB abstract library in PHP. Similar implementation can be done for BLOB data type using the updateBlob() function. Till next time, good luck and good coding!