From:             bugs dot php dot net dot ws at jondh dot me dot uk
Operating system: Windows XP Pro v2002 SP3
PHP version:      5.2.10
PHP Bug Type:     OCI8 related
Bug description:  Executing procedure ref cursor via dblink-synonym causes 
ORA-24338

Description:
------------
I have been advised by thi...@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 bug report at http://bugs.php.net/?id=49553&edit=1
-- 
Try a snapshot (PHP 5.2):            
http://bugs.php.net/fix.php?id=49553&r=trysnapshot52
Try a snapshot (PHP 5.3):            
http://bugs.php.net/fix.php?id=49553&r=trysnapshot53
Try a snapshot (PHP 6.0):            
http://bugs.php.net/fix.php?id=49553&r=trysnapshot60
Fixed in SVN:                        
http://bugs.php.net/fix.php?id=49553&r=fixed
Fixed in SVN and need be documented: 
http://bugs.php.net/fix.php?id=49553&r=needdocs
Fixed in release:                    
http://bugs.php.net/fix.php?id=49553&r=alreadyfixed
Need backtrace:                      
http://bugs.php.net/fix.php?id=49553&r=needtrace
Need Reproduce Script:               
http://bugs.php.net/fix.php?id=49553&r=needscript
Try newer version:                   
http://bugs.php.net/fix.php?id=49553&r=oldversion
Not developer issue:                 
http://bugs.php.net/fix.php?id=49553&r=support
Expected behavior:                   
http://bugs.php.net/fix.php?id=49553&r=notwrong
Not enough info:                     
http://bugs.php.net/fix.php?id=49553&r=notenoughinfo
Submitted twice:                     
http://bugs.php.net/fix.php?id=49553&r=submittedtwice
register_globals:                    
http://bugs.php.net/fix.php?id=49553&r=globals
PHP 4 support discontinued:          http://bugs.php.net/fix.php?id=49553&r=php4
Daylight Savings:                    http://bugs.php.net/fix.php?id=49553&r=dst
IIS Stability:                       
http://bugs.php.net/fix.php?id=49553&r=isapi
Install GNU Sed:                     
http://bugs.php.net/fix.php?id=49553&r=gnused
Floating point limitations:          
http://bugs.php.net/fix.php?id=49553&r=float
No Zend Extensions:                  
http://bugs.php.net/fix.php?id=49553&r=nozend
MySQL Configuration Error:           
http://bugs.php.net/fix.php?id=49553&r=mysqlcfg

Reply via email to