Introduction to Oracle Database Limitations and How to Deal with Them
To store large objects in databases, Oracle has a data type named LOB. This allows us to store up to 4GB of data in an Oracle database (9i).
LOB Data type Description
LOB Storage Option
Oracle Restrictions for LOBs Storage
Oracle databases have a limitation that more than 4kb of data cannot be stored in line for any LOB data type as this will depreciate the performance of the search/retrieve operation on that table due to a full table scan. In that case the data will be stored in the table space and not in the table. If you want to store LOB > 4KB in size then it gets stored in table space. But the retrieval of those LOB > 4kb is not the same as that of other rows.
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!