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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: Tomasz dot Fryc at bph dot pl
New email:
PHP Version: OS:

 

 [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: Fri Dec 27 06:01:29 2024 UTC