Unexpected ORA-22814 Using CAST(COLLECT ... when max_string_size=EXTENDED
(Doc ID 2137565.1)
Last updated on JANUARY 08, 2025
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterOracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A 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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |