Remote DBA Plans
Remote DBA Service

Large Objects (LOB)

Oracle Tips by Burleson Consulting

So far, numbers, strings and cursors have been used. The last example in this section deals with loading files into the database. The file to upload is the full text of E. A. Poe’s poem, “The Raven”. Oracle can store up to 4096 bytes into a VARCHAR2 field. While “The Raven” is certainly larger than this field, a large object of the CLOB type (CLOB = Character Large Object) is needed.

First, a table containing a CLOB column must be created because SCOTT schema does not provide one. The following example shows the CREATE TABLE command used to create the table “POETRY” in the SCOTT schema:

Example 18

        CREATE TABLE POETRY (
        FILE_ID NUMBER(5) NOT NULL ,
        FILE_DESC VARCHAR2(255) NOT NULL,
        FILE_DATA CLOB)
        LOB (FILE_DATA) STORE AS (
             TABLESPACE LOBS
             DISABLE STORAGE IN ROW );

To further ease up loading files, a counter needs created. Oracle calls counters “sequences”, which are persistent database objects that “remember” the last number and return the next number.   Here is the SQL statement used:

CREATE SEQUENCE POEM_ID_SEQ START WITH 1 NOCYCLE CACHE 512;

To populate the FILE_ID column automatically, an additional Oracle object called “trigger” is needed. Triggers are PL/SQL procedures that are run automatically by the database, based on certain conditions. There are many types of triggers that serve various purposes in the world of Oracle.

The general description of all types and uses of PL/SQL triggers is beyond the scope of this book. Database triggers are described in detail in the Oracle literature, and in particular in the following book: Oracle® Database Concepts 9.2

The trigger created by the statement below will fire after an INSERT statement is executed and before the other conditions are checked:

Example 18 (Cont.)

    create or replace trigger poetry_pre_ins
    before insert on poetry
    for each row
  begin
       SELECT poem_id_seq.nextval
       INTO :new.file_id
       FROM dual;
    end;
    /

Why is such a complex structure necessary? It isn’t. A table and a form to load a file as CLOB into the table could be used, but that would not be a realistic scenario.

There are many principles for good database design, but the most important one is to always be able to uniquely identify records. In order to do that, a column is needed that is different for every record entered and not left to the user to determine uniqueness.

Another thing that could be used to that end is system date. It is embodied in the Oracle function SYSDATE, and it makes assumptions regarding the frequency of uploads and the time granularity. Oracle date arithmetic is precise up to the granularity of seconds, and such an assumption means that two users cannot upload files simultaneously or less then a second apart from each other. In order to enforce uniqueness, there is one more SQL commandthat must be executed:

ALTER TABLE POETRY ADD CONSTRAINT POETRY_PK
PRIMARY KEY(FILE_ID)
USING INDEX TABLESPACE INDX;

The database schema is now ready for uploads. Note that only a short description of the file and the content of the file need to be entered. The file ID will be entered automatically, from the trigger, without human intervention. In order to do this, Example 13 can again be modified to provide the database login and to invoke the next file, the HTML upload form called file_load.html. The usual call to the header function now reads:

header(“ Location: load_file.html”);

The example18.php file is not shown here as it only differs from example13a.php in a single line (seen above). It is available on the accompanying CD. Now, it is time to create an upload form. This can be done using an open source Linux rapid HTML development tool called Bluefish. More information about the Bluefish tool can be found at the following web page: http://bluefish.openoffice.nl.

Example 18 (Cont.)

<html>
<head>
   <title>File Upload</title>
</head>
<body>
<br><br><br><br><br><br><br>
<div align="center">
    <h3>File Upload</h3>
</div>
<hr>
<form action="load_file.php"
        method="post"
        enctype="multipart/form-data">
    File Description: <input type="text"
                             name="desc" size="40"
                             maxlength="255">
<p>
    File       
                 
             :
    <input type="file" name="file"><br>
    <input type="submit"
           name="load"
           value="Load"><br></p>
</form>
<hr>
</body>
</html>

It is a very simple form which looks like the following when displayed:

This is simply a bare bones form needed to invoke the PHP script which does all the work. First thing worth noticing is the fact that the FILE input type includes a text field and browse button and that the button doesn’t need to be manually added. The second thing worth noticing is the encoding type (enctype=”multiptart/form-data”) part of the form tag, without which the $_FILES global array will not be functional. Here is the load_file.php from the “action” qualifier in the <form> tag which does all the work:

Example 18( Cont.)

<?php
require_once('OCI_Session.php');
session_start();
$dbh=$_SESSION['dbh'];
$INS="insert into poetry(file_desc,file_data)
      values (:dscr,empty_clob())
      returning file_data into :loc";
