Pear "executeStoredProc" function with Oracle 10g

My last post I showed how I was able to eventually connect to an Oracle database using Pear MDB2. This post I am going to show you how I am able to call an Oracle stored procedure using Pear MDB2.

Initially I attempted using the Pear function executeStoredProc() but after looking at the documentation in the Pear oci8 driver, it became clear that this function does not do what it is intended to do(at least by the way I interpret it). The executeStoredProc function should be able take in the database procedure name and an array of parameters, and use that information to call the stored procedure. The executeStoredProc function also has other options that can be sent in as parameters, more info on executeStoredProc can be found in the MDB2_Driver_Function_oci8 documentation.

Instead of using this function I wrote a similar function that I included in my connect script. This way I am able to call this function anytime I am connected to the Oracle database. The function below is specific for my purposes only, but it should be easily modifiable to fit in other situations. It is modeled after the executeStoredProc function.

function &executeDatabaseProc($name, $params, $db){
$query = "BEGIN ".$name;
$query .= $params ?
"('".implode("', '", $params)."')" : "()";
$query .= ";end;";

return $db->query($query, null, true, false);
}

This function takes the database procedure name, an array of parameters, and the current database connection. It creates a string called $query that is formatted similar to:

"BEGIN database_proc_name('param1', 'param2');end;"

After creating $query, the Pear MDB2 function query is called. Like I stated previously I did not need the last 3 parameters so I just hard coded them into the call (they will never need to change).

Below I briefly will show you how to make a call to this function in php code:

$mdb2 = connect();
if ($mdb2->supports('transactions')) {
$mdb2->beginTransaction();
}

//array must match the number of parameters
//in the procedure you are calling
$data = array("param1","param2");

$result =& executeDatabaseProc(
"database_proc_name",$data,$mdb2);

if (PEAR::isError($result)) {
if ($mdb2->in_transaction) {
$mdb2->rollback();
}
} else {
if ($mdb2->in_transaction) {
$mdb2->commit();
}
}
$mdb2->disconnect();

That is just one example of how this function can be used. If you have any questions or are having any trouble let me know in the comments and I will see if I can help you out. Also, if anyone else knows a better alternative I am always open for new suggestions.

Hope this helps.

Shane