ID: 31693
User updated by: Tomasz dot Fryc at bph dot pl
Reported By: Tomasz dot Fryc at bph dot pl
-Status: Feedback
+Status: Open
Bug Type: OCI8 related
Operating System: Windows 2003 Server
PHP Version: 5.0.3
New Comment:
Here is procedure, which described PHP code invokes (in this case $sql
variable looks as follows: "begin
MOA.MOA_EVENT_PKG.GetEventList('param1','param2',...); end;"):
PROCEDURE GetEventsList
(
P_UGP_ID IN NUMBER,
P_HST_ID IN NUMBER,
P_HST_GRP_ID IN NUMBER,
P_DAYS 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
(
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, 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 HST_VISIBLE = 1
AND SYS_ACTIVE = 1
AND SYS_VISIBLE = 1
-- AND EVT_DATE > sysdate - P_DAYS
ORDER BY EVT_ID DESC
)
WHERE ROWNUM <= 100;
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
(
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, 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 HST_GROUP = P_HST_GRP_ID
AND EVT_ETD_ID = EUS_ETD_ID
AND EUS_UGP_ID = P_UGP_ID
AND HST_VISIBLE = 1
AND EVT_DATE > SYSDATE - P_DAYS
AND SYS_ACTIVE = 1
AND SYS_VISIBLE = 1
ORDER BY EVT_ID DESC
)
WHERE ROWNUM <= 100;
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 HST_ID = P_HST_ID
AND EVT_ETD_ID = EUS_ETD_ID
AND EUS_UGP_ID = P_UGP_ID
AND EVT_DATE > SYSDATE - P_DAYS
AND HST_VISIBLE = 1
AND SYS_ACTIVE = 1
AND SYS_VISIBLE = 1
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 HST_GROUP = P_HST_GRP_ID
AND EVT_HST_ID = P_HST_ID
AND EVT_ETD_ID = EUS_ETD_ID
AND EUS_UGP_ID = P_UGP_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','GetEventList', 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','GetEventList', SQLCODE,
P_ERR_CODE, SQLERRM );
END;
Previous Comments:
------------------------------------------------------------------------
[2005-02-11 21:19:36] tony2001@php.net
Any info on how to reproduce it?
Your reproduce code is fairly senseless, as I don't have your packages
with your stored procedures.
------------------------------------------------------------------------
[2005-02-03 22:27:54] fixit at bugs dot com
This seems to happens irregularly for me.
------------------------------------------------------------------------
[2005-01-25 21:49:19] Tomasz dot Fryc at bph dot pl
Description:
------------
Database (Oracle 9.2.0.5.0 on HP-UX 11.11) connection is opened with
oci_new_connect function. A statment is prepared with oci_parse and a
cursor is created with oci_new_cursor. After binding the statement to
the cursor (oci_bind_by_name), the statement and the cursor are
executed (oci_execute). The problem occures while invoking
oci_fetch_all function, which sometimes doesn't give expected number of
rows (not all of the rows are fetched) and causes "ORA-01001: invalid
cursor" warning.
Reproduce code:
---------------
$sql="begin EXM.MY_PKG.getrows('param1','param2'); end;"
$conn=oci_new_connect("somebody","something")
$curs=oci_new_cursor($conn)
$stmt=oci_parse($conn,$sql)
oci_bind_by_name($stmt,"data",$curs,-1,OCI_B_CURSOR)
oci_execute($stmt,OCI_DEFAULT)
oci_execute($curs,OCI_DEFAULT)
$nrows=oci_fetch_all($curs,$results)
oci_free_statement($curs)
oci_free_statement($curs)
Expected result:
----------------
Every function is correctly executed except oci_fetch_all, which
sometimes doesn't give all of the rows.
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=31693&edit=1