ORA-06502: PL/SQL: numeric or value error with stored procedure and ASP.NET MVC

Question

I am trying to insert data into Oracle database from ASP.NET MVC application. I am using a stored procedure to do so, but I get this error:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "C##AET.KATEGORIJA_PKG", line 40 ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1

Procedure code is:

PROCEDURE INS_KATEGORIJA
  (
    P_NAZIV IN KATEGORIJA.NAZIV%TYPE,

    P_KATEGORIJA_ID OUT KATEGORIJA.KATEGORIJA_ID%TYPE,
    P_REZULTAT OUT NUMBER,
    P_PORUKA OUT VARCHAR2
  )
  IS
  BEGIN
    P_REZULTAT := 1;

    SELECT KATEGORIJA_SEQ.NEXTVAL INTO P_KATEGORIJA_ID FROM DUAL;

    INSERT INTO KATEGORIJA
    VALUES (P_KATEGORIJA_ID, P_NAZIV);

    P_REZULTAT := 0;
    P_PORUKA := 'DB: Kategorija uspesno uneta.';

    EXCEPTION
    WHEN OTHERS THEN
      P_REZULTAT := 1;
      P_PORUKA := 'DB: ' ||SQLCODE|| ' - ' ||SQLERRM;
  END INS_KATEGORIJA;

And the method calling it:

List<OracleParameter> izvrsiTransakciju(string nazivProcedure, List<OracleParameter> parametri, OracleTransaction transakcija)
        {
            try
            {
                using (OracleCommand komanda = new OracleCommand(nazivProcedure, konekcija))
                {
                    komanda.Transaction = transakcija;
                    komanda.CommandType = CommandType.StoredProcedure;
                    komanda.BindByName = true;

                    List<OracleParameter> izlazniParametri = new List<OracleParameter>();

                    foreach (OracleParameter p in parametri)
                    {
                        komanda.Parameters.Add(p);

                        if (p.Direction != ParameterDirection.Input) izlazniParametri.Add(p);
                    }

                    komanda.ExecuteNonQuery();

                    return izlazniParametri;
                }
            }
            catch (Exception ex)
            {

                throw new Exception("Greška pri izvršavanju transakcije: " + ex.Message);
            }
        }

The methods that communicates with the database are:

    public  List<OracleParameter> PokreniTransakciju(string nazivProcedure, List<OracleParameter> parametri)
            {
                try
                {
                    List<OracleParameter> listaParametara;
                    using (konekcija)
                    {
                        konekcija.Open();

                        using (OracleTransaction transakcija = konekcija.BeginTransaction())
                        {
                            listaParametara = izvrsiTransakciju(nazivProcedure, parametri, transakcija);

                            int rezultat = Convert.ToInt32(listaParametara.FirstOrDefault(x => x.ParameterName == "P_REZULTAT"));
                            if (rezultat != 0)
                                transakcija.Rollback();
                            else
                                transakcija.Commit();

                            return listaParametara;
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

    List<OracleParameter> izvrsiTransakciju(string nazivProcedure, List<OracleParameter> parametri, OracleTransaction transakcija)
            {
                try
                {
                    using (OracleCommand komanda = new OracleCommand(nazivProcedure, konekcija))
                    {
                        komanda.Transaction = transakcija;
                        komanda.CommandType = CommandType.StoredProcedure;
                        komanda.BindByName = true;

                        List<OracleParameter> izlazniParametri = new List<OracleParameter>();

                        foreach (OracleParameter p in parametri)
                        {
                            komanda.Parameters.Add(p);

                            if (p.Direction != ParameterDirection.Input) izlazniParametri.Add(p);
                        }

                        komanda.ExecuteNonQuery();

                        return izlazniParametri;
                    }
                }
                catch (Exception ex)
                {

                    throw new Exception("Greška pri izvršavanju transakcije: " + ex.Message);
                }
            }

public OracleParameter NapraviParametar(string naziv, object vrednost, OracleDbType? tip, ParameterDirection pravac)
        {
            OracleParameter param = new OracleParameter();

            param.ParameterName = naziv;
            param.Direction = pravac;

            if (tip != null)
                param.OracleDbType = (OracleDbType)tip;

            dodajVrednost(param, vrednost);

            return param;
        }

        void dodajVrednost(OracleParameter param, object vrednost)
        {
            if (vrednost == null) return;

            if (vrednost is int)
                param.Value = (int)vrednost;
            else if (vrednost is decimal)
                param.Value = (decimal)vrednost;
            else if (vrednost is string)
                param.Value = (string)vrednost;
            else if (vrednost is DateTime)
                param.Value = (DateTime)vrednost;
        }

Any ideas? Thank you in advance!

[SOLUTION] With much appreciated assistance from LauDec, I figured out that one must specify the Size attribute of an OracleParameter object when defining Varchar2 parameters. Added that, and the problem was solved.

Answer 1

apparently, it comes from variable affected to the P_PORUKA out parameter when calling the INS_KATEGORIJA procedure which is too small to store the message you are trying to put in ( based on discussion in comments ).

If you called the INS_KATEGORIJA from inside your package, I would have understood the behavious :

  DECLARE 
    PORUKA VARCHAR2(10); 
    KATEGORIJA_ID NUMBER; 
    REZULTA NUMBER; 
  BEGIN 
    INS_KATEGORIJA('TEST',KATEGORIJA_ID,REZULTA,PORUKA); 
  END;

This will definitely raise the mentionned error message. But I didn't expect when calling from the outside. ( Does your code calls directly the INS_KATEGORIJA or does it call another procedure from your package which calls INS_KATEGORIJA ? )

I Guess you have to change the code in the caller of PokreniTransakciju, the one who defines the out variable(and their size) used to call the procedure

Edit : The reasons was the following based on OP's comment :

When one defines OracleParameter of Varchar2 type, OracleParameter.Size is required (it seems it is set to 0 if it isn't manually set).