0

I am working on an ASP.NET web application project in C# with an Oracle db backend. Currently our application is using Oracle's ODP.NET since Microsoft's System.Data.OracleClient has been depricated. We are having compatibility issues with our test and production servers so we are creating an ODBC version of our application. I am having trouble with CommandText and adding parameters for the OdbcCommand. Here is what I have for the ODP.NET:

using (OracleCommand cmd = new OracleCommand())
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "oracle_pkg.sproc_name";
    cmd.Parameters.Add("param1", txtbox1.Text);
    cmd.Parameters.Add("param2", txtbox2.Text);
    cmd.Parameters.Add(new OracleParameter("o_refcursor", OracleDbType.RefCursor, ParameterDirection.Output));
    cmd.Connection = UHFUtility.GetDBConnection();
    OracleDataAdapter oda = new OracleDataAdapter(cmd);
}

using (OdbcCommand cmd = new OdbcCommand())
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "{ ?= call oracl_pkg.sproc_name( ?, ?) }";
    cmd.Parameters.Add("p_start_date", txtFromDate.Text);
    cmd.Parameters.Add("p_end_date", txtToDate.Text);
    **cmd.Parameters.Add(new OdbcParameter("o_rc", OdbcType.Something, ParameterDirection.Output));**
    cmd.Connection = UHFUtility.GetMetricsDBConnection();
    OdbcDataAdapter oda = new OdbcDataAdapter(cmd);
}

I utilized these two web pages as my reference to create the CommandText string: http://support.microsoft.com/kb/255043/en-us and http://msdn.microsoft.com/en-us/library/system.data.odbc.odbccommand.commandtext(v=vs.110).aspx

I am trying to figure out what the syntax of the bold line should be for ODBC. Here's a post I found on StackOverflow: What is the proper odbc command for calling Oracle stored procedure with parameters from .Net?, but I am trying to find out if .NETs ODBC provider supports receiving the ref_cursor.

I am not sure how the CommandText is supposed to be formed especially since I have a SYS_REFCURSOR as an out parameter from the stored procedure.

Many thanks,

Jared

Community
  • 1
  • 1
iJared
  • 887
  • 3
  • 13
  • 26
  • In the past, I've found Oracle and ODBC don't always play nicely together. For example, ODBC freaked out on a "with" clause. It wouldn't surprise me that anything that's not a standard "select - from" could break ODBC. It's not answering your question, but can you elaborate on the "compatibility" issues with ODP.net? It should function fine across different versions/instances of Oracle. – Hambone Sep 05 '14 at 21:03
  • We are getting this error: "The provider is not compatible with the version of Oracle client" and I've been working with our administrator and we tried a few solutions found around the internet to no avail. The only details I know are we are running OracleClient 11.2.x.x 64bit on the test web server I am using 64bit ODP.NET and something something. I am not quite sure what else is going on. Everything works fine for me on my development station, its when I deploy to our test server that we get this compatibility error. – iJared Sep 05 '14 at 21:32
  • Just a thought... try managed ODP.net instead. It does not rely on the locally installed Oracle client. – Hambone Sep 06 '14 at 23:49

0 Answers0