oci_new_connect
I encountered a very strange problem today - returning REF CURSORS across certain kinds of synonym would return an error of "PHP Warning: oci_fetch_array(): ORA-24338: statement handle not executed" when using oci_fetch_* or (the deprecated) OCIFetchinto.
Here is my demo code, plus notes on how specifying a DbLink causes the warning:
<?php
/*
The synonym must be created thusly:
CREATE [PUBLIC] SYNONYM MY_PACKAGE FOR OTHERSCHEMA.MY_PACKAGE;
However, this causes problems:
CREATE [PUBLIC] SYNONYM MY_PACKAGE FOR OTHERSCHEMA.MY_PACKAGE@"DBLINK.UKL"
The problem specifically is this warning:
PHP Warning: oci_fetch_array(): ORA-24338: statement handle not executed in (php-file)
on line 51
*/
$dbh = oci_connect('user_test', 'password', 'ORADEV.UKL');
if ($dbh === false)
{
echo "Db connection failed<br/>";
exit();
}
$sql = "BEGIN MY_PACKAGE.Test(:InWebUserId, :OutResults); END;";
$stmt = oci_parse($dbh, $sql);
$val = 'hello';
$ok = oci_bind_by_name($stmt, 'InWebUserId', $val, 30, SQLT_CHR);
if (!$ok)
{
echo "Failed binding param\n";
}
$cursor = oci_new_cursor($dbh);
$ok = oci_bind_by_name($stmt, 'OutResults', $cursor, 1000, SQLT_RSET);
if (!$ok)
{
echo "Failed binding param\n";
}
$ok = oci_execute($stmt);
if (!$ok)
{
echo "Failed executing proc\n";
}
$ok = oci_execute($cursor);
if (!$ok)
{
echo "Failed executing cursor\n";
}
$array = array();
while ($row = oci_fetch_array($cursor))
{
$array[] = $row;
}
print_r($array);
?>
I don't know if this is a grants problem or a bug, but it took me quite some time to figure it out! Placing it here in case it helps someone else. This was carried out using PHP 5.2.10 and both databases on Oracle 10.2.0.4.0.
(Anti-spam: remove the swimming thing. Folks are welcome to drop me a line if they wish to look into this problem in more detail.)
oci_new_cursor
(PHP 5, PECL OCI8 >= 1.1.0)
oci_new_cursor — Allocates and returns a new cursor (statement handle)
Description
resource oci_new_cursor
( resource $connection
)
Allocates a new statement handle on the specified connection.
Return Values
Returns a new statement handle, or FALSE on error.
Examples
Example #1 Using REF CURSOR in an Oracle's stored procedure
<?php
// suppose your stored procedure info.output returns a ref cursor in :data
$conn = oci_connect("scott", "tiger");
$curs = oci_new_cursor($conn);
$stmt = oci_parse($conn, "begin info.output(:data); end;");
oci_bind_by_name($stmt, "data", $curs, -1, OCI_B_CURSOR);
oci_execute($stmt);
oci_execute($curs);
while ($data = oci_fetch_row($curs)) {
var_dump($data);
}
oci_free_statement($stmt);
oci_free_statement($curs);
oci_close($conn);
?>
Example #2 Using REF CURSOR in an Oracle's select statement
<?php
echo "<html><body>";
$conn = oci_connect("scott", "tiger");
$count_cursor = "CURSOR(select count(empno) num_emps from emp " .
"where emp.deptno = dept.deptno) as EMPCNT from dept";
$stmt = oci_parse($conn, "select deptno,dname,$count_cursor");
oci_execute($stmt);
echo "<table border=\"1\">";
echo "<tr>";
echo "<th>DEPT NAME</th>";
echo "<th>DEPT #</th>";
echo "<th># EMPLOYEES</th>";
echo "</tr>";
while ($data = oci_fetch_assoc($stmt)) {
echo "<tr>";
$dname = $data["DNAME"];
$deptno = $data["DEPTNO"];
echo "<td>$dname</td>";
echo "<td>$deptno</td>";
oci_execute($data["EMPCNT"]);
while ($subdata = oci_fetch_assoc($data["EMPCNT"])) {
$num_emps = $subdata["NUM_EMPS"];
echo "<td>$num_emps</td>";
}
echo "</tr>";
}
echo "</table>";
echo "</body></html>";
oci_free_statement($stmt);
oci_close($conn);
?>
Notes
Note: In PHP versions before 5.0.0 you must use ocinewcursor() instead. This name still can be used, it was left as alias of oci_new_cursor() for downwards compatability. This, however, is deprecated and not recommended.
add a note
User Contributed Notesoci_new_cursor
php dot net dot ws at jondh dot fish dot me dot uk
11-Sep-2009 03:55
11-Sep-2009 03:55
sixd at php dot net
05-Nov-2008 08:13
05-Nov-2008 08:13
Because OCI8 uses "prefetching" to greatly improve returning query results, but Oracle doesn't support prefetching for REF CURSORs, application performance using REF CURSORs can be greatly improved by writing a PL/SQL function that pulls data from the REF CURSOR and PIPEs the output. The new function can be queried in a SELECT as if it were a table. See http://blogs.oracle.com/opal/2008/11/
converting_ref_cursor_to_pipe.html