title: Getting error ORA-06502 and ORA-06512 in Oracle PL/SQL
body: <p>I have written a procedure which selects some data and inserts into a table. Code was compiled successfully. However when I pass data to execute the procedure I am getting error like the following:</p>
  
  <pre><code>Error report -
  ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  ORA-06512: at "CUSTOM.BPUBPF", line 51
  ORA-06512: at line 7
  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.
  </code></pre>
  
  <p>The cursor query where it is failing is:</p>
  
  <pre><code>    CURSOR cur_BPubpfRecordInsert1 (templateId VARCHAR2,userId VARCHAR2,moduleId VARCHAR2, parentFileName VARCHAR2,seqNextVal VARCHAR2,contextBankId VARCHAR2,
   fileType varchar2,fileChkSum varchar2, msdStat char) IS
     SELECT MSD.MSG_SRL_NUM,
      FHD.HDR_SRL_NUM,CHECK_SUM,nvl(SUBSTR(MSG_DATA,INSTR(MSG_DATA, '&lt;PayerAcctType&gt;') + LENGTH('&lt;PayerAcctType&gt;'),
      INSTR(MSG_DATA, '&lt;/PayerAcctType&gt;') - (INSTR(MSG_DATA, '&lt;PayerAcctType&gt;') + LENGTH('&lt;PayerAcctType&gt;'))),' ')
      AS ACCOUNT_TYPE, nvl(SUBSTR(MSG_DATA,INSTR(MSG_DATA, '&lt;PayerAcctNum&gt;') + LENGTH('&lt;PayerAcctNum&gt;'),
      INSTR(MSG_DATA, '&lt;/PayerAcctNum&gt;') - (INSTR(MSG_DATA, '&lt;PayerAcctNum&gt;') + LENGTH('&lt;PayerAcctNum&gt;'))),' ')
      AS ENTITYID, ' ' BILLERSRVID,
      nvl(SUBSTR(MSG_DATA,INSTR(MSG_DATA, '&lt;PayerID&gt;') + LENGTH('&lt;PayerID&gt;'),
      INSTR(MSG_DATA, '&lt;/PayerID&gt;') - (INSTR(MSG_DATA, '&lt;PayerID&gt;') + LENGTH('&lt;PayerID&gt;'))), ' ') AS docNum,
      MSD.STATUS, FHD.STATUS FROM TBAADM.GD_FHD FHD,
      TBAADM.GD_MSD MSD WHERE MSD.BATCH_HDR_SRL_NUM = FHD.HDR_SRL_NUM AND
      MSD.FROM_TMPL_ID = FHD.FROM_TMPL_ID AND MSD.RCRE_USER_ID = FHD.RCRE_USER_ID
      AND MSD.MODULE_ID = FHD.MODULE_ID AND IN_OUT_FILE_NAME = parentFileName
      AND MSD.RCRE_USER_ID = userId AND MSD.FROM_TMPL_ID=templateId AND MSD.MODULE_ID = moduleId
      AND FHD.FILE_TYPE = fileType AND FHD.CHECK_SUM = fileChkSum  AND MSD.STATUS = msdStat;
  </code></pre>
  
  <p>The line where it is failing is:</p>
  
  <pre><code>  MSD.FROM_TMPL_ID = FHD.FROM_TMPL_ID AND MSD.RCRE_USER_ID = FHD.RCRE_USER_ID
  </code></pre>
  
  <p>The select query returns the following:</p>
  
  <pre><code>MSG_SRL_NUM     HDR_SRL_NUM     CHECK_SUM                                   ACCOUNT_TYPE                                                                        ENTITYID                                                                            BILLERSRVID DOCNUM                                                                            STATUS    STATUS
  47699           B910000007YH    1078478272                                                                                                                                                                                                                                                                                                R         R                   
  47700           B910000007YH    1078478272                                                                                                                                                                                                                                                                                                 R        R                   
  47701           B910000007YH    1078478272                                                                                                                                                                                                                                                                                              R       R                   
  47702           B910000007YH    1078478272                                                                                                                                                                                                                                                                                               R      R                   
  47703           B910000007YH    1078478272                                                                                                                                                                                                                                                                                                R         R                   
  47704           B910000007YH    1078478272                                
  </code></pre>
  
  <p>                                                        </p>
  
  <p>The variable I declared to get the values from the cursor is:</p>
  
  <pre><code>TYPE RecTyp IS RECORD (
  lv_msdSrlNum                    varchar2(12) default null,
  lv_fhdSrlNum                    varchar2(12) default null,
  lv_chkSum                       VARCHAR2(40) default null,
  lv_acctType                     varchar2(5)  default null,
  lv_acctNo                       varchar2(40) default null,
  lv_billerSrvId                  varchar2(35) default null,
  lv_docId                        varchar2(20) default null,
  lv_msdStatus                    char(1) default null,
  lv_fhdStatus                    char(1)  default null
  );
  </code></pre>
  
  <p>I am not able to understand why my code is failing in that particular line.</p>