8 Replies Latest reply: Apr 28, 2014 10:34 AM by rp0428 RSS

    Random : java.sql.SQLException: ORA-20000: ORA-06502: PL/SQL: numeric or value error: character string buffer too smallORA-06512

    Rohit Jadhav

      Hi,

       

      We have done upgrade below upgrades

       

      1) IBM WAS6 to IBM WAS7. (Datasource on WAS7 is RAC)

      2) JDK 1.4 to JDK1.6.0_45

      3) Oracle 10G to Oracle 11G

       

      We created couple of servers at client side, and one server at our local Test Env (offshore).

      We are facing issue as mentioned in subject line when we are running one of our application screens. Same screen with same Param and same env when run in our local env we not getting the error. I am doubting on Datasource which has RAC url as well as Some DB Param if any would be great help.

       

      Java Code :

       


      package com.iflex.fcr.bh.procedure;
      //-----------------------------------------------------------------------------
      import java.sql.Types;
      import java.sql.Timestamp;
      import java.sql.ResultSet;
      import java.text.SimpleDateFormat;
      import java.text.DecimalFormat;
      import java.text.NumberFormat;
      import oracle.jdbc.OracleTypes;
      import com.iflex.fcr.common.FCRJProcedureExecutor;
      import com.iflex.fcr.common.FCRJProcedureExecutorFactory;
      import com.iflex.fcr.common.FCRJProcedureParameter;
      import com.iflex.fcr.common.FCRJQueryResultBean;
      import com.iflex.fcr.common.FCRJProcedureConstants;
      //-----------------------------------------------------------------------------

       

      public final class
             ap_ch_stop_chq_mnt
      extends
             FCRJProcedureExecutor
      {
      //--------------------------------------------------------------------------
      public static final String
        VERSION_INFO   = "VERSION$ : Generated on 2010-01-11 18:29:02.525 : 10.180.13.79 "
      ;
      public static final String
        THIS_CLASS_NAME   = ap_ch_stop_chq_mnt.class.getName ()
      ;
      //--------------------------------------------------------------------------
      static {

        FCRJProcedureExecutorFactory.resetProcedureInstances ("ap_ch_stop_chq_mnt");
        procParamTable.remove ("ap_ch_stop_chq_mnt");

      }
      //--------------------------------------------------------------------------
      public ap_ch_stop_chq_mnt ()
      throws Exception {

        super (
         "ap_ch_stop_chq_mnt"
        ,
      true
        , 12
        );

        if (procParamsLoaded) {
         return;
        }

       
        procParams [1] = new FCRJProcedureParameter (1
            , "p_field_list_new.COD_ACCT_NO"
            , ""
            , ""
            , Types.VARCHAR
            , true);
        procParams [2] = new FCRJProcedureParameter (2
            , "p_field_list_new.REF_STP_CHQ_ST_NO"
            , ""
            , ""
            , Types.VARCHAR
            , true);
        procParams [3] = new FCRJProcedureParameter (3
            , "p_field_list_new.REF_STP_CHQ_END_NO"
            , ""
            , ""
            , Types.VARCHAR
            , true);
        procParams [4] = new FCRJProcedureParameter (4
            , "p_field_list_new.DAT_CHQ_STOP"
            , ""
            , ""
            , Types.TIMESTAMP
            , true);
        procParams [5] = new FCRJProcedureParameter (5
            , "p_field_list_new.DAT_STOP_CHQ_INST"
            , ""
            , ""
            , Types.TIMESTAMP
            , true);
        procParams [6] = new FCRJProcedureParameter (6
            , ""
            , ""
            , ""
            , Types.CHAR
            , true);
        procParams [7] = new FCRJProcedureParameter (7
            , "MuCommonStruct.cod_func"
            , ""
            , ""
            , Types.CHAR
            , true);
        procParams [8] = new FCRJProcedureParameter (8
            , "MuCommonStruct.cod_func_old"
            , ""
            , " "
            , Types.CHAR
            , false);
        procParams [9] = new FCRJProcedureParameter (9
            , "p_field_list_new.FLG_CHQ_STAT"
            , ""
            , ""
            , Types.CHAR
            , true);
        procParams [10] = new FCRJProcedureParameter (10
            , ""
            , "p_field_list_new.NAM_CCY_SHORT"
            , ""
            , Types.VARCHAR
            , true);
        procParams [11] = new FCRJProcedureParameter (11
            , ""
            , ""
            , ""
            , Types.INTEGER
            , true);
        procParams [12] = new FCRJProcedureParameter (12
            , ""
            , ""
            , ""
            , Types.INTEGER
            , true);


      }
      //--------------------------------------------------------------------------

      public void extractMaxRowsRelatedTags ()
      throws Exception {
        strMaxRowsToFetchTagName = "";
           
        strNoOfRowsSelectedTagName = "";

        intMaxRowsToFetch = -1;
      }
      //--------------------------------------------------------------------------

      public void extractErrorCodeTag ()
      throws Exception {
        strErrorCodeTagName = "";
      }
      //--------------------------------------------------------------------------
      protected void registerOutputParameters ()
      throws Exception {

                  procStmt.registerOutParameter (
         11, Types.VARCHAR);
         procStmt.registerOutParameter (
         12, Types.INTEGER);
         procStmt.registerOutParameter (
         1, Types.INTEGER);
        
      }
      //--------------------------------------------------------------------------
      protected void setInputParameters (
      ) throws Exception {

        String l_value = null;

        if ((l_value = getValueFromSource (1)) != null) {
         procStmt.setString (
          2
         ,  l_value
         );
        }
        if ((l_value = getValueFromSource (2)) != null) {
         procStmt.setString (
          3
         ,  l_value
         );
        }
        if ((l_value = getValueFromSource (3)) != null) {
         procStmt.setString (
          4
         ,  l_value
         );
        }
        if ((l_value = getValueFromSource (4)) != null) {
         procStmt.setTimestamp (
          5
         ,  new java.sql.Timestamp ((new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")).parse (l_value).getTime ())
         );
        }
        if ((l_value = getValueFromSource (5)) != null) {
         procStmt.setTimestamp (
          6
         ,  new java.sql.Timestamp ((new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")).parse (l_value).getTime ())
         );
        }
        if ((l_value = getValueFromSource (6)) != null) {
         procStmt.setString (
          7
         ,  l_value
         );
        }
        if ((l_value = getValueFromSource (7)) != null) {
         procStmt.setString (
          8
         ,  l_value
         );
        }
        if ((l_value = getValueFromSource (8)) != null) {
         procStmt.setString (
          9
         ,  l_value
         );
        }
        if ((l_value = getValueFromSource (9)) != null) {
         procStmt.setString (
          10
         ,  l_value
         );
        }
      }
      //--------------------------------------------------------------------------
      protected void extractOutputValues ()
      throws Exception {

        Object l_obj = null;
        String l_value = null;
       
        setValueInDest (
         10
        ,  procStmt.getString (11)
        );
        setValueInDest (
         11
        ,  String.valueOf (procStmt.getInt (12))
        );
       
        setValueInDest (
         12
        ,  String.valueOf (procStmt.getInt (1))
        );
       
        l_obj   = null;
        l_value = null;
      }
      //--------------------------------------------------------------------------
      protected boolean hasCursor () {

        return false;
      }
      //--------------------------------------------------------------------------
      }
      //------------------------------------------------------------------------------
      //
      // End of file
      //
      //------------------------------------------------------------------------------

       

       

      Proc Structure

       

      CREATE OR REPLACE FUNCTION ap_ch_stop_chq_mnt(cod_acct_no        CHAR,

      ref_stp_chq_st_no      VARCHAR2,

      ref_stp_chq_end_no   IN OUT   VARCHAR2,

      dat_chq_stop        DATE,

      dat_stop_chq_inst      DATE,

      p_flg_select        CHAR,

      p_cod_func        CHAR,

      p_cod_func_old       CHAR,

      flg_chq_stat        CHAR,

      var_ccy_shrt_nam     IN OUT  VARCHAR2,

      var_p_err_code       IN OUT  NUMBER)

       

       

      Error  in client env :

       

      2014-04-23 12:02:04,539(Log4JHelper.java:83) - THE PROCEDURE STATEMENT IS --->

      {? = call ap_ch_stop_chq_mnt (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}

      2014-04-23 12:02:04,542(Log4JHelper.java:83) - p_ordinal = 1 l_value = 0001104000384

      2014-04-23 12:02:04,543(Log4JHelper.java:83) - p_ordinal = 2 l_value = 000000000001

      2014-04-23 12:02:04,544(Log4JHelper.java:83) - p_ordinal = 3 l_value = null

      2014-04-23 12:02:04,545(Log4JHelper.java:83) - p_ordinal = 4 l_value = 2015-09-30 00:00:00

      2014-04-23 12:02:04,547(Log4JHelper.java:83) - p_ordinal = 5 l_value = 2015-09-30 00:00:00

      2014-04-23 12:02:04,548(Log4JHelper.java:83) - p_ordinal = 6 l_value = I

      2014-04-23 12:02:04,549(Log4JHelper.java:83) - p_ordinal = 7 l_value = a

      2014-04-23 12:02:04,551(Log4JHelper.java:83) - p_ordinal = 8 l_value = null

      2014-04-23 12:02:04,552(Log4JHelper.java:83) - p_ordinal = 9 l_value = null

       

       

      Error in FCRJProcedureExecutor.execute
      ## FCR Database Message Trace Start ##

      ## FCR Database Message Trace End  ##

      ## FCR Stack Trace of Causing Exception Start ##
      java.sql.SQLException: ORA-20000: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "MZFCRH.AP_CH_STOP_CHQ_MNT", line 62
      Execution of ap_ch_stop_chq_mnt failed Line: 253
      ORA-06512: at "MZFCRH.ORA_RAISERROR", line 34
      ORA-06512: at "MZFCRH.AP_CH_STOP_CHQ_MNT", line 174
      ORA-06512: at line 1

        • 2. Re: Random : java.sql.SQLException: ORA-20000: ORA-06502: PL/SQL: numeric or value error: character string buffer too smallORA-06512
          Rohit Jadhav

          Agreed, But in current scenario, data is same and is not different, on the contrary, I created one stub and fetch connection from jdbc and fired proc from same. In response there wasn't any error. But from application is returning ora error.

          • 4. Re: Random : java.sql.SQLException: ORA-20000: ORA-06502: PL/SQL: numeric or value error: character string buffer too smallORA-06512
            Rohit Jadhav

            Hi

             

            As mentioned in the thread, We are passing null in input from Java Bind variables (ref java class above and input params above).


            To simulate i ran a simple Java Stub (stand alone)


            1) JDBC Connection

            2) CallableStatment

            3) Passed same parameters as printed above in failing environment. Program works fine.

            4) Ran the procedure with same params from PLSQL Developer., Program works fine.



            Procedure when called runtime from application server, we passed same params but observed ora-06502.

            failing for ref_stp_chq_end_no   IN OUT   VARCHAR2 (3 INOUT Param in proc).


            Runtime value from ref_stp_chq_st_no (2 IN Param in proc -> 000000000001) gets assigned to ref_stp_chq_end_no which is exactly 12, and is expected type for Types.VARCHAR in Java binding.


            is there a possibility that some extra character is getting appended from java binding at runtime from App Server?


            Since same program with same values running in my local env so does from java standalone stub i have created works fine in failing env on other hand.


            Program working fine in WAS6 and Oracle 10G Environment.


            Note  : I will check on your suggestion as well and revert back.

            • 5. Re: Random : java.sql.SQLException: ORA-20000: ORA-06502: PL/SQL: numeric or value error: character string buffer too smallORA-06512
              sb92075

              06502, 00000, "PL/SQL: numeric or value error%s"

              // *Cause: An arithmetic, numeric, string, conversion, or constraint error

              //         occurred. For example, this error occurs if an attempt is made to

              //         assign the value NULL to a variable declared NOT NULL, or if an

              //         attempt is made to assign an integer larger than 99 to a variable

              //         declared NUMBER(2).  

              // *Action: Change the data, how it is manipulated, or how it is declared so

              //          that values do not violate constraints.

               

              same data produces same results

              different results occur when data is different.

              • 6. Re: Random : java.sql.SQLException: ORA-20000: ORA-06502: PL/SQL: numeric or value error: character string buffer too smallORA-06512
                rp0428
                java.sql.SQLException: ORA-20000: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "MZFCRH.AP_CH_STOP_CHQ_MNT", line 62

                Ok - so you have an exception at line 62 in your procedure.

                 

                Your procedure is trying to assign a value to the parameter that is LARGER than the buffer.

                So - is there some reason you haven't posted the code so we can see what the procedure is doing at line 62?

                • 7. Re: Random : java.sql.SQLException: ORA-20000: ORA-06502: PL/SQL: numeric or value error: character string buffer too smallORA-06512
                  Rohit Jadhav

                  sb92075 : As mentioned earlier this procedure is working fine when executed from PLSQL Developer or from Stand Alone java file. Also I have similar environment in which procedure is working fine. Again on WAS6 + jdbc14.jar procedure is working fine.

                   

                   

                  ref_stp_chq_end_no := ref_stp_chq_st_no; -- failure line. (Below is the stored procedure)

                  one quick finding,

                  I could see when I assign ref_stp_chq_end_no:= '1'; proc works fine but when I assign ref_stp_chq_end_no:='11' then proc fails.

                  Input binding has not value restrictions for VARCHAR so it is default which is more then 32767, is it like Java Binding is putting any restrictions?

                  When I run proc from PLSQL Developer, things seems to be working successful. issue is with call from application server.

                   

                  But again behavior is not the same in my env.

                   

                   

                   

                  Below is the stored procedure,   

                   

                  CREATE OR REPLACE FUNCTION ap_ch_stop_chq_mnt(cod_acct_no        CHAR,

                                                                ref_stp_chq_st_no  VARCHAR2,

                                                                ref_stp_chq_end_no IN OUT VARCHAR2,

                                                                dat_chq_stop       DATE,

                                                                dat_stop_chq_inst  DATE,

                                                                p_flg_select       CHAR,

                                                                p_cod_func         CHAR,

                                                                p_cod_func_old     CHAR,

                                                                flg_chq_stat       CHAR,

                                                                var_ccy_shrt_nam   IN OUT VARCHAR2,

                                                                var_p_err_code     IN OUT NUMBER)

                    RETURN NUMBER AS

                    ret_val     NUMBER;

                    l_err_codes VARCHAR2(10);

                  BEGIN

                    IF (p_flg_select = 'I') THEN

                      BEGIN

                        ret_val := ap_ch_mnt_valkey_stop_chq(cod_acct_no,

                                                             ref_stp_chq_st_no,

                                                             l_err_codes,

                                                             var_ccy_shrt_nam);

                        IF (ret_val != 0) THEN

                          BEGIN

                            var_p_err_code := ret_val;

                            ora_raiserror(SQLCODE,

                                          'Error while executing ap_ch_mnt_valkey_stop_chq',

                                          48);

                            RETURN ret_val;

                          END;

                        END IF;

                        IF (SUBSTR(l_err_codes, 4, 1) = '1') THEN

                          BEGIN

                            var_p_err_code := 2853;

                            RETURN var_p_err_code;

                          END;

                        END IF;

                        IF (p_cod_func != 'z' AND p_cod_func != 'k' AND p_cod_func != 'n' AND

                           p_cod_func != 'i' AND SUBSTR(l_err_codes, 3, 1) = '1') THEN

                          BEGIN

                            var_p_err_code := 3029;

                            RETURN var_p_err_code;

                          END;

                        END IF;

                        IF (SUBSTR(l_err_codes, 2, 1) = '1') THEN

                          BEGIN

                            var_p_err_code := 3011;

                            RETURN var_p_err_code;

                          END;

                        END IF;

                        IF (SUBSTR(l_err_codes, 1, 1) = '1') THEN

                          BEGIN

                            var_p_err_code := 2946;

                            RETURN var_p_err_code;

                          END;

                        END IF;

                        IF (p_cod_func = 'a') THEN

                         BEGIN

                            ref_stp_chq_end_no := ref_stp_chq_st_no; -- failure line.

                          END;

                        END IF;

                        IF (p_cod_func = 'a') THEN

                          BEGIN

                            ret_val := ap_ch_mnt_val_stop_chq(cod_acct_no,

                                                              ref_stp_chq_st_no,

                                                              ref_stp_chq_end_no,

                                                              dat_chq_stop,

                                                              dat_stop_chq_inst,

                                                              flg_chq_stat,

                                                              l_err_codes);

                            IF (ret_val != 0) THEN

                              BEGIN

                                var_p_err_code := ret_val;

                                RETURN var_p_err_code;

                              END;

                            END IF;

                            IF (SUBSTR(l_err_codes, 7, 1) = '1') THEN

                              BEGIN

                                var_p_err_code := 3232;

                                RETURN var_p_err_code;

                              END;

                            END IF;

                          END;

                        END IF;

                        IF ((p_cod_func = 'z') AND (p_cod_func_old = 'A')) THEN

                          BEGIN

                            ret_val := ap_ch_mnt_val_stop_chq(cod_acct_no,

                                                              ref_stp_chq_st_no,

                                                              ref_stp_chq_end_no,

                                                              dat_chq_stop,

                                                              dat_stop_chq_inst,

                                                              flg_chq_stat,

                                                              l_err_codes);

                            IF (ret_val != 0) THEN

                              BEGIN

                                var_p_err_code := RET_VAL;

                                RETURN var_p_err_code;

                              END;

                            END IF;

                            IF (SUBSTR(l_err_codes, 6, 1) = '1') THEN

                              BEGIN

                                var_p_err_code := 3230;

                                RETURN var_p_err_code;

                              END;

                            END IF;

                          END;

                        END IF;

                      END;

                    ELSE

                      BEGIN

                        ret_val := ap_ch_mnt_val_stop_chq(cod_acct_no,

                                                          ref_stp_chq_st_no,

                                                          ref_stp_chq_end_no,

                                                          dat_chq_stop,

                                                          dat_stop_chq_inst,

                                                          flg_chq_stat,

                                                          l_err_codes);

                        IF (ret_val != 0) THEN

                          BEGIN

                            var_p_err_code := ret_val;

                            RETURN var_p_err_code;

                          END;

                        END IF;

                        IF (SUBSTR(l_err_codes, 2, 1) = '1') THEN

                          BEGIN

                            var_p_err_code := 2947;

                            RETURN var_p_err_code;

                          END;

                        END IF;

                        IF (SUBSTR(l_err_codes, 3, 1) = '1') THEN

                          BEGIN

                            var_p_err_code := 3012;

                            RETURN var_p_err_code;

                          END;

                        END IF;

                        IF (SUBSTR(l_err_codes, 4, 1) = '1') THEN

                          BEGIN

                            var_p_err_code := 3013;

                            RETURN var_p_err_code;

                          END;

                        END IF;

                        IF (SUBSTR(l_err_codes, 9, 1) = '1') THEN

                          BEGIN

                            var_p_err_code := 3009;

                            RETURN var_p_err_code;

                          END;

                        END IF;

                        IF (SUBSTR(l_err_codes, 8, 1) = '1') THEN

                          BEGIN

                            var_p_err_code := 3229;

                            RETURN var_p_err_code;

                          END;

                        END IF;

                        IF (SUBSTR(l_err_codes, 6, 1) = '1') THEN

                          BEGIN

                            var_p_err_code := 3230;

                            RETURN var_p_err_code;

                          END;

                        END IF;

                        IF (((p_cod_func = 'a') OR (p_cod_func_old = 'A')) AND

                           (SUBSTR(l_err_codes, 5, 1) = '1')) THEN

                          BEGIN

                            var_p_err_code := 3231;

                            RETURN var_p_err_code;

                          END;

                        END IF;

                        IF (SUBSTR(l_err_codes, 7, 1) = '1') THEN

                          BEGIN

                            var_p_err_code := 3232;

                            RETURN var_p_err_code;

                          END;

                        END IF;

                      END;

                    END IF;

                    RETURN 0;

                  EXCEPTION

                    WHEN OTHERS THEN

                      ora_raiserror(SQLCODE, 'Execution of ap_ch_stop_chq_mnt failed', 253);

                      RETURN 95;

                  END;

                  • 8. Re: Random : java.sql.SQLException: ORA-20000: ORA-06502: PL/SQL: numeric or value error: character string buffer too smallORA-06512
                    rp0428

                    I don't see how that code you posted could work in ANY environment.

                     

                    You either posted the wrong code or you have bigger problems than what you think you have.

                     

                    The statement code you posted has TWELVE parameters

                    2014-04-23 12:02:04,539(Log4JHelper.java:83) - THE PROCEDURE STATEMENT IS --->

                    {? = call ap_ch_stop_chq_mnt (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}

                    The function code you just posted has ELEVEN parameters.

                    Below is the stored procedure,   

                     

                    CREATE OR REPLACE FUNCTION ap_ch_stop_chq_mnt(cod_acct_no        CHAR,

                                                                  ref_stp_chq_st_no  VARCHAR2,

                                                                  ref_stp_chq_end_no IN OUT VARCHAR2,

                                                                  dat_chq_stop       DATE,

                                                                  dat_stop_chq_inst  DATE,

                                                                  p_flg_select       CHAR,

                                                                  p_cod_func         CHAR,

                                                                  p_cod_func_old     CHAR,

                                                                  flg_chq_stat       CHAR,

                                                                  var_ccy_shrt_nam   IN OUT VARCHAR2,

                                                                  var_p_err_code     IN OUT NUMBER)

                      RETURN NUMBER AS

                    The three OUT parameters are #3, #10 and #11 of the function and #4, #11 and !12 of the statement.

                     

                    But you are registering #1, #11 and #12 as OUT parameters in the code you posted:

                    protected void registerOutputParameters ()

                    throws Exception { 

                                procStmt.registerOutParameter (
                       11, Types.VARCHAR);
                       procStmt.registerOutParameter (
                       12, Types.INTEGER);
                       procStmt.registerOutParameter (
                       1, Types.INTEGER);
                      
                    }

                    You are NOT registering the OUT parameter for 'ref_stp_chq_end_no'.

                     

                    Also your parameter setup shows TWELVE parameters but they use DIFFERENT numbering than the statement:

                    procParams [1] = new FCRJProcedureParameter (1

                          , "p_field_list_new.COD_ACCT_NO"

                          , ""

                          , ""

                          , Types.VARCHAR

                          , true);

                    . . .

                      procParams [12] = new FCRJProcedureParameter (12

                          , ""

                          , ""

                          , ""

                          , Types.INTEGER

                          , true);

                    Presumably #12 is actually for the function return value which is parameter #1 in the statement.