web analytics

How to call stored function from C# and ODP.NET

Options

codeling 1602 - 6666
@2017-05-24 11:22:14

Try to write C# code with ODP.NET to call a stored function in a package. the following exception is thrown:

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to INSERT_FUNC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

 

@2017-05-24 11:26:35

If you get this error, it means that either you missed the fillowing return value parameter or the the return value paramter is not the first parameter in case when you are using no-name parameter. Just add the following parameter as the first parameter on the command object:

cmd.Parameters.Add("Return_Value", OracleDbType.Int32, ParameterDirection.ReturnValue);

Here is the working code:

using (var conn = new OracleConnection(oradb))
using (var cmd = conn.CreateCommand())
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "PKG_NAME.INSERT_FUNC";

    cmd.BindByName = true;

    cmd.Parameters.Add("Return_Value", OracleDbType.Int16,
        ParameterDirection.ReturnValue);
    cmd.Parameters.Add("i_description", OracleDbType.Varchar2, 1000,
        promotionEventSetupDetails.PromotionDescription,
        ParameterDirection.Input);
    cmd.Parameters.Add("i_theme", OracleDbType.Varchar2, 80,
        promotionEventSetupDetails.PromotionTheme,
        ParameterDirection.Input);
    cmd.Parameters.Add("o_id", OracleDbType.Varchar2,
        ParameterDirection.Output);
    cmd.Parameters.Add("o_error_msg", OracleDbType.Varchar2,
        ParameterDirection.Output);

    conn.Open();
    using (var dr = cmd.ExecuteReader())
    {
        // do some work here
    }
}

Comments

You must Sign In to comment on this topic.


© 2024 Digcode.com