Re: note 93477 deleted from function.oci-new-cursor by thiago

This note should not have been deleted. On Fri, Sep 11, 2009 at 13:30, <thiago@php.net> wrote: > 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.) > > -- > PHP Notes Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- </Daniel P. Brown> daniel.brown@parasane.net || danbrown@php.net http://www.parasane.net/ || http://www.pilotpig.net/ Check out our great hosting and dedicated server deals at http://twitter.com/pilotpig