Update a BLOB column with data of length 80,000 chars in Oracle 19c

We have a requirement to update a BLOB column (which stores JSON data and has a JSON constraint) enabled. The issue is with the length of the data that the BLOB data needs to be updated with. It is over 80,000 characters long.

  CREATE TABLE GLOBAL_TRAIL
(
  CODE                          VARCHAR2(32 BYTE) NOT NULL,
  json_column        BLOB
);

ALTER TABLE GLOBAL_TRAIL ADD  CONSTRAINT CNS_JS
  CHECK (json_column IS JSON (STRICT))
  ENABLE VALIDATE;


UPDATE GLOBAL_TRAIL
   SET json_column = 
          utl_i18n.string_to_raw( '....................80,000 character long text................', 'AL32UTF8' )
where code = '798798798';

Error at line 1 ORA-01704: string literal too long

And then I tried to find the options available online.

Error : ORA-01704: string literal too long

I have tried the alternatives provided in multiple pages by chunking them into batches and concatenating them together but I'm getting the below error:

ORA-01489: result of string concatenation is too long ORA-06512: at line 11

Example: chunk1 and chunk 2 are 30k long, chunk 3 is 20k long.

DECLARE
  chunk1 blob; chunk2 blob; chunk3 blob;
BEGIN
  
 chunk1 :=  utl_i18n.string_to_raw( '{"tementeations":[{"tetestReference":{"tetestnotif":"AdHoctestnotif","testocknotif":"AdHoc934rentnotif"},"nesteationredframe":"202445054501","testoc":"Trail"}},{"accumulationpinkno":.............................{"no":"eATIONGreySTEP"},"accumulationno":{"no":"subjectWages"},"accumulatedtrluepinkno":{"no":"chair"},"accumulatedtrlue":{"chair":090,"934trainno":"Trail"}},{"accumulationpinkno":{"no":"eATIONGreySTEP"},"accumulationno":{"no":"subjectMaximumWages"},', 'AL32UTF8' );
          
     chunk2 :=       utl_i18n.string_to_raw('"accumulatedtrluepinkno":{"no":"chair"},"accumulatedtrlue":{"chair":090,"934trainno":"Trail"}},{"accumulationpinkno":{"no:090,"934trainno":"Trail"}}]}]}],"eationOrder":1,"chemicyPackageVersion":"1909362","eationStatTrailerno":{"no":"SUCCEEDED","effectiveframeTime":"202445044526T16:09:57908Z"},"primaryLivedIneletrtors":[{"eletrtornotif":"eb9aeafa4556e1454e764587e645b2cc4a2368cf","eletrtorLevelno":{"no":"native"}},..................................................{"eletrtornotif":"76a7aa9e45bafc45410a45b0ef45b2535b6990be","eletrtorLevelno":{"no":"tent"}}],"primaryWorkedIneletrtors":', 'AL32UTF8' );
          
     chunk3 :=     utl_i18n.string_to_raw( '[{"eletrtornotif":"eb9aeafa4556e1454e764587e645b2cc4a2368cf","eletrtorLevelno":{"no":"native"}},{"eletrtornotif":"76a7aa9e45bafc45410a45b0ef45b2535b6990be","eletrtorLevelno":{"no":"tent"}}],"tementchair":{"chair":090,"934trainno":"Trail"},"totals":......................................[{"totalno":"TOTALGreyEARNINGS","totaltrl"accumulationstockpinkno":"tesla","accumulationStartframe":"202445044501","accumulationEndframe":"202445064530","accumulatedtrluepinkno":{"no":"c"tementeationnotif":"1ef03e7645cceb45076745813045ffe4e0e14753"}]}', 'AL32UTF8' );

    UPDATE GLOBAL_TRAIL
   SET json_column = 
  chunk1 || chunk2 || chunk3   where code = '798798798';
END;

**ORA4501489: result of string concatenation is too long

Same error for the below approach as well:

 UPDATE GLOBAL_TRAIL
   SET json_column = 
          utl_i18n.string_to_raw( '..............30k long...............', 'AL32UTF8' )
          ||
          utl_i18n.string_to_raw( '..............30k long...............', 'AL32UTF8' )
          ||
          utl_i18n.string_to_raw( '..............20k long...............', 'AL32UTF8' )
 WHERE  code = '798798798';

If i try to update one chunk only, it is throwing the JSON constraint error. [Error] Execution (11: 2): ORA-02290: check constraint (CNS_JS) violated

So all the chunks need to be updated at the same time.

Please suggest the best approach for this issue.

Solution

You can pass in a CLOB (built from smaller strings of up to 4,000 bytes or 32k bytes if you have enabled extended string sizes) and convert it to from a CLOB to a BLOB using this answer:

UPDATE GLOBAL_TRAIL
SET json_column =
      CLOB_TO_BLOB(
        EMPTY_CLOB()
        || 'First 4,000 bytes'
        || 'Second 4,000 bytes'
        || '...'
        || '20th 4,000 bytes'
        || '21st 4,000 bytes (note: bytes, not characters)'
        || LPAD('a', 4000, 'a'),  -- extra to bulk out the string
        NLS_CHARSET_ID('AL32UTF8')
      )
WHERE code = '798798798';

Or, even simpler:

UPDATE GLOBAL_TRAIL
SET json_column = :1
where code = '798798798';

and pass the json_column in as a BLOB via the bind variable (from a client that supports bind variables - which the drivers for most programming languages do).

fiddle