|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2005-02-16 10:49 UTC] 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.
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Wed May 08 00:01:31 2024 UTC |
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 );