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

    Question

  • I have some problems with calling stored procedure (written in PL/SQL - Oracle) from VB 2005.

    I assume that problem is in part where I define 2 parameters which are sent to stored procedure:

    VB 2005 code:
    Declaring first of two parameters
    Code Snippet

    Dim param1 As OracleClient.OracleParameter = New OracleClient.OracleParameter()
            param1.Value = dataGridSource.CurrentRow.Cells(1).Value
            param1.Direction = ParameterDirection.Input
            param1.Size = 9
            param1.DbType = DbType.String
            param1.OracleType = OracleClient.OracleType.VarChar
            param1.ParameterName = "P_PLATE"
            command.Parameters.Add(param1)


    Second parameter:
    Code Snippet

    Dim param2 As OracleClient.OracleParameter = New OracleClient.OracleParameter()
            param2.Value = dataGridSource.CurrentRow.Cells(2).Value
            param2.Direction = ParameterDirection.Input
            param2.Size = 6
            param2.DbType = DbType.String
            param2.OracleType = OracleClient.OracleType.VarChar
            param2.ParameterName = "P_SERIES"
            command.Parameters.Add(param2)


    Existing stored procedure in PL/SQL, works fine (tested with calling from VB 6)
    Code Snippet

    PROCEDURE Move_in_stack_up(p_plate string,p_series string) is
       w_zap_num number;
       w_plate varchar2(9);
       w_series varchar2(9);
       w_temp_plate_num number;
       w_temp_stack string(4);
       BEGIN
       
       select l.kup_poz,l.kup into w_zap_st,w_temp_kup
       from plate_location l
       Where l.plate = p_plate and l.series = p_series;

    .....


    I am not sure about DbType and OracleType, but I tried a lot of combinations.
    In Visual Studio in Server Explorer I get that P_PLATE and P_SERIES are both data type VARCHAR2. Unfortunately in parameter declaration I can't specify OracleType.VarChar2 (just VarChar).

    Have anyone some suggestions about my problem?
    Thank you in advance!

    maMile
    Wednesday, January 23, 2008 7:40 AM

All replies

  • Are you attempting to work with values which exceed the size specified in the parameter statements? That would appear to be what the error message is referring to.
    Wednesday, January 23, 2008 2:32 PM
  • Test your code updating on param2 the Size property to 9, according to the code below:

     

    Dim param2 As OracleClient.OracleParameter = New OracleClient.OracleParameter()
            param2.Value = dataGridSource.CurrentRow.Cells(2).Value
            param2.Direction = ParameterDirection.Input
            param2.Size = 9

            param2.DbType = DbType.String
            param2.OracleType = OracleClient.OracleType.VarChar
            param2.ParameterName = "P_SERIES"
            command.Parameters.Add(param2)

    Tuesday, January 29, 2008 6:42 PM
  • Are you attempting to work with values which exceed the size specified in the parameter statements? That would appear to be what the error message is referring to.

    >> This would be the first place any programmer would have checked before posting question anywhere.
    Monday, June 29, 2009 3:05 AM