Oracle Insert with PHP using Bind Variables

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
• Bind the INSERT statement variables to PHP variables
• Execute the Oracle statement checking for any Oracle errors
• Echo out any Oracle errors found during the execution
• Commit the query if the execution ran successfully

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

<?

// 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)";
$parse_sql = oci_parse($con, $sql);

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

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 {
  // commit the query
  oci_commit($con);
}

?>

In another posts, I provide code for an Oracle SELECT statement using bind variables, and Oracle INSERT statement with BLOB data using bind variables..

Tags: Bind Variables, Constants, Database, Oracle