note 93477 deleted from function.oci-new-cursor by thiago
| From: | thiago@php.net | Date: | Fri, 11 Sep 2009 17:30:28 +0000 |
| Subject: | note 93477 deleted from function.oci-new-cursor by thiago | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-160467@lists.php.net to get a copy of this message | ||
Note Submitter: php dot net dot ws at jondh dot fish dot me dot uk
----
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.)