oracle-apexclob

Oracle Apex Clob display column throwing error for null value


I have an Interactive Grid in Apex where i need to dispaly clob column.

I followed the below link and set up exactly till step 10.

It works fine if there is data. But in case of null it throws PL/SQL numeric error.

How can this be handled?

https://akilramesh.blogspot.com/2021/06/display-and-edit-clob-content-in-oracle.html


Solution

  • Wrap the loop in an IF statement to check that the CLOB is not null:

    DECLARE
      LO_CLOB        CLOB;
      LO_CHUNK_SIZE  NUMBER := 4000;
    BEGIN
      SELECT DESCRIPTION 
      INTO   LO_CLOB 
      FROM   T_EMPLOYEE
      WHERE  EMP_ID = apex_application.g_x01;
    
      IF lo_clob IS NOT NULL THEN
        FOR i IN 0 .. FLOOR(LENGTH(LO_CLOB)/LO_CHUNK_SIZE )
        LOOP
          SYS.HTP.PRN(SUBSTR(LO_CLOB, i * LO_CHUNK_SIZE + 1, LO_CHUNK_SIZE ));
        END LOOP;
      END IF;
    END;
    /
    

    fiddle