MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle LOB Processing

with 13 comments

This page demonstrates how to upload, store, and manage CLOBs (Character Large OBjects) and BLOBs (Binary Large OBjects). It provides some instructions that supplement Chapter 8 in my Oracle Database 11g PL/SQL Programming book.

Before you begin these steps, you should have already installed Zend Server Community Edition. If you haven’t done so, please click here for instructions.

If you find any problems, please let me know. I’ll be happy to fix them.

Written by maclochlainn

June 29th, 2009 at 8:15 pm

Posted in Uncategorized

13 Responses to 'Oracle LOB Processing'

Subscribe to comments with RSS or TrackBack to 'Oracle LOB Processing'.

  1. how do I query to see if a blob is loaded in a column?


    30 Jun 09 at 3:53 pm

  2. I’ll put the following query in the troubleshooting section but here it is. You use the GETLENGTH function from the DBMS_LOB package, like this:

    SELECT   i.item_id
    ,        dbms_lob.getlength(i.item_clob)
    ,        dbms_lob.getlength(i.item_blob)
    FROM     item i
    WHERE    i.item_desc IS NOT NULL
    OR       i.item_blob IS NOT NULL;


    30 Jun 09 at 4:05 pm

  3. In ZEND I don’t get any errors when I should. So I fixed it by changing a line in php.ini file to say:

    display_errors = ON

    This enables it because by default it is off.


    1 Jul 09 at 10:52 am

  4. I’m stumped.

    I followed all the instructions, I checked the credentials, changed both db connection strings to localhost/xe. There is nothing in the temp directory. When I do a describe on the item table there is a zero in the item_desc column, and nothing (null) in the item_blob column.


    9 Jul 09 at 8:26 pm

  5. If there’s nothing in the temp directory, then the database hasn’t been touched. The process_uploaded_file() function takes the uploaded file and moves it from the temporary cache to the temp directory.

    In your environment, this appears to not be working. This happens before any database activity. Is the temp directory a subdirectory of the directory where you’ve placed the PHP code? If not, you’d get this error unless you rewrite the function provided. I did it that way so that it would work on Linux/Unix or Windows systems. Please let me know if I can help more.

      function process_uploaded_file()
        // Declare a variable for file contents.
        $contents = "";
        // Define the upload file name for Windows or Linux.
        if (ereg("Win32",$_SERVER["SERVER_SOFTWARE"]))
          $upload_file = getcwd()."\\temp\\".$_FILES['userfile']['name'];
          $upload_file = getcwd()."/temp/".$_FILES['userfile']['name'];
        // Check for and move uploaded file.
        if (is_uploaded_file($_FILES['userfile']['tmp_name']))
        // Open a file handle and suppress an error for a missing file.
        if ($fp = @fopen($upload_file,"r"))
          // Read until the end-of-file marker.
          while (!feof($fp))
            $contents .= fgetc($fp);
          // Close an open file handle.
        // Return file content as string.
        return $contents;


    9 Jul 09 at 10:38 pm

  6. Wow, thanks.

    Moving the temp directory from being even with the folder containing the php to beneath it helped out. I now see the text after I load the text, and the text file is in the temp folder, but the image is still having trouble. It now displays this:



    9 Jul 09 at 10:58 pm

  7. You need to call the URL not a file reference, change it to http://localhost/lob/webcode/UploadItemBlobForm.htm and it should work. When you attempt to open a PHP file with:


    It should output what you got. The reason is that the URL (URI) is passed by the Apache server to the Zend Server, which then interprets the PHP. That can’t happen if you attempt to open it as a local file. Also, the code is written to use a POST method. That means you should call the form, which then calls the PHP code through a POST method operation, like:

    <form id="uploadForm"

    This should fix the problem. Let’s hope. 🙂


    9 Jul 09 at 11:08 pm

  8. SWEEEEET!!!!

    It is working!!!



    9 Jul 09 at 11:16 pm

  9. I’ve updated the code to PHP 5.3 and added all components in drop down text. The zip files aren’t yet updated but when I fix them, I’ll update this comment.


    27 Feb 10 at 1:56 pm

  10. Hi,
    I follow the above installation code but I get this error:

    Warning: oci_execute() [FUNCTION.oci-EXECUTE]: ORA-06550: line 1, COLUMN 21: PLS-00201: identifier 'WEB_LOAD_BLOB_FROM_FILE' must be declared ORA-06550: line 1, COLUMN 21: PL/SQL: Statement ignored IN /var/www/html/UploadItemBlob.php ON line 40

    Can someone tell me there is the mistake?


    23 Feb 11 at 6:41 am

  11. Peter, you skipped a step. You need to download the two procedures and compile them (run them) in the target schema. They’re noted below. The web_load_clob_from_file procedure handles large character objects and the web_load_blob_from_file procedure handles large binary objects.

    Clob files

    CREATE OR REPLACE PROCEDURE web_load_clob_from_file  
    ( item_id_in IN     NUMBER
    , descriptor IN OUT CLOB ) IS
      -- A FOR UPDATE makes this a DML transaction.
      UPDATE    item
      SET       item_desc = empty_clob()
      WHERE     item_id = item_id_in
      RETURNING item_desc INTO descriptor;
    END web_load_clob_from_file;

    Blob files

    CREATE OR REPLACE PROCEDURE web_load_blob_from_file  
    ( item_id_in IN     NUMBER
    , descriptor IN OUT BLOB ) IS
      -- A FOR UPDATE makes this a DML transaction.
      UPDATE    item
      SET       item_blob = empty_blob()
      WHERE     item_id = item_id_in
      RETURNING item_blob INTO descriptor;
    END web_load_blob_from_file;


    23 Feb 11 at 12:18 pm

  12. My mistake, would you show me that is the table structure for BLOB?

    There is another problem.

    Warning: OCI-Lob::save() [FUNCTION.OCI-Lob-save]: OCI_INVALID_HANDLE IN /var/www/html/UploadItemBlob.php ON line 53


    24 Feb 11 at 8:16 am

  13. Peter, The base script for the video store example can be downloaded from the McGraw-Hill web site. Click on the link and it will download the code for the Oracle Database 11g book, click on the Introduction folder, and then the Code folder within it. Grab and run create_store.sql script. This script also seeds 93 records in the ITEM table. The ITEM table created by that script will support what you need, but it uses system assigned names for the Clob and Blob segments. That’s suboptimal but fine for sample code. The proper way to define the ITEM table is found in this other blog post of mine.

    The blog post assumes you have a basic ITEM table and provides this syntax which allows you to add the necessary columns to it:

    ALTER TABLE item
    ADD (item_desc CLOB)
    ADD (item_blob BLOB);

    I hope this helps you. Also, don’t forget to create a temp folder in the htdocs folder because thats where the upload code positions the image before uploading it to the database. A Java module can clean that folder up, which you can find in this blog page.

    Let me know how it works for you, and if you will could you update your two forum posts. Otherwise the Oracle Product Manager will ping me again and ask me to do it. Thanks, Michael


    24 Feb 11 at 12:32 pm

Leave a Reply