Problem getting data from a stored procedure (Oracle)

[GDavy]

On the Oracle DB there's a stored proc defined like:

PROCEDURE pGetHashes ( iFrom IN NUMBER, iTo IN NUMBER, sHash1 OUT CHAR, sHash2 OUT CHAR );

When I call this procedure from within my app, I only get a value for the sHash2 parameter. The value of the sHash1 parameter is always null. (Running the same stored proc from sqldeveloper gives a result for both hash values.) Underneath I have added the code which I use to call the stored proc. Does anybody see anything I might have done wrong?

            int iFrom = 0;
            int iTo = 1000;
            using (IDbCommand command = dbConnection.CreateCommand())
            {
                OracleCommand orclCommand = command as OracleCommand;
                orclCommand.CommandText = "pGetHashes";
                orclCommand.CommandType = CommandType.StoredProcedure;
                
                orclCommand.Parameters.Clear();
                orclCommand.Parameters.Add("iFrom", OracleDbType.Int32, iFrom, ParameterDirection.Input);
                orclCommand.Parameters.Add("iTo", OracleDbType.Int32, iTo, ParameterDirection.Input);

                OracleParameter orclParam = new OracleParameter("sHash1", OracleDbType.Char, 100);
                orclParam.Direction = ParameterDirection.Output;
                orclCommand.Parameters.Add(orclParam);
                orclParam = new OracleParameter("sHash2", OracleDbType.Char, 100);
                orclParam.Direction = ParameterDirection.Output;
                orclCommand.Parameters.Add(orclParam);

                orclCommand.BindByName = true;

                orclCommand.ExecuteNonQuery();

                // after this the orclCommand.Parameters\["sHash1"\].Value is always null. 
                // the orclCommand.Parameters\["sHash2"\].Value has the correct value.
            }

For extra documentation. Running the following PLSQL from within sqldeveloper results in both a value for Hash1 and Hash2:

SET SERVEROUTPUT ON;
DECLARE
sHash1 CHAR(67);
sHash2 CHAR(67);
nFrom NUMBER := 0;
nTo NUMBER := 1000;
BEGIN

pGetHashes( nFrom, nTo, sHash1, sHash2 );

dbms_output.put_line('Hash1: '|| sHash1);
dbms_output.put_line('Hash2: '|| sHash2);
END;

Thanks for any light you can shed on this problem.

[micke andersson]

Maybe you need two orclParam variables? You create it with:

OracleParameter orclParam = new OracleParameter("sHash1", OracleDbType.Char, 100);

But later you assign a new object to it:

orclParam = new OracleParameter("sHash2", OracleDbType.Char, 100);

I would be very surprised if this fixes your problem but it might be worth a try? If it fixes your problem then the Add() function is implemented... in a peculiar way.