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

Question

I got the following error when running my ColdFusion 2016 application: The error says:

[Macromedia][Oracle JDBC Driver][Oracle]ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "PROC_CHECKDATA", line 1064 ORA-06512: at line 1

It's has something to do with calling a stored procedure and running it. This codes works for years when I ran it in ColdFusion 8 but once I moved to ColdFusion 2016 it errors out. I don't quite understand what is this error saying. I appreciate any help I can get. Thank you so much.

Here is my procedure call:

<cfstoredproc procedure="PROC_CHECKDATA" datasource="#application.DSN#">              
    <cfprocparam type="in" cfsqltype="CF_SQL_CHAR" DBVARNAME="ins" value="#url.ins#" MAXLENGTH="2">           
    <cfprocparam type="in" cfsqltype="CF_SQL_CHAR" DBVARNAME="ly" value="#url.ly#" MAXLENGTH="4">           
    <cfprocparam type="inout" cfsqltype="CF_SQL_VARCHAR" DBVARNAME="summary" variable="summary" value="">          
    <cfprocparam type="inout" cfsqltype="CF_SQL_VARCHAR" DBVARNAME="continue" variable="continue" value="">

    <cfprocresult name="errors" resultset="1">
</cfstoredproc>

And this is the stored procedure (in Oracle 11g):

 create or replace PROCEDURE proc_checkparentdata (
   v_institution IN     CHAR DEFAULT NULL,
   v_load_year   IN     CHAR DEFAULT NULL,
   v_summary        OUT VARCHAR2, /* DEFAULT ' '*/                              
   v_continue       OUT VARCHAR2, /* DEFAULT ' '*/                               
   cv_1             OUT SYS_REFCURSOR)
   AS
     v_rowcount   NUMBER (10, 0);
      v_errorcount NUMBER (5, 0);
   BEGIN
   -- clear comment
    UPDATE um_parent_temp
    SET comments = ' '
    WHERE load_year = v_load_year AND institution = v_institution;

   -- clear status
    UPDATE um_parent_temp
    SET status = ' '
    WHERE load_year = v_load_year AND institution = v_institution;

   -- campus code
   UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Campus code: '
       || institution
    WHERE institution NOT IN ('BC', 'CP', 'ES', 'FS', 'SU', 'TU', 'UC')
    AND load_year = v_load_year
    AND institution = v_institution;

    /* parent 1 */
    -- firstname
    UPDATE um_parent_temp
     SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Parent firstname1 is missing'
     WHERE NVL (trim(parent_fn1), ' ') = ' '
     AND load_year = v_load_year
     AND institution = v_institution;

    -- lastname
    UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Parent_lastname1 is missing'
     WHERE NVL (trim(parent_ln1), ' ') = ' '
     AND load_year = v_load_year
     AND institution = v_institution;

   -- lastname1 too short
   UPDATE um_parent_temp
   SET comments    =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Parent_lastname1 is too short'
   WHERE Length(trim(parent_ln1)) = 1
   AND load_year = v_load_year
   AND institution = v_institution;     

   -- maximum label name = 60; includes three spaces
   UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Parent mailname1 is over 60 characters'
    WHERE lengthc (nvl(trim(parent_prefix1),'') ||   
    nvl(trim(parent_fn1),'') || nvl(trim(parent_mn1),'') ||   
    nvl(trim(parent_ln1),'')) > 37
    AND load_year = v_load_year
    AND institution = v_institution;

    -- prefix
    UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Prefix1: '
       || parent_prefix1
    WHERE NVL (trim(parent_prefix1), ' ') = ' '
    OR (NVL (trim(parent_prefix1), ' ') <> ' '
    AND parent_prefix1 NOT IN
        (SELECT gl_prefixes.campprefix FROM gl_prefixes)
    AND load_year = v_load_year
    AND institution = v_institution);

    -- suffixPers
    UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Personal suffix1: '
       || parent_suffix_pers1
    WHERE NVL (trim(parent_suffix_pers1), ' ') <> ' '
    AND parent_suffix_pers1 NOT IN
       (SELECT gl_suffixes.campsuffix FROM gl_suffixes)
    AND load_year = v_load_year
    AND institution = v_institution;

   -- suffixProf
    UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Profession suffix1: '
       || parent_suffix_prof1
     WHERE NVL (trim(parent_suffix_prof1), ' ') <> ' '
     AND parent_suffix_prof1 NOT IN
       (SELECT gl_suffixes.campsuffix FROM gl_suffixes)
      AND load_year = v_load_year
      AND institution = v_institution;

    -- race
    UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Race1: '
       || race1
    WHERE NVL (trim(race1), ' ') <> ' '
    AND race1 NOT IN (SELECT campcode
                     FROM gl_races
                    WHERE campuscode = v_institution)
    AND load_year = v_load_year
    AND institution = v_institution;

    -- sex
    UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Sex code1: '
       || sex1
   WHERE NVL (trim(sex1), ' ') NOT IN ('M', 'F')
   AND load_year = v_load_year
   AND institution = v_institution;

   UPDATE um_parent_temp
   SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Mismatching gender1 and prefix1: '
       || parent_prefix1
       || ' <---> '
       || sex1
    WHERE ( (sex1 = 'M'
    AND trim(parent_prefix1) IN ('Mrs.', 'Mrs', 'Miss.', 'Miss', 'Ms.', 
                                                               'Ms'))
    OR (trim(sex1) = 'F' AND trim(parent_prefix1) IN ('Mr.', 'Mr')))
    AND load_year = v_load_year
    AND institution = v_institution;

   -- address
    UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Preferred address flag1: '
       || pref_addr1
       || '<br>'
       || 'note: must also have unlisted flag set for preferred addr'
   WHERE trim(pref_addr1) NOT IN ('H', 'B')
   AND load_year = v_load_year
   AND institution = v_institution;

    UPDATE um_parent_temp
     SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Home address1_1 is empty'
     WHERE pref_addr1 = 'H'
     AND NVL (trim(home_addr1_1), ' ') = ' '
     AND load_year = v_load_year
     AND institution = v_institution;

     UPDATE um_parent_temp
     SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Business address1 is empty'
   WHERE pref_addr1 = 'B'
   AND NVL (trim(busn_addr1_1), ' ') = ' '
   AND load_year = v_load_year
   AND institution = v_institution;

   -- city
   UPDATE um_parent_temp
   SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Home city1 is empty'
   WHERE NVL (trim(home_addr1_1), ' ') <> ' '
   AND NVL (trim(home_city1), ' ') = ' '
   AND load_year = v_load_year
   AND institution = v_institution;

   UPDATE um_parent_temp
   SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Business city1 is empty'
   WHERE NVL (trim(busn_addr1_1), ' ') <> ' '
   AND NVL (trim(busn_city1), ' ') = ' '
   AND load_year = v_load_year
   AND institution = v_institution;

   -- state
   UPDATE um_parent_temp
   SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Home state code1: '
       || home_state1
   WHERE NVL (trim(home_addr1_1), ' ') <> ' '
   AND trim(home_country1) IN ('US', 'USA', ' ')
   AND trim(home_state1) NOT IN (SELECT tms_states.state_code FROM 
   tms_states)
   AND load_year = v_load_year
   AND institution = v_institution;

   UPDATE um_parent_temp
   SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Business state code1: '
       || busn_state1
   WHERE NVL (trim(busn_addr1_1), ' ') <> ' '
   AND trim (busn_country1) IN ('US', 'USA', ' ')
   AND busn_state1 NOT IN (SELECT tms_states.state_code FROM tms_states)
   AND load_year = v_load_year
   AND institution = v_institution;

    -- zip
    UPDATE um_parent_temp
     SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
        || 'Invalid home zip code1: '
        || home_zip1
    WHERE trim(home_country1) IN ('US', 'USA', ' ')
    AND INSTR (home_zip1, '-') > 0
    AND load_year = v_load_year
    AND institution = v_institution;

    UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Invalid business zip code1: '
       || busn_zip1
    WHERE trim (busn_country1) IN ('US', 'USA', ' ')
    AND INSTR (busn_zip1, '-') > 0
    AND load_year = v_load_year
    AND institution = v_institution;

    -- country
    UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Home country code1: '
       || home_country1
    WHERE NVL (trim(home_country1), ' ') <> ' '
    AND NVL (trim(home_country1), ' ') NOT IN (select campcountry from    
    gl_countries
    WHERE gradloadcode = v_institution)
    AND load_year = v_load_year
    AND institution = v_institution;

    UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Business country code1: '
       || busn_country1
     WHERE NVL (trim(busn_country1), ' ') <> ' '
     AND busn_country1 NOT IN (select campcountry from gl_countries
                            WHERE gradloadcode = v_institution)
     AND load_year = v_load_year
     AND institution = v_institution;

     -- unlisted flag
     UPDATE um_parent_temp
     SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Invalid home unlisted flag 1'
     WHERE pref_addr1 = 'H'
     AND home_unlisted_flag1 NOT IN ('N', 'Y')
     AND load_year = v_load_year
     AND institution = v_institution;

     UPDATE um_parent_temp
     SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Invalid business unlisted flag 1'
     WHERE pref_addr1 = 'B'
     AND busn_unlisted_flag1 NOT IN ('N', 'Y')
     AND load_year = v_load_year
     AND institution = v_institution;

    /* parent 2 */
    -- firstname
    UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Parent firstname2 is missing'
    WHERE NVL (trim(parent_fn2), ' ') = ' '
    AND NVL (trim(parent_ln2), ' ') <> ' '
    AND load_year = v_load_year
    AND institution = v_institution;

    -- lastname
    UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Parent lastname2 is missing'
    WHERE NVL (trim(parent_ln2), ' ') = ' '
    AND NVL (trim(parent_fn2), ' ') <> ' '
    AND load_year = v_load_year
    AND institution = v_institution;

    -- lastname2 too short
    UPDATE um_parent_temp
    SET comments    =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Parent_lastname2 is too short'
    WHERE Length(trim(parent_ln2)) = 1
    AND NVL (trim(parent_fn2), ' ') <> ' '
    AND load_year = v_load_year
    AND institution = v_institution;     

    -- maximum label name = 460; includes three spaces
    UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Parent mailname2 is over 60 characters'
     WHERE NVL (trim(parent_fn2), ' ') <> ' '
     AND lengthc (parent_prefix2 || parent_fn2 || parent_mn2 || 
     parent_ln2) > 57
     AND load_year = v_load_year
     AND institution = v_institution;

     -- prefix
     UPDATE um_parent_temp
     SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Prefix2: '
       || parent_prefix2
    WHERE NVL (trim(parent_prefix2), ' ') <> ' '
    AND parent_prefix2 NOT IN
       (SELECT gl_prefixes.campprefix FROM gl_prefixes)
    AND load_year = v_load_year
    AND institution = v_institution;

    -- suffixPers
    UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Personal suffix2: '
       || parent_suffix_pers2
    WHERE NVL (trim(parent_suffix_pers2), ' ') <> ' '
    AND parent_suffix_pers2 NOT IN
       (SELECT gl_suffixes.campsuffix FROM gl_suffixes)
    AND load_year = v_load_year
    AND institution = v_institution;

     -- suffixProf
     UPDATE um_parent_temp
     SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Profession suffix2: '
       || parent_suffix_prof2
      WHERE NVL (trim(parent_suffix_prof2), ' ') <> ' '
      AND parent_suffix_prof2 NOT IN
       (SELECT gl_suffixes.campsuffix FROM gl_suffixes)
      AND load_year = v_load_year
      AND institution = v_institution;

     -- race
     UPDATE um_parent_temp
      SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Race2: '
       || race2
     WHERE NVL (trim(race2), ' ') <> ' '
     AND race2 NOT IN (SELECT campcode
                     FROM gl_races
                    WHERE campuscode = v_institution)
     AND load_year = v_load_year
     AND institution = v_institution;

     -- sex
     UPDATE um_parent_temp
     SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Sex code2: '
       || sex2
    WHERE NVL (trim(parent_fn2), ' ') <> ' '
    AND sex2 NOT IN ('M', 'F')
    AND load_year = v_load_year
    AND institution = v_institution;

    UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Missmatching gender2 and prefix2: '
       || parent_prefix2
       || ' <---> '
       || sex2
    WHERE ( (sex2 = 'M'
    AND trim(parent_prefix2) IN ('Mrs.', 'Mrs', 'Miss.', 'Miss', 'Ms.',
                                                              'Ms'))
      OR (sex2 = 'F' AND trim(parent_prefix2) IN ('Mr.', 'Mr')))
      AND load_year = v_load_year
      AND institution = v_institution;

      -- address
      UPDATE um_parent_temp
      SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Preferred address flag2: '
       || pref_addr2
       || '<br>'
       || 'note: must also have unlisted flag set for preferred addr'
     WHERE NVL (trim(parent_fn2), ' ') <> ' '
     AND pref_addr2 NOT IN ('H', 'B')
     AND load_year = v_load_year
     AND institution = v_institution;

    UPDATE um_parent_temp
    SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Home address2_1 is empty'
     WHERE NVL (trim(parent_fn2), ' ') <> ' '
     AND pref_addr2 = 'H'
     AND NVL (trim(home_addr2_1), ' ') = ' '
     AND load_year = v_load_year
     AND institution = v_institution;

     UPDATE um_parent_temp
     SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Business address2 is empty'
     WHERE NVL (trim(parent_fn2), ' ') <> ' '
     AND pref_addr2 = 'B'
     AND NVL (trim(busn_addr2_1), ' ') = ' '
     AND load_year = v_load_year
     AND institution = v_institution;

     -- city
     UPDATE um_parent_temp
     SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Home city2 is empty'
     WHERE NVL (trim(home_addr2_1), ' ') <> ' '
    AND NVL (trim(home_city2), ' ') = ' '
     AND load_year = v_load_year
     AND institution = v_institution;

     UPDATE um_parent_temp
     SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Business city2 is empty'
      WHERE NVL (trim(busn_addr2_1), ' ') <> ' '
      AND NVL (trim(busn_city2), ' ') = ' '
      AND load_year = v_load_year
      AND institution = v_institution;

      -- state
      UPDATE um_parent_temp
      SET comments      =
          (CASE comments WHEN ' ' THEN ' ' ELSE comments || '<br>' END)
       || 'Home state 2: '
       || home_state2
    WHERE NVL (trim(home_addr2_1), ' ') <> ' '
    AND trim(home_country2) IN ('US', 'USA', ' ')
    AND trim(home_state2) NOT IN (SELECT tms_states.state_code FROM     
    tms_states)
    AND load_year = v_load_year
    AND institution = v_institution;

