In this quick example, I will provide code which will do the following:

 

  • Define database connection CONSTANTS
  • Connection to Oracle with a┬ápermanent connection
  • Create an INSERT statement with bind variables and empty blobs
  • Create new blob descriptor pointers
  • Bind the INSERT statement (non-blob) variables to PHP variables
  • Bind the INSERT statement blob variables to PHP
  • Execute the Oracle statement checking for any Oracle errors
  • Echo out any Oracle errors found during the execution
  • If no errors were detected, save the blob data
  • Commit the query
  • Free up the blob descriptors

 

Note: Using bind variables provides much better performance from Oracle as it can reuse an execution path.

Note: When inserting blobs, there are several additional steps required (in comparison to non-blob data). These include using descriptors, creating empty blobs in the SQL statement, mapping the blob field names to the descriptors, binding the SQL references to the descriptors using OCI_B_BLOB, and calling the ->save method before committing the query.

 

 

<?

// define some constants
define("DB_USER", "username");
define("DB_PASS", "password");
define("DB", "//host:1523/sid");

// connect to oracle
if(!$con = oci_pconnect(DB_USER, DB_PASS, DB)) {
  echo "Cannot connect to database.";
  exit(9);
}

// sql with variables
$sql = "INSERT INTO table_a 
        VALUES(:somevalue_a,:somevalue_c,EMPTY_BLOB(),EMPTY_BLOB()) 
        returning field_lob_a,field_lob_b into :LOB_A,:LOBA";
$parse_sql = oci_parse($con, $sql);

// create empty lob descriptor
// can use: OCI_D_FILE, OCI_D_LOB or OCI_D_ROWID
$lob_a = oci_new_descriptor($con, OCI_D_LOB);
$lob_b = oci_new_descriptor($con, OCI_D_LOB);

// bind the sql variable to php variable
oci_bind_by_name($parse_sql, ":somevalue_a", $somevariable_a);
oci_bind_by_name($parse_sql, ":somevalue_c", $somevariable_c);
// bind the LOB fields
oci_bind_by_name($parsed_sql, ':LOB_A', $lob_a, -1, OCI_B_BLOB);
oci_bind_by_name($parsed_sql, ':LOB_B', $lob_b, -1, OCI_B_BLOB);

if(!oci_execute($parse_sql, OCI_DEFAULT)) {
  $e = error_get_last();
  $f = oci_error();
  echo "Message: ".$e['message']."\n";
  echo "File: ".$e['file']."\n";
  echo "Line: ".$e['line']."\n";
  echo "Oracle Message: ".$f['message'];
  // exit if you consider this fatal
  exit(9);
} else {

  // save the blob data
  $lob_a->save($lob_date_a);
  $lob_b->save($lob_data_b);
  // commit the query
  oci_commit($con);
  // free up the blob descriptors
  $lob_a->free();
  $lob_b->free();

}
?>

 

 

In another post, I provide code for an Oracle SELECT statement using bind variables, and Oracle INSERT statement (no blobs) using bind variables..

 

 

 

Tags: , , , , ,