CLOB variable gives "ORA-06502: PL/SQL: numeric or value error"

I'm creating a dynamic report on oracle apex.

I've a very huge query and as per requirement any portion of the query can be returned.

I'm using PL/SQL Function Body returning SQL query feature of IR.

For that, I'm simply returning the output. Return Function_name(<Portion>);

But I'm getting error ORA-06502: PL/SQL: numeric or value error for only FULL_Query. Other portions works fine.

The code is like below:

Create Function Function_Name (Portion Varchar2) Return CLOB IS
    Query_1     CLOB;
    Query_2     CLOB;
    Query_3     CLOB;
    CONDITIONS  CLOB;
    FULL_QUERY CLOB := ' ';
BEGIN

    Query_1 := 'Has query 1';
    Query_2 := 'Has query 2';
    Query_3 := 'Has query 3';
    
    CONDITIONS := 'Has Some conditions';

    Query_1 := Query_1 || ' ' || CONDITIONS;
    Query_2 := Query_2 || ' ' || CONDITIONS;
    Query_3 := Query_3 || ' ' || CONDITIONS;
    
    FULL_QUERY := Query_1 ||  Query_2 ||  Query_3; -- Gives the same error
    
    --DBMS_OUTPUT.PUT_LINE(length(Query_1));      -- 17k
    --DBMS_OUTPUT.PUT_LINE(length(Query_2));      -- 19k
    --DBMS_OUTPUT.PUT_LINE(length(Query_3));      -- 19k
    --DBMS_OUTPUT.PUT_LINE(length(FINAL_QUERY));  -- 56k
    
    If Portion = 1 then
        Return Query_1;
    Elsif Portion = 2 then
        Return Query_2;
    Elsif Portion = 3 then
        Return Query_3;
    Elsif Portion is NULL then
        Return FULL_Query;
    End if;
END;

Only when I try to get FULL_QUERY, it gives me ORA-06502: PL/SQL: numeric or value error. All other portions are fine.

I tried CONCAT() and DBMS_LOB.APPEND instead of normal pipe concatenations. But still, FULL_QUERY is giving me same error.

--------- With CONCAT ---------
FULL_QUERY := CONCAT( CONCAT(Query_1, Query_2), Query_3); -- Gives the same error
    
    
--------- With APPEND ---------
DBMS_LOB.APPEND(FULL_QUERY, Query_1);
DBMS_LOB.APPEND(FULL_QUERY, Query_2);     
DBMS_LOB.APPEND(FULL_QUERY, Query_3);   -- Gives the same error

Any idea how to achieve this?

It seems, Oracle apex itself won't allow to load any query having character length more than 32k into IR. Even when we are using PL/SQL Function Body returning SQL query.