try {
      $dbh->refresh();
      if (!$dbh->db) {
         $e=$dbh->err;
         throw new Exception ('CONN:'.$e['message']);
      }
      $clob=oci_new_descriptor($dbh->db, OCI_D_LOB);
      if (!$clob) {
         $e=$dbh->err;
         throw new Exception ('DESCR'.$e['message']);
      }
      $sth=oci_parse($dbh->db,$INS);
      if (!$sth) {
         $e=oci_error($sth);
         throw new Exception ('PARSE:'.$e['message']);
      }
      if (!oci_bind_by_name($sth,":dscr",$_POST['desc'],255)) {
         $e=oci_error($sth);
         throw new Exception ('BIND DESC:'.$e['message']);
      }
      if (!oci_bind_by_name($sth,":loc",$clob,-1,OCI_B_CLOB)) {
         $e=oci_error($sth);
         throw new Exception ('BIND CLOB:'.$e['message']);
      }
      if (!oci_execute($sth,OCI_DEFAULT)) {
          $e=oci_error($sth);
          throw new Exception ('EXEC:'.$e['message']);
      }
}
catch (Exception $e) {
       if ($dbh->db) { $dbh->rollback(); }
       die($e->getMessage());
}
if ($clob->savefile($_FILES['file']['tmp_name'])) {
    $dbh->commit();
    ?>
    <center>
    <h2> File <?=$_FILES['file']['name']?> uploaded
successfully!</h2>
    </center>
    <?php
} else {
    $dbh->rollback();
    $e=oci_error($clob);
    die($e['message']);
}
?>

There are many important things to notice in this example. The first one is the insert statement itself. The POETRY table contains a LOB locator, an entity which has to be initialized using the empty_clob() PL/SQL function. It cannot be initialized using the NULLvalue.

Second thing to note is the “returning” clause, which makes the newly initialized LOB locator available to the program. This saves the user a network trip because without using the “returning” clause, the user would have to select the record back with another select statement in order to get hold of the LOB locator.

The third crucial thing to note is the use of the oci_execute()function with the OCI_DEFAULT flag. The oci_execute() function performs an implicit commit. LOB descriptors cannot span transactions, which means that the user has to prevent oci_execute() from performing a commit and ending the transaction. The only way to do this is to use the OCI_DEFAULT flag. This also means that the user must perform an explicit commit or rollback if wanting the transaction to make its results permanent.

Another interesting thing here is a bind: LOB descriptor is allocated and bound to the $clob variable using the OCI_B_CLOB flag and size -1, in the complete analogy to the method used for binding the cursor descriptor in the example 17.

In addition, LOB descriptors are object types with many useful methods, one among which is the “savefile” method used in this example. LOB descriptors have methods to read, write, change position and inform about position. They also have methods for importing files into the database. All these methods are described in the online manuals.

Last but not least is the use of the $_FILES global array. The $_FILES global array is indexed by two indexes: the name of the field in the form (load_file.html) and pre-defined values, of which two are used:

$_FILES[‘field_name’][‘tmp_name’] is the name of the temporary file used by the web server to upload the user’s file.
$_FILES[‘field_name’][‘name’] is the name of the uploaded file on the client machine.
Here is the list of the things that $_FILES arraycan reveal about the uploaded file, right from the online manual:
$_FILES[‘userfile’][‘name’] - The original name of the file on the client machine.
$_FILES[‘userfile’][‘type’] - The mime type of the file, if the browser provided this information. An example would be “image/gif”.
$_FILES[‘userfile’][‘size’] - The size, in bytes, of the uploaded file.
$_FILES[‘userfile’][‘tmp_name’] - The temporary filename of the file in which the uploaded file was stored on the server.
$_FILES[‘userfile’][‘error’] - The error code associated with this file upload. This element was added in PHP 4.2.0

So, what does the final page looks like? It looks like the other successful pages in this book:

The database reveals what was loaded into the database:

SQL> select file_id id,file_desc,
2 dbms_lob.getlength(file_data) len
3 from poetry
4 order by id;

ID FILE_DESC                                       LEN
---- ---------------------------------------- ----------
   1 E.A. Poe "The Raven"                          13990
   2 R. Frost, "Stopping by Woods on a Snowy        7780
     Evening"

3 Monty Python, "Life of Brian" lyrics 1918
4 E. A. Poe, "The Raven". 13990

DBMS_LOB is a PL/SQL package supplied by Oracle Corp. and the getlength() function is one among its numerous functions. It returns the length of the LOB data, in bytes. The DBMS_LOB package is fully described in Oracle manuals. The function to get length can be used to quickly check whether the load was successful. It also showed that the same file can be loaded twice, despite having a primary key.

The primary key is an artificial “ID” column. In order to fully identify the file, SYSDATE, username, client machine name and even checksum may be utilized. Yet doing so would complicate both the data model and the script complexity beyond the level appropriate for this book.

SEE CODE DEPOT FOR FULL SCRIPTS