CODEWITHSUNDEEP
c#oracle-databasestored-functions
Neil Benn
Neil Benn

Reputation: 926

Passed Parameter in Stored Function with C# and Oracle

I have an Oracle stored function which in Oracle is called as follows:

DECLARE
  return_data varchar2(32767);
BEGIN

  return_data := UCB_SYNC.GET_PROTEIN_DETAILS('PB0000007');
  DBMS_OUTPUT.PUT_LINE(return_data);
END;

This works just fine so I now want to call it in C#; so I have the following code:

    public string ExecuteStoredProcedure()
    {
        using (DbConnection cnn = GetNewConnection())
        {
            cnn.Open();
            using (DbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = "ucb_sync.get_protein_details";
                cmd.CommandType = CommandType.StoredProcedure;

                DbParameter dbp = cmd.CreateParameter();
                dbp.ParameterName = "protein_batch_id";
                dbp.Value = "PB0000007";
                dbp.DbType = DbType.String;
                cmd.Parameters.Add(dbp);

                DbParameter returnDbp = cmd.CreateParameter();
                returnDbp.Direction = ParameterDirection.ReturnValue;
                returnDbp.ParameterName ="result_data";
                returnDbp.DbType = DbType.String;
                cmd.Parameters.Add(returnDbp);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw e;
                }
                return returnDbp.Value.ToString();

            }
        }
    }

This executes but the first parameter (protein_batch_id) gets passed through as an empty string. I can see this from my logs and the exception that comes back from the Oracle also confirms this.

It's a simple question; can anyone see what I'm doing wring to cause the first parameter to get passed through as an empty string?

Thanks for your help; it's dark, late and I need to get this done before going home!

Upvotes: 0

Views: 174

Answers (1)

Luke Woodward
Luke Woodward

Reputation: 64949

You need to set the BindByName property of the command to true.

To do this replace the line

        using (DbCommand cmd = cnn.CreateCommand())

with

        using (OracleCommand cmd = cnn.CreateCommand())

and then add the line

            cmd.BindByName = true;

to the other lines that configure cmd.

Upvotes: 1

Related Questions