Unexpected ORA-22814 Using CAST(COLLECT ... when max_string_size=EXTENDED (Doc ID 2137565.1)

Last updated on AUGUST 24, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

This is the error being returned:

SQL> SELECT CAST(COLLECT('Line #' || h.row_number || ': ' || h.header_row)
  over() AS varchar2_tab) AS supported_header_rows_tab
  FROM source_file_headers h
 WHERE h.staging_table_name = 'SB340_ACT'
  2 3 4 5 /
SELECT CAST(COLLECT('Line #' || h.row_number || ': ' || h.header_row)
  *
ERROR at line 1:
ORA-22814: attribute or element value is larger than specified in type

VARCHAR2_TAB metadata is:
  CREATE OR REPLACE EDITIONABLE TYPE "DATABASE_BUILD"."VARCHAR2_TAB" as table of varchar2(4000)

However, the values in the table being selected does not exceed this max length.

SQL> select length('Line #')+length(row_number)+length(':')+length(header_row) as length, lengthb('Line #')+lengthb(row_number)+lengthb(':')+lengthb(header_row) as length_bytes from source_file_headers where staging_table_name='SB340_ACT'
  2 /

  LENGTH LENGTH_BYTES
---------- ------------
  466 466
  466 466
  466 466

This problem started to occur after performing change to set MAX_STRING_SIZE=EXTENDED, which also involves running utl32k.sql migration script. 

Changes

max_string_size is set to EXTENDED

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms