Default values of Oracle SP parameters not working with ODP.net in DAAB 5.0

Topics: Building and extending application blocks, Data Access Application Block
Jun 30, 2011 at 2:42 PM

I'm trying to use Oracle.DataAccess.Client provider 11g with DAAB 5.0. I have done the necessary customization for Oracle data types & RefCursor. Now the problem I'm facing is Oracle.DataAccess.Client provider uses stored procedure parameter binding by position. This I've changed by overriding GetStoredProcCommand where I've set the BindByName property of the DBCommand by boxing it as OracleCommand to true [ ((OracleCommand)command).BindByName = true; ]. The parameter collection is populated using the OracleCommandBuilder.DeriveParameters((OracleCommand)discoveryCommand).

The problem I'm facing is when I try to fetch a dataset from the stored procedure nothing is returned. I've identified the problem to be the default values of the stored procedure parameters. The stored procedure is like this

CREATE OR REPLACE PROCEDURE MYPROC 
( 
p_param1 IN NUMBER DEFAULT 4, 
p_param2 IN VARCHAR2 DEFAULT NULL, 
p_param3 IN VARCHAR2 DEFAULT NULL, 
p_param4 IN VARCHAR2 DEFAULT NULL, 
p_ref_cursor OUT sys_refcursor 
) 
AS 
v_sys_error NUMBER := 0; 
BEGIN 
BEGIN 
IF p_opmode = 4 THEN 
BEGIN 
BEGIN 
OPEN p_ref_cursor FOR SELECT FLD1,FLD2,FLD3 FROM MYTABLE WHERE FLD2 = p_param2; 
EXCEPTION 
WHEN OTHERS THEN 
v_sys_error := SQLCODE; 
END; 
IF v_sys_error <> 0 THEN 
BEGIN 
GOTO err_handler; 
END; 
END IF; 
END; 
ELSE 
BEGIN 
BEGIN 
OPEN p_ref_cursor FOR SELECT FLD1,FLD2,FLD3 FROM MYTABLE WHERE FLD2 = p_param3 AND FLD5 = p_param4; 
EXCEPTION 
WHEN OTHERS THEN 
v_sys_error := SQLCODE; 
END; 
IF v_sys_error <> 0 THEN 
BEGIN 
GOTO err_handler; 
END; 
END IF; 
END; 
END IF; 
END; 
<<cleanup>> 
RETURN; 
<<err_handler>> 
v_errormsg := fetchMsg(v_sys_error); 
raise_application_error (-20002, ':' || v_errormsg); 
GOTO cleanup; 
END; 

 

When I'm calling this SP I'm not assigning any value of the parameter "p_param1" since it has a default value (skipping the parameter). In this scenario the dataset returned does not contain a datatable. If I assign the value to the parameter then it returns records. Is there something I'm missing or doing wrong.

Oracle.DataAccess.dll 2.112.2.0

.Net Framework 3.5 SP 1

Visual Studio 2008

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Jul 1, 2011 at 9:18 AM

Hi,

I'm afraid this is more of an ODP.NET issue. Try asking this to ODP.NET Forum to get a better answer.

 

Noel Angelo Bolasoc
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

 

Jul 1, 2011 at 10:17 AM

Hi Noel

The OracleCommand's .BindByName is not exposed through System.Data.IDbCommand, that means I won’t have access to it from my code. How do I set the .BindByName property in this case.

Jul 1, 2011 at 10:47 AM

This is because the property BindByName is only inherent to ODP.NET. Since you already did ((OracleCommand)command).BindByName = true and it doesn't work, I really don't have any more idea how to get this done as I haven't personally worked with ODP.NET. Though, I have found this article that suggests modifying the Database Class. Hope this helps.

 

Noel Angelo Bolasoc
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Jul 1, 2011 at 3:51 PM

I've done it exactly like the example depicted in the article. Can't understand why this is not functioning. Is there any road map of implementing the same in the future releases of DAAB.

Jul 8, 2011 at 5:38 AM

We'll reach out with Microsoft and let you know for any feedbacks.

 

Noel Angelo Bolasoc
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com