Passed Parameter in Stored Function with C# and Oracle

Question

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!

Are you sure that the name of the parameter to the function is actually protein_batch_id? So return_data := UCB_SYNC.GET_PROTEIN_DETAILS(protein_batch_id => 'PB0000007'); also successfully calls the function? — Justin Cave
– Justin Cave, Commented Mar 17, 2016 at 22:12

Luke Woodward · Accepted Answer · 2016-03-17 23:01:14Z

1

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.

answered Mar 17, 2016 at 23:01

Luke Woodward

65.3k16 gold badges94 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Neil Benn Over a year ago

Ah; that means I've got to make an oracle subclass for this. I'll need to refactor for that a bit. However; I changed the PL/SQL method from a function to a procedure with an OUT parameter for the result and that worked just fine. I'll roll with that but come back to this with that suggestion.

Collectives™ on Stack Overflow

Passed Parameter in Stored Function with C# and Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related