Result of string concatenation is too long when using CLOB in Oracle 12c

Question

I am getting a "result of string concatenation is too long" error when using CLOB fields. The result field is

varSQLConcat CLOB;

The concatenation statement is

varSQLConcat := CONCAT(TO_CLOB(varSel_CREATION), TO_CLOB(varWhere_New));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(' AND SY_INPUT_UID_NEW.SUBMISSION_TYPE = '));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB('''CREATION'''));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(' UNION '));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(varSel_SUBMISSION));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(varWhere_New));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(' AND (SY_INPUT_UID_NEW.SUBMISSION_TYPE = '));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB('''SUBMISSION'''));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB('OR SY_INPUT_UID_NEW.SUBMISSION_TYPE = '));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB('''NEW_W_REG_PARENT'')'));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(' UNION '));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(varSel_ROLLOVER));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(varWhere_Rollover));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(' UNION '));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(varSel_EMBED));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(varWhere_Embed));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(' UNION '));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(varSel_LIFECYCLE));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(varWhere_Lifecycle));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(' UNION '));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(varSel_MARK));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(varWhere_Mark));
   varSQLConcat := CONCAT(varSQLConcat, TO_CLOB(varOrder_By));

The error occurs on the following statement:

OPEN cRecord FOR varSQLConcat; 

This is under Oracle 12c.

Answer 1

I don't have the real solution but this could be a workaround:

DECALRE

    cmd DBMS_SQL.VARCHAR2A;
    cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
    res INTEGER;
    cRecord := SYS_REFCURSOR;

BEGIN
    cmd(1) := varSel_CREATION; -- each line is limited by 32767 characters!
    cmd(cmd.LAST+1) := varWhere_New;
    cmd(cmd.LAST+1) := 'AND SY_INPUT_UID_NEW.SUBMISSION_TYPE = ';
    cmd(cmd.LAST+1) := '''CREATION'''; 
    ...


    DBMS_SQL.PARSE(cur, cmd, cmd.FIRST, cmd.LAST, TRUE, DBMS_SQL.NATIVE); 
    res := DBMS_SQL.EXECUTE(cur);   
    cRecord := DBMS_SQL.TO_REFCURSOR(cur);

END;