Get data from Oracle Ref Cursor using PHP OCI8

Question

I'm trying to get data from oracle pl/sql function, that return REF cursor. I wrote test function, just to check it. I'm using fresh Oracle 19g, and WAMP in windows, with Oracle Driver.

Function code:    FUNCTION getTestData
    
      RETURN types_package.cursor_type
      
      
 AS
      list_cursor   types_package.cursor_type;
      tmpcnt        INTEGER;
   BEGIN
   
      OPEN list_cursor FOR
           SELECT field1  from test;

      RETURN list_cursor;
      
    end;     

I tried many other ways, but every time i have some error like Invalid Fetch, or ora-01008. My question is, is it even possible? or there is some known issue using OCI8 and ref cursor in oracle? getting scalar variables working good for me, but i need ref cursors. Getting data from SELECT query also working fine.

<?php

// Replace these variables with your actual Oracle database credentials
$database = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.202.238)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PDB1)))";
$username = "***";
$password = "*****";

// Attempt to establish a connection
$conn = oci_connect($username, $password, $database);

if ($conn) {
    echo "Connected to Oracle database successfully!";
    oci_close($conn); // Close the connection
} else {
    $error = oci_error(); // Get the error if connection fails
    echo "Failed to connect to Oracle database: " . $error['message'];
}


      // Prepare the SQL statement with the stored function call
    $sql = "BEGIN :result := test_pkg.getTestData(); END;";
      $stmt = oci_parse($conn, $sql);

    // Bind the result parameter as a REF CURSOR
    $result = oci_new_cursor($conn);
    oci_bind_by_name($stmt, ':result', $result, -1, OCI_B_CURSOR);

    // Execute the statement
    oci_execute($stmt);

    // Define the fetch mode for the columns
    oci_define_by_name($result, 'COLUMN_NAME', $columnValue);

    // Fetch the result from the REF CURSOR
    while (oci_fetch($result)) {
        // Access the returned data
        // Example: $value = $columnValue;
    }

    // Close the statement and connection
    oci_free_statement($stmt);
    oci_close($conn); 

Answer 1

You are executing $stmt which returns the ref cursor into $result, but then you must execute $result to actually execute the ref cursor itself:

oci_execute($stmt); // gets the ref cursor
oci_execute($result); // executes the ref cursor

You should also free (oci_free_statement) them both as well. You might also want to avoid confusion by renaming $result something like $refcur.