1 Reply Latest reply: Apr 25, 2014 8:32 AM by Srini Chavali-Oracle RSS

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

    Rohit Jadhav-Oracle


      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

       

      Oracle11g Client (Onsite) Version :

      Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

       

      Oracle 11G Offshore Version :

      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

       

       

      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