PL/SQL stored procedure with ref cursor in PHP

Question

I'm having some troubles with a function I developed in CodeIgniter 3. I'm trying to call a stored procedure in Oracle (a SELECT with some conditions and joins) with a ref cursor but when a I execute it, I get an empty array from the cursor.

I leave you below the code in PHP:

public function getReport($nummonth,$numyear) {
    $sqlqry = "BEGIN FRGSV006_SCP60_COMPACT(:cur,:nummonth,:numyear); END;";
        
    $smt = oci_parse($this->connbi, $sqlqry);
        
    oci_bind_by_name($smt, ":nummonth", $nummonth,32);
    oci_bind_by_name($smt, ":numyear", $numyear,32);
        
    $refcur = oci_new_cursor($this->connbi);
        
    oci_bind_by_name($smt, ":cur",$refcur,-1,OCI_B_CURSOR);
        
    oci_execute($smt);
    oci_execute($refcur);
        
    oci_fetch_all($refcur, $result,null,null,OCI_FETCHSTATEMENT_BY_ROW);
        
    oci_free_statement($smt);
    oci_free_statement($refcur);
        
    return $result;
}

I already test the procedure working on the database and it's ok. I've been searching all over Google for a solution and none of them worked.

Do you know what may be happening/why is not returning data?

Answer 1

Your code looks okay to me. I'm assuming connbi is already defined from an @oci_pconnect call that succeeded? You should also check for errors at each step:

$connbi = @oci_pconnect( . . . );

if (!$connbi) {
  $e = oci_error();
  trigger_error('Could not connect to database: ' . $e['message'],E_USER_ERROR);
  }

$smt = oci_parse($this->connbi, $sqlqry);

if (!$smt) {
  $e = oci_error($connbi);
  trigger_error('Could not parse statement: ' . $e['message'] . $sql,E_USER_ERROR);

}

$refcur = oci_new_cursor($this->connbi);
    
oci_bind_by_name($smt, ":cur",$refcur,-1,OCI_B_CURSOR);
    
$r = oci_execute($smt);

if (!$r) {
  $e = oci_error($smt);

  trigger_error(htmlentities('Could not execute: ' . $e['message']),E_USER_ERROR);
  }

$r = oci_execute($refcur);

if (!$r) {
  $e = oci_error($refcur);

  trigger_error(htmlentities('Could not execute: ' . $e['message']),E_USER_ERROR);
  }

Also capture the rows returned $nrows = oci_fetch_all($refcur, $result,null,null, OCI_FETCHSTATEMENT_BY_ROW); and emit debugging output to see if it returned anything. You may also want to set some PHP settings like:

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);