#49553 [Asn->Bgs]: Executing procedure ref cursor via dblink-synonym causes ORA-24338
| From: | sixd@php.net | Date: | Mon, 05 Oct 2009 14:53:04 +0000 |
| Subject: | #49553 [Asn->Bgs]: Executing procedure ref cursor via dblink-synonym causes ORA-24338 | ||
| References: | 1 | Groups: | php.bugs |
| Request: | Send a blank email to php-bugs+get-143279@lists.php.net to get a copy of this message | ||
ID: 49553
Updated by: sixd@php.net
Reported By: bugs dot php dot net dot ws at jondh dot me dot uk
-Status: Assigned
+Status: Bogus
Bug Type: OCI8 related
Operating System: Windows XP Pro v2002 SP3
PHP Version: 5.2.10
Assigned To: sixd
New Comment:
Sorry, but your problem does not imply a bug in PHP itself. For a
list of more appropriate places to ask for help using PHP, please
visit http://www.php.net/support.php as this bug system
is not the
appropriate forum for asking support questions. Due to the volume
of reports we can not explain in detail here why your report is not
a bug. The support channels will be able to provide an explanation
for you.
Thank you for your interest in PHP.
Previous Comments:
------------------------------------------------------------------------
[2009-09-29 17:03:37] bugs dot php dot net dot ws at jondh dot me dot
uk
This bug can be closed. Some further research reveals that a ref cursor
cannot be returned over a dblink - it's an Oracle limitation.
http://www.google.co.uk/search?q=oracle+ref+cursor+across+dblink
------------------------------------------------------------------------
[2009-09-14 10:15:16] bugs dot php dot net dot ws at jondh dot me dot
uk
The PL/SQL I provided had some mistakes in it, and relies on external
tables. Here is a simplified version for the convenience of
investigators:
CREATE OR REPLACE PACKAGE OTHERSCHEMA.WEB_ORDER_ENTRY_PACKAGE AS
TYPE REF_CURSOR IS REF CURSOR;
PROCEDURE Test(
InWebUserId IN VARCHAR2,
OutResults OUT REF_CURSOR);
END WEB_ORDER_ENTRY_PACKAGE;
/
CREATE OR REPLACE PACKAGE BODY OTHERSCHEMA.WEB_ORDER_ENTRY_PACKAGE AS
PROCEDURE Test(InWebUserId IN VARCHAR2, OutResults OUT REF_CURSOR) IS
BEGIN
OPEN OutResults FOR
SELECT 'CLIENT' CLIENT_CODE, 'NAME' CLIENT_NAME
FROM DUAL;
END Test;
END WEB_ORDER_ENTRY_PACKAGE;
/
------------------------------------------------------------------------
[2009-09-14 10:03:07] bugs dot php dot net dot ws at jondh dot me dot
uk
Description:
------------
I have been advised by thiago@php to raise this as a bug, having
initially reported it on a note page as an N.B. instead. I have a
package in schema "OTHERSCHEMA" to which I create a synonym in
"MYSCHEMA" via a db link, thus:
CREATE PUBLIC SYNONYM WEB_ORDER_ENTRY_PACKAGE FOR
OTHERSCHEMA.WEB_ORDER_ENTRY_PACKAGE@"DBLINK.UKL";
In the package I have a stored procedure which has an output parameter
that returns a ref cursor. The proc definition is thus:
PROCEDURE Test(InWebUserId IN VARCHAR2, OutResults OUT REF_CURSOR);
I call this from MYSCHEMA thus:
BEGIN WEB_ORDER_ENTRY_PACKAGE.Test(:InWebUserId, :OutResults); END;
This fails as detailed below.
Reproduce code:
---------------
$dbh = oci_connect('myuser', 'password', 'mydb');
if ($dbh === false)
exit('Db connection failed');
$sql = "BEGIN WEB_ORDER_ENTRY_PACKAGE.Test(:InWebUserId, :OutResults);
END;";
$stmt = oci_parse($dbh, $sql);
$ok = oci_bind_by_name($stmt, 'InWebUserId', 'hello', 30, SQLT_CHR);
if (!$ok)
exit('Failed binding param');
$cursor = oci_new_cursor($dbh);
$ok = oci_bind_by_name($stmt, 'OutResults', $cursor, 1000, SQLT_RSET);
if (!$ok)
exit('Failed binding param');
$ok = oci_execute($stmt);
if (!$ok)
exit('Failed executing proc');
$ok = oci_execute($cursor);
if (!$ok)
exit('Failed executing cursor');
$array = array();
while ($row = oci_fetch_array($cursor))
{
$array[] = $row;
}
print_r($array);
Expected result:
----------------
A results array returned without warnings or errors. Interestingly, if
the schemas are on the same database, and the DB Link is omitted in the
synonym definition, thus:
CREATE PUBLIC SYNONYM WEB_ORDER_ENTRY_PACKAGE FOR
OTHERSCHEMA.WEB_ORDER_ENTRY_PACKAGE;
Then it will work without problems. This suggests that the problem is
in the db link. This bug is exhibited when a db link is used regardless
of whether the schemas are on the same database or different databases.
It does not matter if the synonym is public or private.
Oracle 10.2.0.4.0
PL/SQL:
CREATE OR REPLACE PACKAGE OTHERSCHEMA.WEB_ORDER_ENTRY_PACKAGE AS
TYPE REF_CURSOR IS REF CURSOR;
PROCEDURE CLIENT_FLD(
InWebUserId IN WEB_USER.WEB_USERID%TYPE,
OutResults OUT REF_CURSOR);
END WEB_ORDER_ENTRY_PACKAGE;
/
CREATE OR REPLACE PACKAGE BODY OTHERSCHEMA.WEB_ORDER_ENTRY_PACKAGE AS
PROCEDURE Test(InWebUserId IN VARCHAR2, OutResults OUT REF_CURSOR) IS
BEGIN
OPEN OutResults FOR
SELECT CLIENTCODE CLIENT_CODE, DESCRIPTION CLIENT_NAME
FROM CLIENT;
// Select anything from dual would be fine here
END Test;
END WEB_ORDER_ENTRY_PACKAGE;
/
Actual result:
--------------
After executing the procedure, and then the cursor, upon using
oci_fetch_array() I receive this warning:
"PHP Warning: oci_fetch_array(): ORA-24338: statement handle not
executed"
As detailed above, if the DB Link is omitted from the synonym
definition (which implies the two schemas are on the same database) then
the repro code executes fine.
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=49553&edit=1