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.

How to Configure 32K VARHAR2 Data Type in 12c

Configure 32k VARCHAR2, NVARCHAR2 and RAW data type by setting max_string_size=extended, and be aware this parameter can no long be able to change back, like setting max_string_size=standard.

Overview

In 12c database, you can define a maximum size of 32767  bytes for VARCHAR2, NVARCHAR2 and RAW data type column.

Before 12c database, the maximum size was 4000 bytes for VARHAR2 and NVARHAR2 and 2000 bytes for RAW data type.

The declared column length for VARHAR2,NVARHAR2 and RAW data type decides how the column is stored internally in database.

  • If VARCHAR2 and NVARCHAR2 are declared with column length of 4000 bytes or less, and RAW data type column is declared with length 2000 bytes or less, the data is store in-line.
  • If VARCHAR2 and NVARCHAR2 are declared with column length of greater than 4000 bytes, and RAW data type column is declared with length of greater than 2000 bytes, the data is store out-of-line. The column is called “extended character data type column”.

Configure Database for Extended Data Type

  • Before configuring extended data type feature for database.
$ sqlplus / as sysdba
SQL*Plus:Release 12.1.0.2.0 Production on Mon Nov 17 21:54:47 2014Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> create table test_table(longvar varchar2(32767));
create table long_varchar(id number,vc varchar2(32767))
 *
ERROR at line 1:
ORA-00910: specified length too long for its datatype
SQL> alter system set max_string_size=extended; system set max_string_size=extended * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration
  • Shutdown Database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
  • Start database in upgrade mode
SQL> startup upgrade
ORACLE instance started.
TotalSystem Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 675283048 bytes
Database Buffers 390070272 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
  • Change MAX_STRING_SIZE setting
SQL> alter system set max_string_size=extended;
System altered.
  • Run utl32k.sql script
SQL> @ $ORACLE_HOME/rdbms/admin/utl32k.sql
Session altered.
DOC>##############################################################
DOC>##############################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database has not been opened for UPGRADE.
DOC>
DOC> Perform a "SHUTDOWN ABORT" and
DOC> restart using UPGRADE.
DOC>##############################################################
DOC>##############################################################
DOC>#
no rows selected
DOC>##############################################################
DOC>##############################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database does not have compatible >= 12.0.0
DOC>
DOC> Set compatible >= 12.0.0 and retry.
DOC>##############################################################
DOC>##############################################################DOC>#
PL/SQL procedure successfully completed.
Session altered.
1393 rows updated.
Commit complete.
System altered.
PL/SQL procedure successfully completed.
Commit complete.
System altered.
Session altered.
PL/SQL procedure successfully completed.
No errors.
Session altered.
PL/SQL procedure successfully completed.
Commit complete.
Package altered.
Package altered.
SQL>
  • Shutdown and Startup database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 675283048 bytes
Database Buffers 390070272 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
  • Check parameter setting and create table with extended data type column
SQL> show parameter max_string
NAME TYPE VALUE ---------------- ------- -------- max_string_size string EXTENDED SQL> create table test_table(longvar varchar2(32767)); Table created.

Please note you can not change parameter MAX_STRING_SIZE from EXTENDED  to STANDARD.