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); 

• Christopher Jones: Check "Using REF CURSORS for Result Sets" on page 197 of the free PDF The Underground PHP and Oracle Manual

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.