I cannot enter any more code but the rest of it is similar

I ran the procedure in Oracle and the procedure ran just fine! But calling it through ColdFusion generates the ORA-06502: PL/SQL: numeric or value error: character string buffer too small. It points to v_summary := 'Institution: ' || v_institution || ' Load Code: ' || v_load_year || ' Total records checked: ' || TO_CHAR (v_rowcount, '99999') || ' Total records with errors: ' || TO_CHAR (v_errorcount, '99999'); If I comment out this part, the proc runs. — user1557856

user1557856 user1557856 · Accepted Answer · 2017-10-04T20:49:52

ColdFusion 2016 cannot use the DBVARNAME attribute of the <cfstoredproc> tag.

promoted from the comments

I ran the procedure in Oracle and the procedure ran just fine! But calling it through ColdFusion generates the error ORA-06502: PL/SQL: numeric or value error: character string buffer too small. It points to this line:

v_summary := '<b>Institution:</b> ' || 
             v_institution || 
             '<br><b>Load Code:</b> ' ||
             v_load_year || 
             '<br><b>Total records checked:</b> ' || 
             TO_CHAR (v_rowcount, '99999') || 
             '<br><b>Total records with errors:</b> ' || 
             TO_CHAR (v_errorcount, '99999');

If I comment out this part, the procedure runs.

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

1 Answers