|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2005-01-25 21:49 UTC] 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.
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2025 The PHP GroupAll rights reserved. |
Last updated: Sun Oct 26 21:00:01 2025 UTC |
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;I am seeing the same error while using oci_fetch_array(). I am using oci_new_connect('u','pw','db') in my main script. The script uses oci_fetch_array() to loop through items. Each item has an image tag which calls another script to fetch a jpg from a blob. <img src="view_image.php?id=n">. The view_image.php script also does an oci_new_connect() using the same credentials, fetches the jpeg from the blob using oci_fetch_array, outputs the jpeg data stream, and exits. I am getting sporatic ora-1001 errors. However, if I place a return() in the view_image.php code before the oci_new_connect(), the main script does not generate any ora-1001 errors. I've tried numerous combinations of freeing oracle resources in both scripts, as well as switching the view_image script to use oci_connect(), but the problem persists.The php page is similar as <? session_start(); $conn=OCILogon("test", "test", "db"); $mout=OCINewCursor($conn); $s=OCIParse($conn, "begin usr.usr_login(:b1,:b2,:b3,:b4,:b5,:b6,:b7); end;"); OCIBindByName($s, ":b1", &$usr1 ,-1); OCIBindByName($s, ":b2", &$pas ,-1); OCIBindByName($s, ":b3", &$lang ,-1); OCIBindByName($s, ":b4", $sess ,38); OCIBindByName($s, ":b5", $mout ,-1, OCI_B_CURSOR); OCIBindByName($s, ":b6", $out ,600); OCIBindByName($s, ":b7", $nus ,200); OCIExecute($s, OCI_DEFAULT); OCIExecute($mout); ?> And the oracle stored procedure: procedure usr_login (p_user in usr_payroll.pay_usr%type, p_pass in usr_payroll.pay_pass%type, p_lang in usr_language.lan_id%type, p_sess out usr_session.ses_id%type, p_tree out typ_access_cur, p_msge out varchar2, p_name out varchar2) end usr_login; The same page and stored procedure working good before replace de php_oci8.dll Thank a lot again !!