web analytics
How to call stored function from C# and ODP.NET Options
codeling
Posted: Wednesday, May 24, 2017 11:22:14 AM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 784
Points: 3288

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

 

Sponsor
Posted: Wednesday, May 24, 2017 11:22:14 AM
 
codeling
Posted: Wednesday, May 24, 2017 11:26:35 AM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 784
Points: 3288

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
    }
}
Users browsing this topic
Guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.


© 2017 Digcode.com. All rights reserved.