Oracle and PHP with Cursors

From: Björn Wächter <bwc_at_p3-solutionsKILL_SPAM.de>
Date: Thu, 15 Nov 2007 09:03:26 +0100
Message-ID: <5q2cqeFtn5pdU1@mid.dfncis.de>

Hi all,

we are using Oracle and PHP for a Web application. We are using ref cursors to return data from oracle to php.

PROCEDURE GetData(

    p_form             IN VARCHAR2,
    crs_data           OUT Usertype.ref_cursor

)
AS
...
BEGIN
    ....

    OPEN crs_data FOR v_sql;

END; CREATE OR REPLACE PACKAGE usertype AS

   TYPE ref_cursor IS REF CURSOR;

END usertype;

We are facing the problem that retrieving the data over the cursor is quite slow because we see in the trace that every row is request with one call to oracle. We enabled prefetching in PHP but as fare as we know this is not possible for cursors. We need the flexibility of cursors to be able to return different numbers of columns in each request.
Any ideas how we can solve this problem? We read something about scrollable cursors. Is this a way?

Thanks Björn Received on Thu Nov 15 2007 - 02:03:26 CST