Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2008
    Posts
    5

    Unanswered: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    HELP... one of my DBA wrote me this oracle sp, when my web form call this sp, I will pass over 2 params which which are firstname and lastname, then this sp will insert the 2 params to the table, if the insert succ, the sp will return 0 else return and 99 for error,
    below is on the asp.net call the sp and i am getting this error when execute cmd.ExecuteNonQuery();[/

    ORA-06502: PL/SQL: numeric or value error: character string buffer too small




    PROCEDURE Insert_Data(
    i_lastname IN VARCHAR2,
    i_firstname IN VARCHAR2,
    error_code OUT NUMBER,
    error_mes OUT VARCHAR2
    )
    IS
    BEGIN

    INSERT
    INTO new_table(
    rec_id,
    i_firstname,
    i_lastname,
    created_date,
    new_old,
    created_by
    )
    VALUES (
    rec_id.nextval,
    firstname,
    lastname,
    SYSDATE,
    'New'
    );

    error_code := SQLCODE;
    error_mes := SUBSTR (SQLERRM, 1, 250);

    COMMIT;

    EXCEPTION

    WHEN OTHERS THEN
    error_code := SQLCODE;
    error_mes := SUBSTR (SQLERRM, 1, 250);

    END;


    ---------------------------------


    public int Insert_Data(string first_name, string last_name)
    {
    string OracleConnString = ConfigurationManager.ConnectionStrings["connstr"].ToString();

    int return_code;

    using (OracleConnection conn = new OracleConnection(OracleConnString))
    {
    OracleCommand cmd = new OracleCommand("Class.Insert_Data", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("i_firstname", OracleDbType.Varchar2, first_name, ParameterDirection.Input);
    cmd.Parameters.Add("i_lastname", OracleDbType.Varchar2, last_name, ParameterDirection.Input);
    cmd.Parameters.Add("oerror_code", OracleDbType.Int32).Direction = ParameterDirection.Output;
    cmd.Parameters.Add("oerror_message", OracleDbType.Varchar2).Direction = ParameterDirection.Output;


    conn.Open();
    cmd.ExecuteNonQuery();
    return return_code = Convert.ToInt32(cmd.Parameters["oerror_code"].Value);
    }
    }

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    This is a data dependent error.
    The length of the input string exceeds the size of the variable that gets the input string.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2008
    Posts
    5
    Thanks for reply
    in the table its set varchar2(50) and varchar2(250)
    and i only passing in a few character per param, i look at the error it stated
    line 64 ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512:

    which is the line: oerror_message := SUBSTR (SQLERRM, 1, 250);

    any ideas ?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >line 64 ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512:


    It would be helpful if you posted the WHOLE procedure including line numbers.
    It would be helpful if you used <code tags> as describe in the #1 STICKY post at the top of this forum.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2008
    Posts
    5
    This is the best i can do, the error occur on .net side when tried to execute cmd.ExecuteNonQUery (asp code is in the first post) and the error on the oracle side is at line 64 which i highlighted below


    PROCEDURE Insert_Data
    (
    i_lastname IN VARCHAR2,
    i_firstname IN VARCHAR2,
    error_code OUT NUMBER,
    error_mes OUT VARCHAR2
    )

    IS
    BEGIN

    /*
    Function:
    Version:
    Purpose:
    Systems:
    File:
    Project:
    Author:
    Created:
    Called By:
    Parameters:
    Call Syntax:
    Call Sample:
    Returns:
    Tables:
    Calls:
    Exceptions:
    Copyright:
    Modified:
    */


    INSERT
    INTO new_table
    (
    rec_id,
    i_firstname,
    i_lastname,
    created_date,
    new_old,
    created_by
    )

    VALUES
    (
    rec_id.nextval,
    firstname,
    lastname,
    SYSDATE,
    'New'
    );

    error_code := SQLCODE;
    error_mes := SUBSTR (SQLERRM, 1, 250);

    COMMIT;

    EXCEPTION

    WHEN OTHERS THEN
    error_code := SQLCODE;
    error_mes := SUBSTR (SQLERRM, 1, 250);
    END;

  6. #6
    Join Date
    Feb 2008
    Posts
    5
    Got it to work on System.Data.OracleClient; but getting an error when try to use Oracle.DataAccess.Client;

    error: Unable to cast object of type 'Oracle.DataAccess.Types.OracleDecimal' to type 'System.IConvertible'.

    public int Insert_Data(string first_name, string last_name)
    {
    string OracleConnString = ConfigurationManager.ConnectionStrings["connstr"].ToString();

    int return_code;

    using (OracleConnection conn = new OracleConnection(OracleConnString))
    {
    OracleCommand cmd = new OracleCommand("Class.Insert_Data", conn);
    cmd.CommandType = CommandType.StoredProcedure;



    //using Microsoft WORK OK
    //cmd.Parameters.Add("i_firstname ", OracleType.VarChar,256).Value = ren_uname;
    //cmd.Parameters.Add("i_lastname", OracleType.VarChar, 256).Value = ren_fname;
    //cmd.Parameters.Add("oerror_code", OracleType.Int32,256).Direction = ParameterDirection.Output;
    //cmd.Parameters.Add("oerror_message", OracleType.VarChar,256).Direction = ParameterDirection.Output;

    //using Oracle
    cmd.Parameters.Add("i_firstname ", OracleDbType.Varchar2,256).Value = ren_uname;
    cmd.Parameters.Add("i_lastname ", OracleDbType.Varchar2, 256).Value = ren_fname;
    cmd.Parameters.Add("oerror_code", OracleDbType.Int32, 256).Direction = ParameterDirection.Output;
    cmd.Parameters.Add("oerror_message", OracleDbType.Varchar2, 256).Direction = ParameterDirection.Output;

    conn.Open();
    cmd.ExecuteNonQuery();
    //string sql_mes = cmd.Parameters["oerror_message"].Value.ToString();
    return sqlcode = Convert.ToInt32(cmd.Parameters["oerror_code"].Value);

    }
    }

  7. #7
    Join Date
    Feb 2008
    Posts
    5
    i got it to work by add tostring
    return sqlcode = Convert.ToInt32(cmd.Parameters["oerror_code"].Value.ToString());

  8. #8
    Join Date
    Dec 2012
    Posts
    1

    ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    Hi everybody !!
    I found one trivial thing.. I've a .NET App hitting an Oracle DB, invoking an stored procedure with two OUT params.. I wasted almost whole day trying to resolve this "ORA-06502: PL/SQL: numeric or value error: character string buffer too small"... The fu!c$k#ing error was that i forgot to put the parameter size in .NET:

    oParam = New OracleParameter("p_Esquema", OracleDbType.Varchar2)
    oParam.Direction = ParameterDirection.Output
    oParam.Size = 10
    oComm.Parameters.Add(oParam)


    I hope this would be useful !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •