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?

Alex Poole · Answer 1 · 2023-05-16T07:23:41.763

1

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.

edited May 16 '23 at 07:23

answered May 16 '23 at 07:17

Alex Poole

183,384
11
179
318

Thanks @Alex for your response, But I've just shown the structure of my query and the problem. I'm trying to get and execute `final_query` on oracle apex. I'm using `dbms_output.put_line` to just to check the result and issue. But even without using that I'm getting the same error. I've just updated the question. – Anand May 16 '23 at 07:54
I would imagine APEX isn't able to deal with a CLOB for the query either; whatever is handling the `return` is expecting VARCHAR2? (I don't use APEX, but the error implies the CLOB is being implicitly converted somewhere, and failing...) – Alex Poole May 16 '23 at 09:06

score 1 · Accepted Answer · answered May 21 '23 at 06:42

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.

answered May 21 '23 at 06:42

Anand

101
13

score 0 · Answer 3 · answered May 16 '23 at 06:42

0

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.

answered May 16 '23 at 06:42

Littlefoot

131,892
15
35
57

Hi @Littlefoot. Yes, I'm trying to use a functionality of oracle apex, Function body returning sql query. And I'm using `DBMS_OUTPUT.PUT_LINE` to just to check my final query. But on apex I'm getting error `ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275`. Also I tried enable(1000000) but still I'm getting error `ORA-06502: PL/SQL: numeric or value error` on SQL Developer – Anand May 16 '23 at 07:19
Don't use DBMS_OUTPUT.PUT_LINE on Apex page, it won't display anything anyway. Instead, create a **text area** item and put FINAL_QUERY in there so that you could see what it contains. – Littlefoot May 16 '23 at 07:23
I'm calling `RETURN FUNCTION_NAME;` on Apex. I've also tried with `Text Area`. But again I got `ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275` – Anand May 16 '23 at 07:38
Now I'm getting just `ORA-06502: PL/SQL: numeric or value error` on Apex as well. – Anand May 16 '23 at 08:06
Hm, maybe that's just too large to be displayed in any item, then. What happens if you don't display it (the query), but actually run it as a page region source? Does it work? Probably not, because of the same error. In that case, maybe you'll have to use current function as a source for another (much shorter!) function which *feeds* Apex with the result retrieved by FUNCTION_NAME. – Littlefoot May 16 '23 at 09:40

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

3 Answers3