How to get the contents of an Oracle CLOB data field in PHP

The Oracle “CLOB” (Character Large Object) is a data type used to store up to 4 Gigabytes of text. Retrieving the contents of a CLOB is not as intuitive as you might think.

Let’s say you have a CLOB field/column named “mychars” in an Oracle DB table named “mytable” along with some other fields. You want to simply echo out the text in the “mychars” field:

<?php
    $id = '3';
    $conn = oci_connect('myusr', 'mypass', 'mydb');
    if (!$conn){
        echo 'Connection error.';
    }
    $sql = 'SELECT * FROM mytable WHERE myid=:id';
    $stid = oci_parse($conn, $sql);
    oci_bind_by_name($stid, ":id", $id);
    $result = oci_execute($stid);
    if($result !== false){
        while($row = oci_fetch_assoc($stid)){
            echo $row['mychars'];
        }
    }
?>

The above code will give you an error that looks like the following:

Catchable fatal error: Object of class OCI-Lob could not be converted to string in somefile.php on line 14

If you try to do a print_r() on the CLOB in an attempt to figure out what you are dealing with you will get something that looks like:

OCI-Lob Object ( [descriptor] => Resource id #3 )

This is because a Lob object is returned instead of the contents of the CLOB.

To get the CLOB contents you will need to call the load() or read() methods on the returned object. The latter will require the length of data to read in bytes but has the advantage of not being limited by the script memory limit:

<?php
    $id = '24382';
    $conn = oci_connect('myusr', 'mypass', 'mydb');
    if (!$conn){
        echo 'Connection error.';
    }
    $sql = 'SELECT * FROM mytable WHERE myid=:id';
    $stid = oci_parse($conn, $sql);
    oci_bind_by_name($stid, ":id", $id);
    $result = oci_execute($stid);
    if($result !== false){
        while($row = oci_fetch_assoc($stid)){
            echo $row['mychars']->load();
            //or
            echo $row['mychars']->read(2000);
        }
    }
?>

9 thoughts on “How to get the contents of an Oracle CLOB data field in PHP”

  1. Another point is that if your data from the database is null then php will set it as a string otherwise the data will be an object.

    Just make sure your doing is_object before you use ->load or ->read.

  2. I think I browsed around 100 different sites for the solution. Finally I got the solution through your site. Thank you

  3. Hello everyone,

    I am also facing the exact problem. And I did the same above. But still nothing displays on the page. Please do help me.

    Can I call directly print $a->load();
    Or do I need to write any function for load()?? Please please do suggest me. I am stucked in there.

Leave a Reply

Your email address will not be published. Required fields are marked *