PL/SQL Infinite Loop

Question

My PL/SQL skills are very limited so I apologize if this is a no brainer. Basically, I have a loop that iterates through a clob. The loop has given me problems in the past (infinite loop) when the value of the clob is null. I tried to build some checks into the code for this loop, however my efforts to prevent an infinite loop fail.

Here are the relevant pieces of the code:

        v_offset        NUMBER DEFAULT 1;
        v_response      CLOB;

        SELECT VALUE
         INTO v_response
         FROM json_cache
        WHERE json_key = 'EMPLOYEES';

       --infinite loop occurs when v_response = ''
       LOOP
          EXIT WHEN v_offset > DBMS_LOB.getlength (v_response) 
              or DBMS_LOB.getlength (v_response) = 0 
              or v_offset = 400000;
          HTP.prn (DBMS_LOB.SUBSTR (v_response, 20000, v_offset));
          v_offset := v_offset + 20000;
       END LOOP;

Shouldn't the additional conditions in the EXIT WHEN statement prevent an infinite loop from occurring? How do I terminate/prevent this loop when v_response is empty? I would like to avoid wrapping this loop in an if statement and would prefer a guard in the loop.

Answer 1

You can add additional control at the end like this;

DECLARE
    v_offset        NUMBER DEFAULT 1;
    v_response      CLOB;
BEGIN
    SELECT VALUE
     INTO v_response
     FROM json_cache
     WHERE json_key = 'EMPLOYEES';

   --infinite loop occurs when v_response = ''
   LOOP
      EXIT WHEN v_offset > DBMS_LOB.getlength (v_response) 
          or DBMS_LOB.getlength (v_response) = 0 
          or v_offset = 400000;
      HTP.prn (DBMS_LOB.SUBSTR (v_response, 20000, v_offset));
      v_offset := v_offset + 20000;

      if v_response is null then
         exit;
      end if;
   END LOOP;
END;

Answer 2

You can always check for a null value:

loop
   exit when v_offset > dbms_lob.getlength (v_response) 
       or dbms_lob.getlength (v_response) = 0
       or v_offset = 400000
       or dbms_lob.getlength (v_response) is null; 
   v_offset := v_offset + 20000;
end loop;