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

Question

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?

Answer 1

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.

Answer 2

I presume you won't be actually displaying FINAL_QUERY to the screen, but execute it.

Because, I'd say that you exceeded limit of what DBMS_OUTPUT.PUT_LINE can display.

Try to enlarge it to max allowed value (1 million):

exec dbms_output.enable(1000000);

and then run your code. But, once again, if you won't display it once you've done with that procedure, it won't really matter.

Answer 3

The put_line() procedure takes a VARCHAR2 argument.

When you pass a CLOB it has to be implicitly converted to that data type. When the CLOB value is small that's fine, but when you exceed the 32k size limit for the PL/SQL VARCHAR2 data type the conversion fails with that error.

You can use the substr funtion to display the first 32k:

DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(FINAL_QUERY, 32767, 1));

or more simply using the default limits:

DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(FINAL_QUERY));

bearing in mind the size limits are in bytes not characters (so if you have multibyte characters you will need a shorter substring).

If you really need to see the whole string then you will need to split into several smaller chunks, which is fairly easy to do if you can split on line breaks - there are plenty of examples of that process around - like this one.