I found a weird behavior in a stored procedure that works 99% of the time fine for historizing a standardized report into our Oracle Database.

For some reports I get the error ORA-01489: result of string concatenation is too long. The procedure creates a temporary table with all the data and is adding a composite primary hash key and hashed business key.

If I perform only the SELECT Statement without the CREATE TABLE temp_tbl for a report that causes trouble it works just fine. Are there any known limitation of CREATE TABLE ... AS functionality?

sql_cmd varchar2(32767);

BEGIN
sql_cmd:= 'CREATE TABLE temp_tbl AS (
        SELECT 
            ID_00010, ID_00020, ID_00030, ID_00040,  to_timestamp(ID_00050, 'YYYY-MM-DD hh24:mi:ss') as ID_00050, ID_00060, [...],
            to_timestamp("PARSER_TIMESTAMP", 'YYYY-MM-DD hh24:mi:ss') as "PARSER_TIMESTAMP",
            
            CAST(STANDARD_HASH(
            STANDARD_HASH(
         UTL_RAW.CAST_TO_RAW(ID_00010|| ID_00020|| ID_00030|| ID_00040|| ID_00050|| [...])
       || UTL_RAW.CAST_TO_RAW(ID_11040|| ID_12000|| ID_13000|| ID_20000|| [...])
       , 'SHA1')
        ||
     STANDARD_HASH(UTL_RAW.CAST_TO_RAW(ID_20160|| ID_20170|| ID_20180|| ID_20190|| ID_20200|| [...])
       || UTL_RAW.CAST_TO_RAW(ID_20680|| ID_20690|| ID_20700|| ID_20710||  [...])
        , 'SHA1')
        ||   
    STANDARD_HASH(UTL_RAW.CAST_TO_RAW(ID_30780|| ID_30790|| ID_30800|| [...])
       || UTL_RAW.CAST_TO_RAW(ID_30980|| ID_30990|| [...])
       || UTL_RAW.CAST_TO_RAW(ID_31820|| ID_31830|| [...])
       || UTL_RAW.CAST_TO_RAW(ID_32680|| ID_32690|| [...])
       || UTL_RAW.CAST_TO_RAW(ID_50710|| ID_50720|| [...])
    , 'SHA1')
    , 'SHA1') as CHAR(160)) as PK_HASH_KEY,
                CAST(STANDARD_HASH(utl_raw.cast_to_raw(id_20000), 'SHA1') as CHAR(160)) as BK_HASH_KEY
            FROM stg_tbl
            )'

EXECUTE IMMEDIATE sql_cmd;
END;

Additional Info

• The length of the cmd_sql string is 13969 and should not be cause of the problem as it’s static text and works for other reports of the same type.

• Due to the concatenation limitation of 4000 characters I split the PK_HASH_KEY generation into multiple “Sub Hash Keys”. The sub hash keys for a report that results in the aforementioned error are well below the limit:

• Error Message

Error report – ORA-01489: result of string concatenation is too long
ORA-06512: at “DATAENGINEER.LOAD_PROC”, line 38 ORA-06512: at
line 2
01489. 00000 – “result of string concatenation is too long”
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size.

• Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production