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.

Create directories or folders, and position code ↓

This section provides you with instructions on how to position the code components in Windows, at least for the newbie. If you’re on Linux, you probably know how to do most if not all of this already. Likewise, if you already know how to put things in the right place, please choose your own locations.

  1. Create a LOB directory for the PHP files inside the htdocs directory.

oraclelobsetup1

Load an Oracle CLOB column to the Oracle database ↓

This is a copy of the three files required to load a large string to an Oracle database into a CLOB data type. The code is in clear text because somebody asked for it. They’re nervous about zip files. Click the title above to expand all the code text.

Credentials.inc

If you’re using Oracle Database 10g Express Edition, you should use the following in your Credentials.inc file:

1
2
3
4
5
6

<?php
  // Connection variables.
  define('SCHEMA',"student");
  define('PASSWD',"student");
  define('TNS_ID',"localhost/xe");
?>

If you’re using Oracle Database 11g, you should use the following in your Credentials.inc file:

1
2
3
4
5
6

<?php
  // Connection variables.
  define('SCHEMA',"student");
  define('PASSWD',"student");
  define('TNS_ID',"localhost/orcl");
?>

UploadItemDescriptionForm.htm

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

<html>
<! UploadItemDescriptionForm.htm                              >
<! Date:    05-Jul-2009                                       >
<! Author:  Michael McLaughlin                                >
<!                                                            >
<! This script demonstrates an HTML FORM used to upload files >
<! local files to the UploadItemDesc.php server-side program. >
<head>
<title>
  Chapter 8 : UploadItemDescriptionForm.htm
</title>
</head>
<body>
<form id="uploadForm"
      action="UploadItemDescription.php"
      enctype="multipart/form-data"
      method="post">
  <table border=0 cellpadding=0 cellspacing=0>
    <tr>
      <td width=125>Item ID</td>
      <td>
        <input id="id" name="id" type="text">
      </td>
    </tr>
    <tr>
      <td width=125>Item Title</td>
      <td>
        <input id="title" name="title" type="text">
      </td>
    </tr>
    <tr>
      <td width=125>Select File</td>
      <td>
        <input id="uploadfilename" name="userfile" type="file">
      </td>
    </tr>
    <tr>
      <td width=125>Click Button to</td>
      <td><input type="submit" value="Upload File"></td>
    </tr>
  </table>
</form>
</body>
</html>

UploadItemDescription.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170

