Pass function parameter in Oracle function using C# is not working

Question

I am calling Oracle Function from my C# code like:

string spName = "ANALYZERS.NEXT_COUNTER";
        OracleCommand oracleCommand = new OracleCommand(spName, Utils.Connection)
        {
            CommandType = System.Data.CommandType.StoredProcedure
        };

        oracleCommand.Parameters.Add("P_COUNTER_TYPE", OracleDbType.Varchar2).Value = "ANALYZERS.STRING_COUNTER";
        oracleCommand.Parameters.Add("P_WHERE_CLAUSE", OracleDbType.Varchar2).Value = "RCH04";
        oracleCommand.Parameters.Add("P_CALLING_OBJECT", OracleDbType.Varchar2).Value = null;
        oracleCommand.Parameters.Add("P_CALLING_USER", OracleDbType.Varchar2).Value = null;
        oracleCommand.Parameters.Add("P_CAlLING_EVENT", OracleDbType.Varchar2).Value = null;

        oracleCommand.Parameters.Add("P_ERROR", OracleDbType.Varchar2);
        oracleCommand.Parameters["P_ERROR"].Direction = System.Data.ParameterDirection.Output;

        oracleCommand.Parameters.Add("RETURN_VALUE", OracleDbType.Varchar2);
        oracleCommand.Parameters["RETURN_VALUE"].Size = 4000;
        oracleCommand.Parameters["RETURN_VALUE"].Direction = System.Data.ParameterDirection.ReturnValue;

        if (Utils.Connection.State != System.Data.ConnectionState.Open)
        {
            Utils.Connection.Open();
        }
        try
        {
            oracleCommand.ExecuteNonQuery();
            var retValue = oracleCommand.Parameters["RETURN_VALUE"].Value;
            stringId = retValue.ToString();
            Console.WriteLine(stringId);
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
        finally
        {
            Utils.Connection.Close();
        }

When I execute it, I get Unknown counter type.... message in retValue which is wrong. It is because the value of parameter P_COUNTER_TYPE is incorrect which means value is not passed properly.

What is other way of passing VARCHAR2 value in Oracle function? Note that I am using OracleDbType instead of OracleType.

I have seen articles which uses OracleType. Is there any issue of using OracleDbType instead of OracleType?

Chunk of Oracle Function:

CREATE OR REPLACE FUNCTION ANALYZERS.NEXT_COUNTER(P_COUNTER_TYPE   IN VARCHAR2,
                                              P_WHERE_CLAUSE   IN VARCHAR2 DEFAULT NULL,
                                              P_CALLING_OBJECT IN VARCHAR2,
                                              P_CALLING_USER   IN VARCHAR2,
                                              P_CAlLING_EVENT  IN VARCHAR2,
                                              P_ERROR          OUT VARCHAR2)
   RETURN VARCHAR2 IS

   .....

    IF P_COUNTER_TYPE = 'ANALYZERS.STRING_COUNTER' THEN
--  Old Code
--  SELECT ANALYZERS.SEQ_STRING_COUNTER.NEXTVAL INTO V_RESULT FROM DUAL;
--
--    RETURN(SUBSTR(V_RESULT, -10));
SELECT CURRENT_YEAR
  INTO V_CURR_YEAR
  FROM ANALYZERS.SYSTEM_CURRENT_YEAR CY;
SELECT ANALYZERS.SEQ_STRING_COUNTER.NEXTVAL INTO V_RESULT FROM DUAL;
IF NVL(LENGTH(V_RESULT), 0) >= 8 THEN
  V_NEXT_NUM := SUBSTR(V_RESULT, -8);
ELSE
  V_NEXT_NUM := V_RESULT;
END IF;
V_RESULT := P_CALLING_LOCATION_ID || V_CURR_YEAR ||
            LPAD(V_NEXT_NUM, 8, '0');
RETURN V_RESULT;
ELSE
P_ERROR  := 'Unknown counter type....';
V_RESULT := NULL;
END IF;
 RETURN V_RESULT;

So what do you get in P_COUNTER_TYPE? Did you log or output that? — nvoigt
– nvoigt, Commented Sep 14, 2018 at 13:20
The problem was, you need to pass parameters in same order as defined in Function. — Faizan Mubasher
– Faizan Mubasher, Commented Oct 8, 2018 at 5:18

Jimenemex · Accepted Answer · 2018-09-14 13:53:41Z

2

The way you are entering the OracleParameters looks a little funky to me.

Instead try this,

OracleParameter [] parameters = {
    new OracleParameter("P_COUNTER_TYPE", OracleDbType.Varchar2, "ANALYZERS.STRING_COUNTER", ParameterDirection.Input);
    ...
};

command.Parameters.AddRange(parameters);

edited Sep 14, 2018 at 13:53

answered Sep 14, 2018 at 13:27

Jimenemex

3,1863 gold badges29 silver badges65 bronze badges

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

2 Comments

Faizan Mubasher Over a year ago

I tried the way you have mentioned but didn't work. Eventually it worked only when I passed parameters in orders as defined in Function and problem solved.

Jimenemex Over a year ago

@FaizanMubasher You might find the BindByName property to be useful to you. Just make sure your parameters are called the same in the C# code and SQL code. command.BindByName = true.

Collectives™ on Stack Overflow

Pass function parameter in Oracle function using C# is not working

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related