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: MS Windows 2003 Serwer
PHP Version: 5.0.3
New Comment:
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
);
Previous Comments:
------------------------------------------------------------------------
[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