<?php
  // Set database credentials.
  include_once("Credentials.inc");
 
  // Displayed moved file in web page.
  $item_desc = process_uploaded_file();
 
  // Return successful attempt to connect to the database.
  if ($c = @oci_connect(SCHEMA,PASSWD,TNS_ID))
  {
    // Declare input variables.
    (isset($_POST['id']))    ? $id = (int) $_POST['id'] : $id = 1021;
    (isset($_POST['title'])) ? $title = $_POST['title'] : $title = "Harry #1";
 
    // Declare a PL/SQL execution command.
    $stmt = "BEGIN
               web_load_clob_from_file(:id,:item_desc);
             END;";
 
    // Strip special characters to avoid ORA-06550 and PLS-00103 errors.
    $stmt = strip_special_characters($stmt);
 
    // Parse a query through the connection.
    $s = oci_parse($c,$stmt);
 
    // Define a descriptor for a CLOB.
    $rlob = oci_new_descriptor($c,OCI_D_LOB);
 
    // Define a variable name to map to CLOB descriptor.
    oci_define_by_name($s,':item_desc',$rlob,SQLT_CLOB);
 
    // Bind PHP variables to the OCI types.
    oci_bind_by_name($s,':id',$id);
    oci_bind_by_name($s,':item_desc',$rlob,-1,SQLT_CLOB);
 
    // Execute the PL/SQL statement.
    if (oci_execute($s,OCI_DEFAULT))
    {
 
      $rlob->save($item_desc);
      oci_commit($c);
      query_insert($id,$title);
    }
 
    // Release statement resources.
    oci_free_statement($s);
 
    // Disconnect from database.
    oci_close($c);
  }
  else
  {
    // Assign the OCI error and format double and single quotes.
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
    die();
  }
 
  // Query results afret an insert.
  function query_insert($id,$title)
  {
    // Return successful attempt to connect to the database.
    if ($c = @oci_new_connect(SCHEMA,PASSWD,TNS_ID))
    {
      // Declare a SQL SELECT statement returning a CLOB.
      $stmt = "SELECT   item_desc
               FROM     item
               WHERE    item_id = :id";
 
      // Parse a query through the connection.
      $s = oci_parse($c,$stmt);
 
      // Bind PHP variables to the OCI types.
      oci_bind_by_name($s,':id',$id);
 
      // Execute the PL/SQL statement.
      if (oci_execute($s))
      {
        // Return a LOB descriptor as the value.
        while (oci_fetch($s))
        {
          for ($i = 1;$i <= oci_num_fields($s);$i++)
            if (is_object(oci_result($s,$i)))
            {
              if ($size = oci_result($s,$i)->size())
                $data = oci_result($s,$i)->read($size);
              else
                $data = "&nbsp;";
            }
            else
            {
              if (oci_field_is_null($s,$i))
                $data = "&nbsp;";
              else
                $data = oci_result($s,$i);
            }
        } // End of the while(oci_fetch($s)) loop.
 
        // Format HTML table to display biography.
        $out = '<table border="1" cellpadding="3" cellspacing="0">';
        $out .= '<tr>';
        $out .= '<td align="center" class="e">'.$title.'</td>';
        $out .= '</tr>';
        $out .= '<tr>';
        $out .= '<td class="v">'.$data.'</td>';
        $out .= '</tr>';
        $out .= '</table>';
      }
 
      // Print the HTML table.
      print $out;
 
      // Release statement resources.
      oci_free_statement($s);
 
      // Disconnect from database.
      oci_close($c);
    }
    else
    {
      // Assign the OCI error and format double and single quotes.
      $errorMessage = oci_error();
      print htmlentities($errorMessage['message'])."<br />";
    }
  }
 
  // Manage file upload and return file as string.
  function process_uploaded_file()
  {
    // Declare a variable for file contents.
    $contents = "";
 
    // Define the upload file name for Windows or Linux.
    if (preg_match(".Win32.",$_SERVER["SERVER_SOFTWARE"]))
      $upload_file = getcwd()."\\temp\\".$_FILES['userfile']['name'];
    else
      $upload_file = getcwd()."/temp/".$_FILES['userfile']['name'];
 
    // Check for and move uploaded file.
    if (is_uploaded_file($_FILES['userfile']['tmp_name']))
      move_uploaded_file($_FILES['userfile']['tmp_name'],$upload_file);
 
    // 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.
      fclose($fp);
    }
 
    // Return file content as string.
    return $contents;
  }
 
  // Strip special characters, like carriage or line returns and tabs.
  function strip_special_characters($str)
  {
    $out = "";
    for ($i = 0;$i < strlen($str);$i++)
      if ((ord($str[$i]) != 9) && (ord($str[$i]) != 10) &&
          (ord($str[$i]) != 13))
        $out .= $str[$i];
 
    // Return pre-parsed SQL statement.
    return $out;
  }
?>

Load a BLOB column to the Oracle database ↓

This is a copy of the four files required to load a large image to an Oracle database with a BLOB data type. The fourth file reads the binary image and translates it into an HTML header and image that can be read through a call to the src attribute of an img tag. You can find the call to the forth file in the UploadItemBlob.php.

The code is in clear text because somebody asked for it. They’re nervous about zip files. Click the title above to expand all the code text.

Credentials.inc

As discussed with large strings, you should use the following in your Credentials.inc file. If you deploy these in same directory as where you tested the code for large text files, there’s no need to update the copy in the current directory.

1
2
3
4
5
6

<?php
  // Connection variables.
  define('SCHEMA',"student");
  define('PASSWD',"student");
  define('TNS_ID',"localhost/xe");
?>

If you’re using Oracle Database 11g, you should use the following in your Credentials.inc file:

1
2
3
4
5
6

<?php
  // Connection variables.
  define('SCHEMA',"student");
  define('PASSWD',"student");
  define('TNS_ID',"localhost/orcl");
?>

UploadItemBlobForm.htm

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

<html>
<! Program: UploadItemBlobForm.htm                            >
<! Date:    05-Jul-2009                                       >
<! Author:  Michael McLaughlin                                >
<!                                                            >
<! This script demonstrates an HTML FORM used to upload       >
<! local files to the UploadItemBlob.php server-side program, >
<! and store the file in a BLOB column.                       >
<head>
<title>
  UploadItemBlobForm.htm
