Code for calling a function in a package from C# and ODP.NET

Question

I've tried to write C# code with ODP.NET to call a function in a package. I'm getting the two errors below:

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

ORA-06550: line 1, column 7:
PLS-00221: 'INSERT_FUNC' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

My OracleCommand is set up as:

cmd.CommandText = "PKG_NAME.INSERT_FUNC";
cmd.CommandType = CommandType.StoredProcedure;

How should I pass parameters to the function below?
Do I need to add a ReturnValue parameter? I've seen many forums discussing the need to add a ReturnValue parameter as the first OracleParameter on the OracleCommand object.

I would appreciate any suggestions.

CREATE OR REPLACE
PACKAGE BODY pkg_name IS
  FUNCTION insert_func (
      i_description IN  tableName.description%TYPE,
      i_theme       IN  tableName.theme%TYPE,
      o_id          OUT tableName.id%TYPE,
      o_error_msg   OUT VARCHAR2 )
    RETURN NUMBER
  IS
    l_program VARCHAR2(100) := 'PKG_NAME.INSERT_FUNC';
  BEGIN
    INSERT INTO tablea ( event_id, id, description, theme, lock_version )
      VALUES ( rms12.tablea_seq.NEXTVAL, rms12.tablea_id_seq.NEXTVAL, i_description, i_theme, NULL );
    INSERT INTO tableb ( id, description, theme )
      VALUES ( rms12.id_seq.CURRVAL, i_description, i_theme );
    SELECT rms12.id_seq.CURRVAL
      INTO o_id
      FROM dual;
    RETURN 1;
  EXCEPTION
    WHEN OTHERS THEN
      o_error_msg := sql_lib.create_msg(
          'PACKAGE_ERROR', SQLERRM, l_program, TO_CHAR( SQLCODE ) );
      RETURN 0;
  END insert_func;
END pkg_name;

Vadim K. · Accepted Answer · 2013-09-18 18:33:27Z

27

This is my first question on this forum and I am happy to post to my own answer.

We can call an oracle package function using ODP.NET by setting CommandType.StoredProcedure.

ORA-06550: line 1, column 7:
PLS-00221: 'INSERT_FUNC' is not a procedure or is undefined
ORA-06550: line 1, column 7: PL/SQL: Statement ignored

If you get this error, just add this line as the first parameter on the command object:

cmd.Parameters.Add("Return_Value", OracleDbType.Int16,
    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
    }
}

edited Sep 18, 2013 at 18:33

Vadim K.

2,61122 silver badges30 bronze badges

answered Sep 15, 2013 at 16:16

user2776709

4211 gold badge5 silver badges8 bronze badges

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

6 Comments

Amir Over a year ago

Why must we add cmd.Parameters.Add("Return_Value", OracleDbType.Int16, ParameterDirection.ReturnValue); as first parameter?

Tim Newton Over a year ago

seems like such an unlikely answer but it works, thanks for sharing!

Fernando JS Over a year ago

This part "cmd.BindByName = true;" save my life, if you have the same problem to execute packages, dont forget.

Jarek Mitek Over a year ago

@FernandoJS thank man, that was exactly what i needed! The only way to get the default (in my case) is not to provide the parameter at all. If the default parameter is not last then then you must bind by name or the drivers will complain.

Adam W Over a year ago

Wow - I spend few hours trying all kinds of parameter combinations - Errors all the time. By using tip from this answer I got it working. I'm calling Oracle 11g Functions (in packages) and returning cursor!. Thanks a lot for this tip.

|

Ken · Accepted Answer · 2014-11-29 03:07:56Z

2

This must be new with a more recent version of Oracle. I was previously able to do this with the return value parameter listed after all of the input parameters in my C# code, but after running this on 12c I had this exact issue, which now works with this suggestion of putting the return val param first.

answered Nov 29, 2014 at 3:07

Ken

212 bronze badges

Comments

ppenchev · Accepted Answer · 2019-12-05 12:47:03Z

I have created a generic method that helps you get your function result

var result = await 
 dbManager.ExecuteFunctionResultAsync<Oracle.ManagedDataAccess.Types.OracleDecimal>(
                     functionName, parameters.List);


public async Task<T> ExecuteFunctionResultAsync<T>(string spName, IEnumerable<OracleParameter> paramaters)
        {
            using (OracleConnection connection = new OracleConnection(this.connectionString))
            {
                connection.Open();
                using (OracleCommand comm = new OracleCommand(spName, connection))
                {
                    comm.CommandType = CommandType.StoredProcedure;
                    comm.BindByName = true;
                    string returnParam = "return_value";
                    comm.Parameters.Add(new OracleParameter() {
                        ParameterName = returnParam,
                        Direction = ParameterDirection.ReturnValue,
                        OracleDbType = OracleDbType.Int16,
                    });

                    this.SetCommandParameters(comm, paramaters);
                    await comm.ExecuteNonQueryAsync();
                    var result = (T)comm.Parameters[returnParam].Value;

                    return result;
                }
            }
        }

private void SetCommandParameters(OracleCommand command, IEnumerable<OracleParameter> paramaters)
        {
            if (paramaters != null)
            {
                foreach (OracleParameter p in paramaters)
                {
                    command.Parameters.Add(p);
                }
            }
        }

Collectives™ on Stack Overflow

Code for calling a function in a package from C# and ODP.NET

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related