Hi,
I'm using the example from the book "Oracle Database 11g PL/SQL Programming". In chapter 8 there is a example how to upload file into BLOB table using php script and pl/sql.
The problem is that there is a save error when I run the script.
This is the table structure:
item (
item_id number,
item_blob blob
)
This is the pl/sql which reads the file and inserts it into Oracle:
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;
/
This the php script:
<html>
<! UploadItemBlob.php >
<! Chapter 8, Oracle Database 11g PL/SQL Programming >
<! by Michael McLaughlin >
<! >
<! This script demonstrates uploading, moving an uploaded >
<! file, storing the content in an Oracle CLOB column using a >
<! stored PL/SQL procedure, and then accesses the stored >
<! content from the database and renders the page. >
<head>
<title>
Chapter 8 : UploadItemBlob.php
</title>
<style>
.e {background-color: #ccccff; font-weight: bold; color: #000000;}
.v {background-color: #cccccc; color: #000000;}
</style>
</head>
<body>
<?php
// Displayed moved file in web page.
$item_blob = process_uploaded_file();
// Return successful attempt to connect to the database.
if ($c = @oci_connect("admin","qwerty","192.168.1.124/oracle"))
{
// 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("3333","qqqqqq");
}
// 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("admin","qwerty","192.168.1.124/oracle"))
{
// Declare a SQL SELECT statement returning a CLOB.
$stmt = "SELECT *
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 = " ";
}
else
{
if (oci_field_is_null($s,$i))
$data = " ";
else
$data = oci_result($s,$i);
}
} // End of the while(oci_fetch($s)) loop.
// Free statement resources.
oci_free_statement($s);
}
// Disconnect from database.
oci_close($c);
}
}
// Manage file upload and return file as string.
function process_uploaded_file()
{
// Declare a variable for file contents.
$contents = "";
$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 backquote_apostrophe($str)
{
$out = "";
for ($i = 0;$i < strlen($str);$i++)
if (ord($str[$i]) != 39)
$out .= $str[$i];
else
$out .= "'".$str[$i];
// Return pre-parsed SQL statement.
return $out;
}
// 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;
}
?>
</body>
</html>
This is the error when I run the php script
Warning: OCI-Lob::save() [function.OCI-Lob-save]: OCI_INVALID_HANDLE in /var/www/html/UploadItemBlob.php on line 53
Any idea where is the problem?