</title>
</head>
<body>
<form id="uploadForm"
      action="UploadItemBlob.php"
      enctype="multipart/form-data"
      method="post">
  <table border=0 cellpadding=0 cellspacing=0>
    <tr>
      <td width=125>Item Number</td>
      <td>
        <input id="id" name="id" type="text">
      </td>
    </tr>
    <tr>
      <td width=125>Item Title</td>
      <td>
        <input id="title" name="title" type="text">
      </td>
    </tr>
    <tr>
      <td width=125>Select File</td>
      <td>
        <input id="uploadfilename" name="userfile" type="file">
      </td>
    </tr>
    <tr>
      <td width=125>Click Button to</td>
      <td><input type="submit" value="Upload File"></td>
    </tr>
  </table>
</form>
</body>
</html>

UploadItemBlob.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178

<?php
  // Set database credentials.
  include_once("Credentials.inc");
 
  // Displayed moved file in web page.
  $item_blob = process_uploaded_file();
 
  // Return successful attempt to connect to the database.
  if ($c = @oci_connect(SCHEMA,PASSWD,TNS_ID))
  {
    // Declare input variables.
    (isset($_POST['id']))    ? $id = (int) $_POST['id'] : $id = 1021;
    (isset($_POST['title'])) ? $title = $_POST['title'] : $title = "Harry #1";
 
    // Declare a PL/SQL execution command.
    $stmt = "BEGIN
               web_load_blob_from_file(:id,:item_blob);
             END;";
 
    // Strip special characters to avoid ORA-06550 and PLS-00103 errors.
    $stmt = strip_special_characters($stmt);
 
    // Parse a query through the connection.
    $s = oci_parse($c,$stmt);
 
    // Define a descriptor for a BLOB.
    $rlob = oci_new_descriptor($c,OCI_D_LOB);
 
    // Define a variable name to map to CLOB descriptor.
    oci_define_by_name($s,':item_blob',$rlob,SQLT_BLOB);
 
    // Bind PHP variables to the OCI types.
    oci_bind_by_name($s,':id',$id);
    oci_bind_by_name($s,':item_blob',$rlob,-1,SQLT_BLOB);
 
    // Execute the PL/SQL statement.
    if (oci_execute($s,OCI_DEFAULT))
    {
 
      $rlob->save($item_blob);
      oci_commit($c);
      oci_free_statement($s);
      query_insert($id,$title);
    }
 
    // Release statement resources.
    oci_free_statement($s);
 
    // Disconnect from database.
    oci_close($c);
  }
  else
  {
    // Assign the OCI error and format double and single quotes.
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
  }
 
  // Query results afret an insert.
  function query_insert($id,$title)
  {
    // Return successful attempt to connect to the database.
    if ($c = @oci_new_connect(SCHEMA,PASSWD,TNS_ID))
    {
      // Declare a SQL SELECT statement returning a CLOB.
      $stmt = "SELECT   item_desc
               FROM     item
               WHERE    item_id = :id";
 
      // Parse a query through the connection.
      $s = oci_parse($c,$stmt);
 
      // Bind PHP variables to the OCI types.
      oci_bind_by_name($s,':id',$id);
 
      // Execute the PL/SQL statement.
      if (oci_execute($s))
      {
        // Return a LOB descriptor as the value.
        while (oci_fetch($s))
        {
          for ($i = 1;$i <= oci_num_fields($s);$i++)
            if (is_object(oci_result($s,$i)))
            {
              if ($size = oci_result($s,$i)->size())
              {
                $data = oci_result($s,$i)->read($size);
              }
              else
                $data = "&nbsp;";
            }
            else
            {
              if (oci_field_is_null($s,$i))
                $data = "&nbsp;";
              else
                $data = oci_result($s,$i);
            }
 
        } // End of the while(oci_fetch($s)) loop.
 
        // Format HTML table to display BLOB photo and CLOB description.
        $out = '<table border="1" cellpadding="5" cellspacing="0">';
        $out .= '<tr>';
        $out .= '<td align="center" class="e">'.$title.'</td>';
        $out .= '</tr>';
        $out .= '<tr><td class="v">';
        $out .= '<div>';
        $out .= '<div style="margin-right:5px;float:left">';
        $out .= '<img src="ConvertBlobToImage.php?id='.$id.'">';
        $out .= '</div>';
        $out .= '<div style="position=relative;">'.$data.'</div>';
        $out .= '</div>';
        $out .= '</td></tr>';
        $out .= '</table>';
      }
 
      // Print the HTML table.
      print $out;
 
      // Release statement resources.
      oci_free_statement($s);
 
      // Disconnect from database.
      oci_close($c);
    }
    else
    {
      // Assign the OCI error and format double and single quotes.
      $errorMessage = oci_error();
      print htmlentities($errorMessage['message'])."<br />";
    }
  }
 
  // Manage file upload and return file as string.
  function process_uploaded_file()
  {
    // Declare a variable for file contents.
    $contents = "";
 
    // Define the upload file name for Windows or Linux.
    if (preg_match(".Win32.",$_SERVER["SERVER_SOFTWARE"]))
      $upload_file = getcwd()."\\temp\\".$_FILES['userfile']['name'];
    else
      $upload_file = getcwd()."/temp/".$_FILES['userfile']['name'];
 
    // Check for and move uploaded file.
    if (is_uploaded_file($_FILES['userfile']['tmp_name']))
      move_uploaded_file($_FILES['userfile']['tmp_name'],$upload_file);
 
    // 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.
      fclose($fp);
    }
 
    // Return file content as string.
    return $contents;
  }
 
  // Strip special characters, like carriage or line returns and tabs.
  function strip_special_characters($str)
  {
    $out = "";
    for ($i = 0;$i < strlen($str);$i++)
      if ((ord($str[$i]) != 9) && (ord($str[$i]) != 10) &&
          (ord($str[$i]) != 13))
        $out .= $str[$i];
 
    // Return pre-parsed SQL statement.
    return $out;
  }
