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

Question

I have an AJAX callback that retrieves a CLOB value from a table.

DECLARE
    L_CLOB CLOB;
BEGIN
    SELECT LOG 
    INTO L_CLOB 
    FROM TAB1
    WHERE COL1 = apex_application.g_x01;

    htp.p(L_CLOB);
END;

However, when the CLOB is too large, it returns

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

Why is this happening?

None of the HTP subprograms have CLOB argument, so you're limited to the maximum size of a varchar2; if the CLOB is larger than 32k it will give that error. You'll have to decide how you want to handle that - probably by breaking the value up into multiple lines? — Alex Poole

Dominik Dominik · Accepted Answer · 2018-09-12T09:15:18

If you use the APEX_JSON Package you can return CLOBS. The htp.p Package does not support CLOB output, if you want to use that you have to split the CLOB up into smaller Chunks of VARCHAR2.

Using the APEX_JSON Package is much more convenient, you can just pass a CLOB as a Parameter to the write procedure:

apex_json.open_object;  
apex_json.write('mydata', MY_CLOB);  
apex_json.close_object;

If you put that into an AJAX Callback Procedure on a Page and then use this Javascript to call it:

 apex.server.process(
    'MY_AJAX_PROCEDURE',   // Process or AJAX Callback name
    {}, //No Parameters
    {
      success: function (pData) {        
        console.log(pData); //The Data will be logged into the console
      }
    }
  );

You will see your CLOB data in the console. You can also do everything else with it.

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

1 Answers