Oracle.DataAccess.Client.OracleException ORA-06502: PL/SQL: numeric or value error: character string buffer too small. Error at ExecuteReader step

Question

I have the following error which I gave when I try to execute my query:

Oracle.DataAccess.Client.OracleException ORA-06502: PL/SQL: numeric or value error: character string buffer too small

The piece of code which throws an exception looks like this:

OracleConnection ocnn = (OracleConnection)this.Session.Connection;

using (OracleCommand command = ocnn.CreateCommand())
            {
                string getFieldValuesQuery =
                    "SELECT FIELD_NAME"
                    + "FROM FIELD_VALUES WHERE FIELD_GUID in (select ID from table(f_parse_as_table(:fieldIds)) tab ) "
                    + "AND DOCUMENT_GUID in (select ID from table(f_parse_as_table(:documentIds)) tab ) "
                    + "AND RESOURCE_STATUS != 'DEL' ";

                // value is a string which have 55 characters lenght
                if (value != null)
                {
                    getFieldValuesQuery += "AND TEXT_VALUE in (select ID from table(f_parse_as_table(:valuesIn)) tab ) ";
                }

                command.CommandText = getFieldValuesQuery;
                command.BindByName = true;

                OracleParameter fieldIdParameter = new OracleParameter();
                OracleParameter documentIdParameter = new OracleParameter();
                //OracleParameter valuesInParameter = new OracleParameter();
                OracleParameter valuesInParameter = new OracleParameter("valuesIn", OracleDbType.Varchar2, 2000, ParameterDirection.Input);


                    fieldIdParameter.ParameterName = "fieldIds";
                    fieldIdParameter.OracleDbType = OracleDbType.Clob;
                    fieldIdParameter.Direction = ParameterDirection.Input;
                    fieldIdParameter.Value = string.Join(",", fieldIds);

                    documentIdParameter.ParameterName = "documentIds";
                    documentIdParameter.OracleDbType = OracleDbType.Clob;
                    documentIdParameter.Direction = ParameterDirection.Input;
                    documentIdParameter.Value = string.Join(",", documentIds);

                    command.Parameters.Add(fieldIdParameter);
                    command.Parameters.Add(documentIdParameter);

                    if (value != null)
                    {
                        //valuesInParameter.ParameterName = "valuesIn";
                        //valuesInParameter.OracleDbType = OracleDbType.Varchar2;
                        //valuesInParameter.Direction = ParameterDirection.Input;
                        valuesInParameter.Value = string.Join(",", value);

                        command.Parameters.Add(value);
                    }

                    returnList = this.ExecuteReader(command);
                }

I know that the similar answers were asked here but they didn't help me. According to this question I set the length of oracle parameter.

All works fine if the length of value is less or equals to 50 characters. I don't know where this limitation appears. It's strange, because my value filed has VARCHAR2 type in the table definition and has the length of 2000 characters.

Error was thrown when the ExecuteReader method was called.

You are calling function in the database called f_parse_as_table(). I'm going to guess this function takes in a VARCHAR2(50), which is why it works when the parameter is less then or equal to 50 characters. — unleashed

unleashed unleashed · Accepted Answer · 2017-04-07T13:46:27

You are calling function in the database called f_parse_as_table(). I'm going to guess this function takes in a VARCHAR2(50), which is why it works when the parameter is less then or equal to 50 characters.

Oracle.DataAccess.Client.OracleException ORA-06502: PL/SQL: numeric or value error: character string buffer too small. Error at ExecuteReader step

1 Answers