oci8 Stored procedure cursor problems

[eluser]Unknown[/eluser]
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:

Code:

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.

Code:

$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

Code:

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?