?>

ConvertBlobToImage.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

<?php
  // Database credentials must be set manually because an include_once() function
  // call puts something ahead of the header, which causes a failure when rendering
  // an image.
 
  // Return successful attempt to connect to the database.
  if ($c = @oci_new_connect("student","student","localhost/orcl"))
  {
    // Declare input variables.
    (isset($_GET['id']))    ? $id = (int) $_GET['id'] : $id = 1023;
 
    // Declare a SQL SELECT statement returning a CLOB.
    $stmt = "SELECT   item_blob
             FROM     item
             WHERE    item_id = :id";
 
    // Parse a query through the connection.
    $s = oci_parse($c,$stmt);
 
    // Bind PHP variables to the OCI types.
    oci_bind_by_name($s,':id',$id);
 
    // Execute the PL/SQL statement.
    if (oci_execute($s))
    {
      // Return a LOB descriptor and free resource as the value.
      while (oci_fetch($s))
      {
        for ($i = 1;$i <= oci_num_fields($s);$i++)
          if (is_object(oci_result($s,$i)))
          {
            if ($size = oci_result($s,$i)->size())
            {
              $data = oci_result($s,$i)->read($size);
            }
            else
              $data = "&nbsp;";
          }
          else
          {
            if (oci_field_is_null($s,$i))
              $data = "&nbsp;";
            else
              $data = oci_result($s,$i);
          }
      } // End of the while(oci_fetch($s)) loop.
 
      // Free statement resources.
      oci_free_statement($s);
 
      // Print the header first.
      header('Content-type: image/x-png');
      imagepng(imagecreatefromstring($data));
    }
 
    // Disconnect from database.
    oci_close($c);
  }
  else
  {
    // Assign the OCI error and format double and single quotes.
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
  }
?>
  1. Create a temp directory for the upload target location, as qualified in the PHP code. The PHP code works regardless of whether you’re on Windows or Linux, but it does depend on the creation of this directory.

oraclelobsetup2

  1. Create a directory or folder for the large file source directories. This directory is probably on your test machine (laptop) but it mimics a client laptop and would work if your server was on a different machine.

oraclelobsetup3

  1. Inside the Upload directory, you should create the following two directories:

oraclelobsetup4

    You should download the CLOB Text File zip file and unzip it into the textfiles directory; then download the BLOB Image File zip file and unzip it into the imagefiles directory.
  1. The last directory or folder that you’ll need to create is for a couple stored procedures. These procedures let you pass a reference to the Oracle database server, which then lets you upload both CLOB and BLOB files.

oraclelobsetup5

Assuming you’ve downloaded the zip files and extracted them into the correct locations, this section is done.

