ODP.NET

I am trying to call a procedure through ODP that passes in one input parameter and returns two (non-RefCursor) VARCHAR2 output parameters. I am calling the procedure using ExecuteNonQuery(); however, my parameters always return null. When I run the procedure outside of ODP, such as with SQLPlus or SQL Navigator, the output parameters are populated correctly. For some reason, there appears to be a disconnect inside of ODP. Is there a way to resolve this?

Anyone have this problem?


Here is the basic code:


===========================================================
// External call of the class below
DBNonCursorParameterTest Tester = new DBNonCursorParameterTest();

===========================================================
// The class and constructor that calls the procedure and prints the results.
public class DBNonCursorParameterTest
{

public DBNonCursorParameterTest()
{
// The test procedure I used is a procedure that takes a recordID (Int32) and then returns a
// general Name (Varchar2) and a Legal Name (Varchar2) from one table with those three fields.

string strProcName = "MyTestProc;
OracleConnection conn = new OracleConnection(DBConnection.ConnectionString);
OracleCommand cmd = new OracleCommand(strProcName,conn);
cmd.CommandType = CommandType.StoredProcedure;


// Create the input parameter and the output cursor parameter to retrieve data; assign a value to the input parameter;
// then create the parameter collection and add the parameters.
OracleParameter pBPID = new OracleParameter("p_bpid", OracleDbType.Int32, ParameterDirection.Input);
OracleParameter pBPName = new OracleParameter("p_Name", OracleDbType.Varchar2, ParameterDirection.Output);
OracleParameter pBPLegalName = new OracleParameter("p_LegalName", OracleDbType.Varchar2, ParameterDirection.Output);

pBPID.Value = 1;

// Open connection and run stored procedure.
try
{
conn.Open();

cmd.Parameters.Add(pBPID);
cmd.Parameters.Add(pBPName);
cmd.Parameters.Add(pBPLegalName);

cmd.ExecuteNonQuery();

Console.Write("\n" + cmd.CommandText + "\n\n");

//for (int i = 0; i < cmd.Parameters.Count; i++)
//{
// Console.WriteLine("Parameter: " + cmd.Parameters.ParameterName + " Direction = " + cmd.Parameters[i].Direction.ToString());
// Console.WriteLine("Parameter: " + cmd.Parameters[i].ParameterName + " Status = " + cmd.Parameters[i].Status.ToString());
// Console.WriteLine("Parameter: " + cmd.Parameters[i].ParameterName + " Value = " + cmd.Parameters[i].Value.ToString() + "\n");
//}

foreach (OracleParameter orap in cmd.Parameters)
{
Console.WriteLine("Parameter: " + orap.ParameterName + " Direction = " + orap.Direction.ToString() + " Value = " + orap.Value.ToString());
Console.WriteLine("Parameter: " + orap.ParameterName + " Status = " + orap.Status.ToString());
Console.WriteLine("Parameter: " + orap.ParameterName + " Value = " + orap.Value.ToString() + "\n");

}
// End Test code.
}
catch (Exception ex)
{
throw new Exception("ExecuteQuery() failed: " + ex.Message);
}
finally
{
this.Close();
}

}

public void Close()
{
if (conn.State != ConnectionState.Closed)
conn.Close();
}
}

=========================================================

Other things to note:

I have no problems with returning RefCursors; they work fine. I just don't want to use RefCursors when they are not efficient, and I want to have the ability to return output parameters when I only want to return single values and/or a value from an insert/update/delete.

Thanks for any help you can provide.