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] [EMAIL PROTECTED] 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] [EMAIL PROTECTED] 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