Prepare the Oracle database ↓

This section provides you with instructions on how to ensure everything will work once the PHP programs call the database. Even if you have one of my sample Video Store databases, you should verify and add appropriate columns. This post assumes you’ve downloaded the code for the Oracle Database 11g PL/SQL Programming book, but it’ll work with the code form the Oracle Database 10g Express Edition PHP Web Programming book too.

  1. Navigate to the directory where you extracted the PL/SQL Stored Procedures, which according to blog page is: C:\Data\SQL. In that directory, connect as the student user, or whichever account you’re using. You should confirm that you have a item_desc column of CLOB data type, and an item_blob column of BLOB type in the item table. If you don’t have those columns, you can add them with the following statement:
ALTER TABLE item
ADD (item_desc CLOB)
ADD (item_blob BLOB);
  1. After ensuring that you have those two columns, create the two stored procedures, like
SQL> @C:\DATA\SQL\create_web_clob_loading.sql
SQL> @C:\DATA\SQL\create_web_blob_loading.sql

Test the Configuration ↓

This section shows you how to test all that you’ve done. It works provided you created the directories and extracted the zip file contents to their respective directories. The virtual URL actually maps to the C:\Program Files\Zend\Apache2\htdocs\lob directory.

  1. Enter the http://localhost/lob/UploadItemDescriptionForm.htm URL, and complete the form by choosing a valid item_id column value and text file from your C:\Data\Upload\TextFiles directory. Then, click the Upload File button (you can see a larger version of the image by clicking on it).

oraclelobupload1

  1. This page displays after you successfully upload the text file to the database.

oraclelobupload2

  1. Enter the http://localhost/lob/UploadItemBLOBForm.htm URL, and complete the form by choosing a valid item_id column value and image file from your C:\Data\Upload\ImageFiles directory. Then, click the Upload File button (you can see a larger version of the image by clicking on it).

oraclelobupload3

  1. This page displays after you successfully upload the image file to the database.

oraclelobupload4

Troubleshooting the Configuration ↓

This section shows you how to check why something isn’t working.

  1. The first thing to check are the credentials. They’re in the credentials.inc file. They’re posted with a student user, student password, and XE TNS alias.
  1. Not to be funny, but the second thing to check are credentials. Specifically, you need to check the credentials in the ConvertBlobToImage.php file. They’re individually entered in the connect string of this file because otherwise they put something in front of the header, which is disallowed to render the image.
  1. Check to see if the text or image file made it to the C:\Program Files\Zend\Apache2\htdocs\lob\temp directory. If they made it that far but no further, check to see if you have valid procedures in the student schema.
  1. If the stored procedures are invalid, verify that you have the required item_desc and item_blob columns in the item table.
  1. Check whether the CLOB or BLOB are loaded into the database. You use the GETLENGTH function from the DBMS_LOB package, like this:
SELECT   i.item_id
,        dbms_lob.getlength(i.item_desc)
,        dbms_lob.getlength(i.item_blob)
FROM     item i
WHERE    i.item_desc IS NOT NULL
OR       i.item_blob IS NOT NULL;
  1. Check if the item_id value is found in the list of values.
  1. If you’re stumped, add a comment and explain what’s up.

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?

    michael

    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;

    maclochlainn

    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.

    Michael

    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.

    Moe

    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'];
    else
      $upload_file = getcwd()."/temp/".$_FILES['userfile']['name'];
 
    // Check for and move uploaded file.
    if (is_uploaded_file($_FILES['userfile']['tmp_name']))
      move_uploaded_file($_FILES['userfile']['tmp_name'],$upload_file);
 
    // 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.
      fclose($fp);
    }
    // Return file content as string.
    return $contents;
  }

    maclochlainn

    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:

    phpfileread

    Moe

    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:

    phpfileurl

    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"
      action="UploadItemBlob.php"
      enctype="multipart/form-data"
      method="post">

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

    maclochlainn

    9 Jul 09 at 11:08 pm

  8. SWEEEEET!!!!

    It is working!!!

    Thanks!

    Moe

    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.

    maclochlainn

    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?

    Peter

    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
 
BEGIN
 
  -- 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
 
BEGIN
 
  -- 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;
/

    maclochlainn

    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

    Peter

    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

    maclochlainn

    24 Feb 11 at 12:32 pm

Leave a Reply