ID: 31995
User updated by: wojciech dot superson at bph dot pl
Reported By: wojciech dot superson at bph dot pl
-Status: Feedback
+Status: Open
Bug Type: OCI8 related
Operating System: Windows 2003 Server
PHP Version: 5.0.3
New Comment:
I made some tests yesterday and I am able to reproduce the problem on
wish. The problem accurs there are more than one call of the procedure
(any procedures which return a ref cursor) in the same time. I made as
follows
1. I have a page which is called every 1 second
2. The execution time of Oracle procedure is about 300-400 ms
3. If the is only one request everything is all right
4. If there is more than one call (for example 3, so they are called in
the same time) one of them return the following warning (and return only
one row)
PHP Warning: oci_fetch_all() [<a
href='function.oci-fetch-all'>function.oci-fetch-all</a>]:
OCIFetchStatement: ORA-01001: invalid cursor\n in d:\\program
files\\apache group\\Apache\\htdocs\\moa\\php\\oracle.php on line 188.
5. On the other hand there is nothing like this in Oracle alert log
file but there is many entries in apache error log file.
Awaiting your soon reply
Best regards,
Wojtek
Previous Comments:
------------------------------------------------------------------------
[2005-02-28 20:21:36] sniper@php.net
Please try using this CVS snapshot:
http://snaps.php.net/php5-latest.tar.gz
For Windows:
http://snaps.php.net/win32/php5-win32-latest.zip
------------------------------------------------------------------------
[2005-02-18 14:21:16] wojciech dot superson at bph dot pl
I just call this function passing the name of the storage procedure.
Here is the procedure:
PROCEDURE GetEventsByState
(
P_UGP_ID IN NUMBER,
P_HST_ID IN NUMBER,
P_HST_GRP_ID IN NUMBER,
P_DAYS IN NUMBER,
P_STATE IN NUMBER,
P_CURSOR OUT c_event,
P_ERR_CODE OUT NUMBER,
P_ERR_DESC OUT VARCHAR2
)
IS
BEGIN
IF P_HST_ID = 0 AND P_HST_GRP_ID = 0 THEN
OPEN P_CURSOR FOR
SELECT EVT_ID, EVT_ETD_ID, EVT_HST_ID, HST_NAME, SYS_ID, SYS_NAME,
EVT_TYPE, EVT_SMS,
EVT_CLASS, EVT_BEEPS, EVT_LEVEL, to_char(EVT_DATE,'YYYY.MM.DD
HH24:MI:SS') EVT_DATE, EVT_STATE, EVT_MESSAGE
FROM MOA_EVENTS, MOA_HOST, MOA_SYSTEM, MOA_EVENT_USERS
WHERE EVT_HST_ID = HST_ID
AND HST_SYS_ID = SYS_ID
AND EVT_ETD_ID = EUS_ETD_ID
AND EUS_UGP_ID = P_UGP_ID
AND EVT_STATE = P_STATE
AND EVT_DATE > sysdate - P_DAYS
AND HST_VISIBLE = 1
AND SYS_ACTIVE = 1
AND SYS_VISIBLE = 1
--Moze byc duzo zarejestrowanych zdarzen, dlatego nie mozna brac 100
ostatnich
--AND EVT_ID >= (select max( EVT_ID ) from MOA_EVENTS) - 100
ORDER BY EVT_ID DESC;
RETURN ;
END IF;
IF P_HST_ID = 0 AND P_HST_GRP_ID > 0 THEN
OPEN P_CURSOR FOR
SELECT EVT_ID, EVT_ETD_ID, EVT_HST_ID, HST_NAME, SYS_ID, SYS_NAME,
EVT_TYPE, EVT_SMS,
EVT_CLASS, EVT_BEEPS, EVT_LEVEL, to_char(EVT_DATE,'YYYY.MM.DD
HH24:MI:SS') EVT_DATE, EVT_STATE, EVT_MESSAGE
FROM MOA_EVENTS, MOA_HOST, MOA_SYSTEM, MOA_EVENT_USERS
WHERE EVT_HST_ID = HST_ID
AND HST_SYS_ID = SYS_ID
AND EVT_ETD_ID = EUS_ETD_ID
AND EUS_UGP_ID = P_UGP_ID
AND EVT_STATE = P_STATE
AND HST_GROUP = P_HST_GRP_ID
AND EVT_DATE > sysdate - P_DAYS
AND HST_VISIBLE = 1
AND SYS_ACTIVE = 1
AND SYS_VISIBLE = 1
--AND EVT_ID >= (select max( EVT_ID ) from MOA_EVENTS) - 100
ORDER BY EVT_ID DESC;
RETURN ;
END IF;
IF P_HST_ID > 0 AND P_HST_GRP_ID = 0 THEN
OPEN P_CURSOR FOR
SELECT EVT_ID, EVT_ETD_ID, EVT_HST_ID, HST_NAME, SYS_ID, SYS_NAME,
EVT_TYPE, EVT_SMS,
EVT_CLASS, EVT_BEEPS, EVT_LEVEL, to_char(EVT_DATE,'YYYY.MM.DD
HH24:MI:SS') EVT_DATE, EVT_STATE, EVT_MESSAGE
FROM MOA_EVENTS, MOA_HOST, MOA_SYSTEM, MOA_EVENT_USERS
WHERE EVT_HST_ID = HST_ID
AND HST_SYS_ID = SYS_ID
AND EVT_ETD_ID = EUS_ETD_ID
AND EUS_UGP_ID = P_UGP_ID
AND EVT_STATE = P_STATE
AND HST_ID = P_HST_ID
AND EVT_DATE > sysdate - P_DAYS
AND HST_VISIBLE = 1
AND SYS_ACTIVE = 1
AND SYS_VISIBLE = 1
--AND EVT_ID >= (select max( EVT_ID ) from MOA_EVENTS) - 100
ORDER BY EVT_ID DESC;
RETURN ;
END IF;
IF P_HST_ID > 0 AND P_HST_GRP_ID > 0 THEN
OPEN P_CURSOR FOR
SELECT EVT_ID, EVT_ETD_ID, EVT_HST_ID, HST_NAME, SYS_ID, SYS_NAME,
EVT_TYPE, EVT_SMS,
EVT_CLASS, EVT_BEEPS, EVT_LEVEL, to_char(EVT_DATE,'YYYY.MM.DD
HH24:MI:SS') EVT_DATE, EVT_STATE, EVT_MESSAGE
FROM MOA_EVENTS, MOA_HOST, MOA_SYSTEM, MOA_EVENT_USERS
WHERE EVT_HST_ID = HST_ID
AND HST_SYS_ID = SYS_ID
AND EVT_ETD_ID = EUS_ETD_ID
AND EUS_UGP_ID = P_UGP_ID
AND EVT_STATE = P_STATE
AND HST_GROUP = P_HST_GRP_ID
AND EVT_HST_ID = P_HST_ID
AND EVT_DATE > sysdate - P_DAYS
AND HST_VISIBLE = 1
AND SYS_ACTIVE = 1
AND SYS_VISIBLE = 1
--AND EVT_ID >= (select max( EVT_ID ) from MOA_EVENTS) - 100
ORDER BY EVT_ID DESC;
RETURN ;
END IF;
P_ERR_CODE := 0;
P_ERR_DESC := 'OK';
EXCEPTION
WHEN NO_DATA_FOUND THEN
P_ERR_CODE := 10000;
P_ERR_DESC := 'Nie znaleziono danych';
MOA_LOG_PKG.SaveFatal( 'MOA_EVENT_PKG','GetEventsByState', SQLCODE,
P_ERR_CODE, P_ERR_DESC );
WHEN OTHERS THEN
P_ERR_CODE := 10001;
P_ERR_DESC := 'Blad: ' || SQLCODE || ' ' || SQLERRM;
MOA_LOG_PKG.SaveFatal( 'MOA_EVENT_PKG','GetEventsByState', SQLCODE,
P_ERR_CODE, SQLERRM );
END;
where c_cursor is:
TYPE c_event IS REF CURSOR RETURN r_event;
TYPE r_event IS RECORD
(
EVT_ID MOA_EVENTS.EVT_ID%TYPE
,EVT_ETD_ID MOA_EVENTS.EVT_ETD_ID%TYPE
,EVT_HST_ID MOA_EVENTS.EVT_HST_ID%TYPE
,EVT_HST_NAME MOA_HOST.HST_NAME%TYPE
,EVT_SYS_ID MOA_SYSTEM.SYS_ID%TYPE
,EVT_HST_SYSTEM MOA_SYSTEM.SYS_NAME%TYPE
,EVT_TYPE MOA_EVENTS.EVT_TYPE%TYPE
,EVT_SMS MOA_EVENTS.EVT_SMS%TYPE
,EVT_CLASS MOA_EVENTS.EVT_CLASS%TYPE
,EVT_BEEPS MOA_EVENTS.EVT_BEEPS%TYPE
,EVT_LEVEL MOA_EVENTS.EVT_LEVEL%TYPE
,EVT_DATE MOA_EVENTS.EVT_DATE%TYPE
,EVT_STATE MOA_EVENTS.EVT_STATE%TYPE
,EVT_MESSAGE MOA_EVENTS.EVT_MESSAGE%TYPE
);
------------------------------------------------------------------------
[2005-02-18 13:56:36] tony2001@php.net
How could we reproduce it?
------------------------------------------------------------------------
[2005-02-18 12:13:32] wojciech dot superson at bph dot pl
I have one more problem, I think it is connected to the original one.
Very often, there is message in apache error.log file "PHP Warning:
oci_fetch_all() [<a
href='function.oci-fetch-all'>function.oci-fetch-all</a>]:
OCIFetchStatement: ORA-01001: invalid cursor\n in d:\\program
files\\apache group\\Apache\\htdocs\\moa\\php\\oracle.php on line 188.
The line 188 is within the OracleExecProcSelect function which I have
sent you before.
------------------------------------------------------------------------
[2005-02-16 10:49:55] wojciech dot superson at bph dot pl
Description:
------------
I use PHP 5.0.3 with Oracle 9.2.0.5.0 on HP-UX 11.11 and Apache 1.3.31.
Aplication works fine and calls the same queries (as Oracle stored
procedures) many times. The problem is that sometimes (more less once
every 30/40 times) query returns only one/two record(s) neverless there
are many records in database for this query. I am not able to reproduce
the problem on wish. I attach the source code of the function I use to
call the Oracle stored procedure for every query in the application.
The name of procedure is passed in $statement variable.
Reproduce code:
---------------
function OracleExecProcSelect( $conn,$statement,& $results, &
$errorcode=-1, & $errordesc="" )
{
$curs = oci_new_cursor( $conn );
$stmt = oci_parse( $conn,"begin ".$statement." end;");
if ( ! oci_bind_by_name( $stmt,"data",$curs,-1,OCI_B_CURSOR ) ) return
;
if ( ! oci_bind_by_name( $stmt,":error_code",$errorcode,32 ) ) return
;
if ( ! oci_bind_by_name( $stmt,":error_desc",$errordesc,255 ) ) return
;
oci_execute( $stmt,OCI_DEFAULT );
oci_execute( $curs,OCI_DEFAULT );
$nrows = oci_fetch_all( $curs,$results );
oci_free_statement($stmt);
oci_free_statement($curs);
return $nrows;
}
Expected result:
----------------
I should get the array ($results) with rows returned by the Oracle
stored procedure (its name is passed by $statement variable). It works
fine but sometimes it returns only one/two rows. Then I call this
procedure from sqlplus I get all requested records.
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=31995&edit=1