I was trying to get some stored procedures working as per a new project manager and started having trouble. Not knowing if it was a problem with my stored procedure or with CI I had a very frustrating time.
Here is my stored procedure:
CREATE OR REPLACE
PROCEDURE FOO
(
P_CURSOR IN OUT SYS_REFCURSOR
)AS
BEGIN
OPEN P_CURSOR FOR
SELECT * FROM BAR;
END FOO;
This is what I was using and getting a “ORA-24374: define not done before fetch or execute and fetch” error.
$this->db->trans_start();
$cursor = $this->db->get_cursor();
$param= array(
array('name'=>':P_CURSOR' , 'value'=> &$cursor, 'type'=>OCI_B_CURSOR, 'length'=> -1)
);
$this->db->stored_procedure('db','foo', $param);
$this->db->trans_complete();
I decided to make a change to the stored_procedure function to make it work. It may no be very pretty and you cannot do more than one cursor but it’s a work around for me and it might help someone else
public function stored_procedure($package, $procedure, &$params)
{
if ($package == '' OR $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 $package.$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;
$cursor = $param['value'];
}
}
$sql = trim($sql, ",") . "); end;";
$this->stmt_id = FALSE;
$this->_set_stmt_id($sql);
$this->_bind_params($params);
if(!$have_cursor){
$this->query($sql, FALSE, $have_cursor);
}else{
oci_execute($this->stmt_id);
oci_execute($cursor);
oci_fetch_all($cursor, $params,0,-1,OCI_FETCHSTATEMENT_BY_ROW);
}
}
Maybe is there another way to do this or am I just not setting up my Stored procedures right?