Skip to Main Content

Oracle Database Express Edition (XE)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PLSQL ORA-06502: PL/SQL: numeric or value error

Anand JagtapMay 16 2023 — edited May 18 2023

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?

Comments
Post Details
Added on May 16 2023
4 comments
1,132 views