[SOLVED] Running a dynamically generated MERGE INTO statement in a loop throws "ORA-01489: result of string concatenation is too long" error

Running a dynamically generated MERGE INTO statement in a loop throws "ORA-01489: result of string concatenation is too long" error

Running a dynamically generated MERGE INTO statement in a loop throws ORA-01489: result of string concatenation is too long error.

The generated MERGE INTO statement is:

USING SOURCE S
ON (T.PK = S.PK)
WHEN NOT MATCHED THEN INSERT (T.PK, T.NAME, T.DESCRIPTION) 
VALUES (S.PK, S.NAME, S.DESCRIPTION);

SQL is generated and executed in a loop using:

SELECT 
    'MERGE INTO ' || DESTINATION_TABLE_NAME || ' T
    USING ' || SOURCE_TABLE_NAME || ' S
    ON (' || PK_COLS || ')
    WHEN NOT MATCHED THEN INSERT (' || DESTINATION_COLS || ') 
    VALUES (' || SOURCE_COLS || ')'                                                            
INTO QUERYVAR
FROM DUAL;

EXECUTE IMMEDIATE QUERYVAR;

Data in SOURCE table is inserted using:

INSERT INTO SOURCE (PK, NAME, DESCRIPTION) VALUES ('GUID_KEY', 'Name', 'Description');

Tables have this structure:

CREATE TABLE SOURCE 
("PK" VARCHAR2(32 BYTE) DEFAULT (SYS_GUID()) NOT NULL ENABLE,
"NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"DESCRIPTION" VARCHAR2(3999 BYTE) NOT NULL ENABLE, 
CONSTRAINT "PK_PK" PRIMARY KEY ("PK"))

Running MERGE INTO stand-alone, not in a loop throws no error. But error happens only when running in loop. Other MERGE INTO following or preceding this one run just fine. Error is thrown even no data is in Source table, meaning nothing should be inserted. More interestingly if destination table has no data, but Source has, then running statement gives error and data is inserted correctly into Source.

What is the reason and how to cope with the error? Thank you in advance.

Solution

Rewriting sql generation from SELECT 'query' INTO QUERYVAR FROM DUAL; to QUERYVAR := 'query' did the trick.