php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #31693 "ORA-01001: invalid cursor" after invoking oci-fetch-all function
Submitted: 2005-01-25 21:49 UTC Modified: 2005-11-16 22:58 UTC
Votes:9
Avg. Score:5.0 ± 0.0
Reproduced:6 of 6 (100.0%)
Same Version:6 (100.0%)
Same OS:4 (66.7%)
From: Tomasz dot Fryc at bph dot pl Assigned:
Status: Closed Package: OCI8 related
PHP Version: 5.0.3 OS: Windows 2003 Server
Private report: No CVE-ID: None
 [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.


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-02-11 21:19 UTC] 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-17 11:16 UTC] Tomasz dot Fryc at bph dot pl
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;
 [2005-02-28 20:57 UTC] sniper@php.net
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-03-08 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2005-04-04 19:51 UTC] terry dot greenlaw at logicalshift dot com
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.
 [2005-06-27 21:06 UTC] wez@php.net
Are you running ISAPI PHP under IIS?
 [2005-06-30 18:54 UTC] wez@php.net
If you could provide the requested feedback by tomorrow, I'll be able to do more to help you.
 [2005-07-08 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2005-11-16 14:48 UTC] sgattas at fibertel dot com dot ar
Sorry to interrupt. This error happens with IIS running ISAPI.
No matter what type of conection (ocilogin, oci_new_connect, etc) function you use you get the error while data is being retriving from a ref cursor in an oracle pck or prc. Invalid cursors everywhere.
As far as I tested (I can confirm this): you have two different sessions: session 1 executes the prc o pck, a few moments later session 2 does the same and then session 1 gets the error invalid cursor, losing all data untill the moment session 2 started.
Seems session 2 steals the conection while session 1 is still fetching.
Guess this error defeates any use of ref cursors when you have more than one user in your site (and we have lots of users).
Please help!!!!!!!!!
 [2005-11-16 15:27 UTC] tony2001@php.net
Please try new OCI8 from PECL.
Windows DLLs can be found here: http://pecl4win.php.net
 [2005-11-16 19:11 UTC] sgattas at fibertel dot com dot ar
Thanks a lot for the followup. I downloaded the extension. However, I never used this extensions and now I get an error: 
"PHP Warning:  ocibindbyname() expects parameter 1 to be resource, null given in ...", happens in every oci8 instruction I ran.
Can you suggest what can be wrong? Thanks a lot for your time!
 [2005-11-16 19:16 UTC] tony2001@php.net
Check why the connection resource is NULL.
I can't say anything without looking at your code.
 [2005-11-16 21:13 UTC] popo_x at hotmail dot com
i have installed php 5.0.4 and i dont find these version of php_oci8.dll. Which one i must install ?
 [2005-11-16 21:21 UTC] tony2001@php.net
http://pecl4win.php.net/ext.php/php_oci8.dll - choose the one that fits your PHP version.
 [2005-11-16 22:54 UTC] sgattas at fibertel dot com dot ar
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 !!
 [2005-11-16 22:58 UTC] tony2001@php.net
Fixed -> closed.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Dec 26 15:01:32 2024 UTC