note 93477 added to function.oci-new-cursor
| From: | php dot net dot ws at jondh dot fish dot me dot uk | Date: | Fri, 11 Sep 2009 15:55:50 +0000 |
| Subject: | note 93477 added to function.oci-new-cursor | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-160466@lists.php.net to get a copy of this message | ||
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.)
----
Server IP: 92.48.74.199
Probable Submitter: 85.115.58.180 (proxied: 80.86.38.100)
----
Manual Page -- http://www.php.net/manual/en/function.oci-new-cursor.php
Edit -- https://master.php.net/note/edit/93477
Del: integrated -- https://master.php.net/note/delete/93477/integrated
Del: useless -- https://master.php.net/note/delete/93477/useless
Del: bad code -- https://master.php.net/note/delete/93477/bad+code
Del: spam -- https://master.php.net/note/delete/93477/spam
Del: non-english -- https://master.php.net/note/delete/93477/non-english
Del: in docs -- https://master.php.net/note/delete/93477/in+docs
Del: other reasons-- https://master.php.net/note/delete/93477
Reject -- https://master.php.net/note/reject/93477
Search -- https://master.php.net/manage/user-notes.php