Writing BLOB in PHP to Oracle via stored procedure

Question

Here is a table in Oracle:

CREATE TABLE upload_blob ( id NUMBER, doc BLOB );
CREATE SEQUENCE doc_id;

Now a stored procedure:

CREATE OR REPLACE PROCEDURE prUpload ( p_id OUT NUMBER, p_doc IN BLOB ) AS
BEGIN
   INSERT INTO upload_blob ( id, doc ) VALUES ( doc_id.NEXTVAL, p_doc )
   RETURNING id INTO p_id;
END;

Now I want to insert in the table from PHP. The examples I've seen do this via an INSERT into the table using EMPTY_BLOB, and then populate the OCI_D_LOB type variable with the content.

However, in my case direct access to the tables is not liked, hence why create the store procedure.

My PHP code is as follows:

       $sql = <<<EOF
begin
   pr_upLoad
   ( 
      p_doc           => :p_doc_blob,
      p_id            => :p_doc_id
   );
end;
EOF;

   $stmt = oci_parse ( $db_conn, $sql );

   $docId = "";

   $blob = oci_new_descriptor ( $db_conn, OCI_D_LOB);
   $file = file_get_contents($_FILES['docName']['tmp_name']);
   $blob->write ( $file );

   oci_bind_by_name ( $stmt, ':p_doc_id', $docId, 20); 
   oci_bind_by_name ( $stmt, ':p_doc_blob', $blob, -1, OCI_B_BLOB);

   if ( oci_execute ( $stmt, OCI_DEFAULT ) && empty ($errMsg) )
   {
      oci_commit($db_conn);
      $msg = "Document " . $docId . " upload successful.";
   }
   else
   {
      $msg = "Document Save  failed.";
   }

   $blob->free();
   oci_free_statement($stmt);

This gibes me the following error:

Warning: OCI-Lob::write(): OCI_INVALID_HANDLE in saveDoc.php on line 54

Line 54 being the following.

$blob->write ( $file );

I have tried changing the stored procedure to create an EMPTY_BLOB in the INSERT and return the EMPTY_BLOB, and then do the $blob->write after the call, followed by a commit, but this too gve a similar error.

What am I doing wrong?

PHP does not like the BLOB datatype and Oracle does not like empty BLOBs. What type of filedata are you uploading to the server and why? Could you use a CLOB instead? — XaxD
– XaxD, Commented Aug 11, 2013 at 18:21
Also post your errors, and have you tried the SQL scripts in the devbox? — XaxD
– XaxD, Commented Aug 11, 2013 at 18:22
Reply to XAD - no, cannot use CLOB as this is Word/PDF docs. — TenG
– TenG, Commented Aug 11, 2013 at 20:47
i guess the question is, why are you tstoring Word/PDF docs in the database in teh first place? why not just store copies of them on the server, and then store a path to them in the database? — XaxD
– XaxD, Commented Aug 11, 2013 at 22:15

TenG · Accepted Answer · 2013-08-11 22:22:57Z

1

It seems BLOBS as parameters to procedures is not liked by PHP.

Creating a temp staging table with a BLOB column, INSERT into this with a EMPTY_BLOB, map that blob to the PHP variable and then write works.

This is poor from a point of view of not wanting to expose tables directly to the account that the PHP connects as. Hence why I use stored procedures.

answered Aug 11, 2013 at 22:22

TenG

4,0343 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Writing BLOB in PHP to Oracle via stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related