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;

1. How should I pass parameters to the function below?
2. 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;

Landmeier

Answer 1

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
    }
}

Landmeier

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

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

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

@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. – Steffen

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. – Turboprop

I would prefer cmd.ExecuteNonQuery() rather than cmd.ExecuteReader(). ExecuteReader() returns a OracleDataReader object (i.e. a data set), so typically you use it when the function/procedure returns RefCursor or when you run a query. For a single scalar return value ExecuteNonQuery() (or maybe ExecuteScalar()) is more suitable. – Farfetched

Answer 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.

Kobarid

Answer 3

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);
                }
            }
        }

Cotta