[eluser]BanditXP[/eluser]
This is my first contribution to Code Igniter. Sorry if I posted it in the wrong place.
I work a lot with PHP and Oracle, and needed to made some changes to oci8_driver to improve the development and integration with plsql code and php.
Changes in oci8_driver.php:
- Stored procedures can now be used without a package.
- New function to call stored functions and return the values.
Function stored_procedure:
Code:
/**
* Stored Procedure. Executes a stored procedure
*
* @access public
* @param package package stored procedure is in
* @param procedure stored procedure to execute
* @param params array of parameters
* @return array
*
* params array keys
*
* KEY OPTIONAL NOTES
* name no the name of the parameter should be in :<param_name> format
* value no the value of the parameter. If this is an OUT or IN OUT parameter,
* this should be a reference to a variable
* type yes the type of the parameter
* length yes the max size of the parameter
*/
function stored_procedure($package, $procedure, $params)
{
$package = is_null($package) || trim($package) == '' ? '' : trim($package) . '.';
$procedure = $package.$procedure;
if ($procedure == '' OR ! is_array($params))
{
if ($this->db_debug)
{
log_message('error', 'Invalid query: '.$package.'.'.$procedure);
return $this->display_error('db_invalid_query');
}
return FALSE;
}
// build the query string
$sql = "begin $procedure(";
$have_cursor = FALSE;
foreach($params as $param)
{
$sql .= $param['name'] . ",";
if (array_key_exists('type', $param) && ($param['type'] == OCI_B_CURSOR))
{
$have_cursor = TRUE;
}
}
$sql = trim($sql, ",") . "); end;";
$this->stmt_id = FALSE;
$this->_set_stmt_id($sql);
$this->_bind_params($params);
$this->query($sql, FALSE, $have_cursor);
}
Function stored_function:
Code:
/**
* Stored Function. Executes a stored function
*
* @access public
* @param package package stored function is in
* @param function stored function to execute
* @param params array of parameters
* @param return array of parameters (variable containing the return of the stored function)
* @return array
*
* params array keys
*
* KEY OPTIONAL NOTES
* name no the name of the parameter should be in :<param_name> format
* value no the value of the parameter. If this is an OUT or IN OUT parameter,
* this should be a reference to a variable
* type yes the type of the parameter
* length yes the max size of the parameter
*/
function stored_function($package, $function, $params, $return)
{
if ($function == '' OR ! is_array($params))
{
if ($this->db_debug)
{
log_message('error', 'Invalid query: '.$package.'.'.$function);
return $this->display_error('db_invalid_query');
}
return FALSE;
}
if (!is_null($package)) {
$function = $package.'.'.$function;
}
// build the query string
//var_dump($return);
$sql = "begin ".$return[0]['name']." := $function(";
$have_cursor = FALSE;
foreach($params as $param)
{
$sql .= $param['name'] . ",";
if (array_key_exists('type', $param) && ($param['type'] == OCI_B_CURSOR))
{
$have_cursor = TRUE;
}
}
$sql = trim($sql, ",") . "); end;";
$this->stmt_id = FALSE;
$this->_set_stmt_id($sql);
$this->_bind_params($params);
$this->_bind_params($return);
$this->query($sql, FALSE, $have_cursor);
return $return[0]['value'];
}
Thanks!
