tracker issue : CF-3038574

Duplicate ID:	CF-3038707

Problem:

This just started happening in Beta 2.    All my Oracle Stored Procedure Calls that use result sets are either not properly closing the ref cursor call or is timing out the session before the results are returned, not sure.

We will have to revert back to Beta 1 soon, we did not see these types of issues with the Oracle Data Direct Driver at all.


We currently run CF 7 and CF 8 and have not seen this type of behavior before running against Oracle 10g Database.

Method:

Create a CF page that has an call to an Oracle Stored Procedure:

	<cfstoredproc procedure="HPCS_REPORT.selectapprovedexpenses" datasource="hpcs">
						<cfprocresult name="GetExpenses">
	</cfstoredproc>
	
	<cfdump var="#GetExpenses#">
	

This calls an Oracle Stored Procedure inside of an package in Oracle, here is the SPEC:
PROCEDURE selectapprovedexpenses (v_output OUT hfctypes.ref_cursor);

And HERE is the BODY:
PROCEDURE selectapprovedexpenses (
      v_output       OUT      hfctypes.ref_cursor
   )
   IS
   BEGIN
      
         OPEN v_output FOR
            select hfcfunc.getusername (a.user_id) user_name,
            A.CELL_NUMBER,A.CELL_PROVIDER,A.EXPENSE_AMOUNTS,to_char(A.CREATED_DATE,'MM/DD/YYYY') created_date,hpcs_func.getlookupcodebyid (a.location) LOCATION,A.PHONE_NUMBER
             from 
            equipment_request a
            where hpcs_func.getlookupcodebyid
                                                                 (a.equip_type) =
                                                                  'EXPENSE'
            order by created_date desc;
   END selectapprovedexpenses;

When CF calls the stored procedure, it may or may not work the first time, but then may work on the 2nd or 3rd time.  

This is happening to all applications we have on our DEV box with CF9 Beta 2 that use Oracle and Oracle Stored Procedures.
Result:

Error Executing Database Query.  
[Macromedia][Oracle JDBC Driver]No more data available to read.  
  
 
Resources: 
Enable Robust Exception Information to provide greater detail about the source of errors. In the Administrator, click Debugging & Logging > Debug Output Settings, and select the Robust Exception Information option. 
Check the ColdFusion documentation to verify that you are using the correct syntax. 
Search the Knowledge Base to find a solution to your problem.

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3038574

Deployment Phase:	Release Candidate

External Customer Info:
External Company:  
External Customer Name: Jeffrey Cookle
External Customer Email: 56C7328143F0F8F50AAC0C19
External Test Config: 05/14/2009

Attachments:

Comments: