Unable to get Oracle Package function to execute in c#

Question

I'm having difficulty trying to call my DBA's function inside C#. It works fine in the Oracle query browser, however when I attempt to execute it in C# it fails.

The most common error is : "ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1"

I've attempted to increase the size of the C# parameters but to no success. Not sure what I'm doing wrong.

C# Code :

     string returnValue = string.Empty;
        using (OracleConnection cn = new OracleConnection("ConnectionString"))

        {
            using (OracleCommand cmd = new OracleCommand()) 
            {
                cmd.Connection = cn;


                cmd.CommandText = "package.function";
                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter param1 = new OracleParameter();
                OracleParameter param2 = new OracleParameter();
                OracleParameter param3 = new OracleParameter();
                OracleParameter param4 = new OracleParameter();
                OracleParameter param5 = new OracleParameter();

                param1.OracleDbType = OracleDbType.Varchar2;
                param1.Direction = ParameterDirection.Input;
                param1.Value = "Test808";
                param1.ParameterName = "var1";
                //param1.Size = 2000;

                param2.OracleDbType = OracleDbType.Varchar2;
                param2.Direction = ParameterDirection.Input;
                param2.Value = "68B54814";
                param2.ParameterName = "var2";
                //param2.Size = 2000;

                param3.OracleDbType = OracleDbType.Varchar2;
                param3.Direction = ParameterDirection.Input;
                param3.Value = "71839";
                param3.ParameterName = "var4";
                //param3.Size = 2000;

                param4.OracleDbType = OracleDbType.Varchar2;
                param4.Direction = ParameterDirection.Input;
                param4.Value = 55 ;
                param4.ParameterName = "var4";
               //param4.Size = 2000;

                param5.OracleDbType = OracleDbType.Varchar2;
                param5.Direction = ParameterDirection.ReturnValue;
                param5.ParameterName = "return";
                //param5.Size = 2000;



                cmd.Parameters.Add(param1);
                cmd.Parameters.Add(param2);
                cmd.Parameters.Add(param3);
                cmd.Parameters.Add(param4);
                cmd.Parameters.Add(param5);


                try
                {
                    cn.Open();

                    cmd.ExecuteNonQuery();

                    returnValue = cmd.Parameters["return"].Value.ToString();
                }
                catch
                {

                }
                finally
                {
                    cn.Close();
                }

            }
        }

        return returnValue;

Function Header:

    FUNCTION create_rec
      (p_mims_pallet_id varchar2
      ,p_item_no varchar2
      ,p_packer_id varchar2
      ,p_tare_wt varchar2
      )
      return varchar2

1st time trying Oracle calls. Never had an issue with SQL. The parameter.Size is commented out for not seeming to do anything.

Was able to get it working by removing the individual Parameters and creating them the Parameter.Add method.

Working Code:

     string returnValue = string.Empty;
        using (OracleConnection cn = new OracleConnection("ConString"))

        {
            using (OracleCommand cmd = new OracleCommand()) 
            {
                cmd.Connection = cn;
                cmd.CommandText = "package.function";
                cmd.CommandType = CommandType.StoredProcedure;


                cmd.Parameters.Add("rv", OracleDbType.Varchar2, 200, "", ParameterDirection.ReturnValue);
                cmd.Parameters.Add("var1", OracleDbType.Varchar2, 20, "Test808", ParameterDirection.Input);
                cmd.Parameters.Add("var2", OracleDbType.Varchar2, 20, "68B54814", ParameterDirection.Input);
                cmd.Parameters.Add("var3", OracleDbType.Varchar2, 20, "71839", ParameterDirection.Input);
                cmd.Parameters.Add("var4", OracleDbType.Decimal, 55, ParameterDirection.Input);

                try
                {
                    cn.Open();

                    cmd.ExecuteNonQuery();

                    returnValue = cmd.Parameters["rv"].Value.ToString();
                }
                catch
                {

                }
                finally
                {
                    cn.Close();
                }

            }
        }

        return returnValue;

check the varchar2 sizes of the fields you are trying to insert/update and compare with the values you are passing. — cableload
– cableload, Commented May 9, 2013 at 18:07
I'm pretty sure you have to give the C# parameter names the same names as the Oracle names, so instead of for example param1.ParameterName = "var1"; try param1.ParameterName = "p_mims_pallet_id"; and so on. That doesn't explain the error you reported, but you mentioned you had others. As for the parameter Size, I just make sure it's at least big enough for the value I'm passing; bigger should be fine too. — Ed Gibbs
– Ed Gibbs, Commented May 9, 2013 at 18:10
I've checked the table structure and everything checks out. I'm believed it to be something with the C# code at this point. If it works fine on Oracles end when called from a SQL browser, then I must being doing something wrong. Just don't see it. Changed all the parameter names to their respected header names and still got the same error. Also changed the .Size to 2000 and same error is thrown. — Tim B
– Tim B, Commented May 9, 2013 at 18:30
You've set the type of param4 to Varchar2 but you assign an integer value to it. Not sure if that would cause your error, but couldn't hurt to fix that. — Mike
– Mike, Commented May 9, 2013 at 22:32

MSAK24 · Accepted Answer · 2013-09-12 08:29:03Z

1

I fixed this issue by changing its syntax. It was giving numeric or value overflow error if we use below syntax:

cmd.Parameters.Add("strSqlMsg", OracleDbType.Varchar2, 255, ParameterDirection.Output);

Which I changed to below and it worked.

cmd.Parameters.Add("strSqlMsg", OracleDbType.Varchar2, ParameterDirection.Output).Size=255;

Hope this will work for you. Thanks. Atul

answered Sep 12, 2013 at 8:29

MSAK24

532 silver badges9 bronze badges

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

Comments

Top Cat · Accepted Answer · 2014-10-29 06:49:06Z

string strRetrun = string.Empty;
 using (OracleConnection objCon = (OracleConnection)_connection)
        {
            using (OracleCommand objCom = new OracleCommand())
            {
                objCom.Connection = objCon;
                objCom.CommandText = "SAM.PKG_SAM_ECH.F_FUND_TRANSFER_TYPE";
                objCom.CommandType = CommandType.StoredProcedure;

                OracleParameter codeReturn = new OracleParameter("RETURN", OracleType.VarChar, 1000);
                codeReturn.Direction = ParameterDirection.ReturnValue;


                OracleParameter code1 = new OracleParameter("V_RECORD_ID", OracleType.Number);
                code1.Direction = ParameterDirection.Input;
                if (obj.RecordId != null)
                    code1.Value = obj.RecordId;
                else
                    code1.Value = DBNull.Value;


                OracleParameter code2 = new OracleParameter("V_DEBIT_APAC", OracleType.VarChar, 200);
                code2.Direction = ParameterDirection.Input;
                if (obj.P_BENEF_APAC != null)
                    code2.Value = obj.P_BENEF_APAC;
                else
                    code2.Value = DBNull.Value;


                OracleParameter code3 = new OracleParameter("V_ACCOUNT_TYPE", OracleType.VarChar, 200);
                code3.Direction = ParameterDirection.Input;
                if (obj.BenfAccType != null)
                    code3.Value = obj.BenfAccType;
                else
                    code3.Value = DBNull.Value;

                OracleParameter code4 = new OracleParameter("V_IFSC_CODE", OracleType.VarChar, 200);
                code4.Direction = ParameterDirection.Input;
                if (obj.IFSC != null)
                    code4.Value = obj.IFSC;
                else
                    code4.Value = DBNull.Value;


                objCom.Parameters.Add(codeReturn);
                objCom.Parameters.Add(code1);
                objCom.Parameters.Add(code2);
                objCom.Parameters.Add(code3);
                objCom.Parameters.Add(code4);

                try
                {
                    objCon.Open();

                    objCom.ExecuteNonQuery();

                    strRetrun = objCom.Parameters["RETURN"].Value.ToString();
                }
                catch
                {

                }
                finally
                {
                    objCon.Close();
                }

            }
        }


        return strRetrun;

DoB · Accepted Answer · 2019-03-01 10:31:32Z

0

To everybody who is wondering why the answer from Tim B is working. It's the order of the parameters! The return parameter of the function has to be bound first! Also for the other Parameters the order counts and not the name.

The first version with creating the parameters individually, would be OK if "param5" would have been added as first object in to the collection.

I got stuck on this for while :-(

answered Mar 1, 2019 at 10:31

DoB

133 bronze badges

Collectives™ on Stack Overflow

Unable to get Oracle Package function to execute in c#

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related