OEM 13c Create Software Image Using Fleet Maintenance with “ORA-06502: PL/SQL: numeric or value error: character string buffer too small”

While creating software image by using Fleet Maintenance, it fails with “ORA-06502: PL/SQL: numeric or value error: character string buffer too small”

[oracle@oemnode1 ~]$ emcli db_software_maintenance  \
                     -createSoftwareImage           \
          -input_file="data:/home/oracle/createSoftwareImage.txt"

Check the  emoms.trc :

Given working directory location is : /tmp  Profile Schedule is: 
ExpressionEvaluationException: Below error message was returned 
                       while executing this Computational step.
----------------------------------------------------------
Unable to create component. ORA-06502: PL/SQL: numeric or value 
                       error: character string buffer too small
ORA-06512: at "SYSMAN.EM_GOLD_IMAGE", line 1039
ORA-06512: at "SYSMAN.EM_GOLD_IMAGE", line 1039
ORA-06512: at "SYSMAN.EM_GOLD_IMAGE", line 2314
ORA-06512: at line 1

Also check the SYSMAN.EM_GOLD_IMAGE package code:

LINE TEXT
---- --------------------------------------------------------------
1036 BEGIN
1037 l_text := NULL; 
1038  FOR cur_rec IN (SELECT bugs_fixed FROM mgmt$oh_patch WHERE target_name= p_oh_target_name) LOOP
1039    l_text := l_text || ',' || cur_rec.bugs_fixed;
1040  END LOOP;   
1041  l_text := substr(l_text,2,length(l_text));
1042 END;

Let’s run a query to see how many total characters of column bugs_fixed in table mgmt$oh_patch for source target ORACLE_HOME, here it is OraDB12c_Home1.

SQL> show parameter max_string_size 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
max_string_size                      string      STANDARD


SQL> select sum(length(bugs_fixed)) 
     from sysman.mgmt$oh_patch 
     WHERE target_name ='OraDB12c_Home1';

SUM(LENGTH(BUGS_FIXED))
-----------------------
34315

If the MAX_STRING_SIZE is STANDARD , then the maximum size for VARCHAR2 is 4000 bytes.  That is why variable l_text  in SYSMAN.EM_GOLD_IMAGE package code raises “character string buffer too small” exception.

RESOLUTION

It is a bug, and Oracle support has provided patches in which the  variable  l_text will be defined from varchar2  to clob  to solves this problem.

Download and apply the latest Enterprise Manager DB Plugin Patches.