DECLARE
  v_clob CLOB;
  v_text VARCHAR2(32767);
BEGIN
  -- 1. Create a temporary LOB in memory
  DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
  
  -- 2. Open it for writing
  DBMS_LOB.OPEN(v_clob, DBMS_LOB.LOB_READWRITE);
  
  FOR i IN 1..1000 LOOP
    v_text := 'Building a massive payload. Row: ' || i || CHR(10);
    
    -- 3. Append the varchar chunk to the end of the CLOB
    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_text), v_text);
  END LOOP;
  
  -- 4. Always close and free the LOB to prevent PGA memory leaks!
  DBMS_LOB.CLOSE(v_clob);
  DBMS_LOB.FREETEMPORARY(v_clob);
END;
/

DECLARE
  v_clob   CLOB;
  v_buffer VARCHAR2(8000);
  v_amount INTEGER := 8000;
  v_offset INTEGER := 1;
BEGIN
  SELECT payload INTO v_clob FROM api_logs WHERE id = 100;
  
  DBMS_LOB.OPEN(v_clob, DBMS_LOB.LOB_READONLY);
  
  LOOP
    BEGIN
      -- Read 8000 bytes starting at the offset
      DBMS_LOB.READ(v_clob, v_amount, v_offset, v_buffer);
      
      -- Process the chunk (e.g., print it)
      DBMS_OUTPUT.PUT_LINE(v_buffer);
      
      -- Move the offset forward for the next iteration
      v_offset := v_offset + v_amount;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT; -- End of the LOB reached
    END;
  END LOOP;
  
  DBMS_LOB.CLOSE(v_clob);
END;
/

DECLARE
  v_position INTEGER;
BEGIN
  -- Find the position of 'ERROR_CODE' starting at character 1, 1st occurrence
  v_position := DBMS_LOB.INSTR(v_clob, 'ERROR_CODE', 1, 1);
  
  IF v_position > 0 THEN
    -- Extract 50 characters starting from that position
    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(v_clob, 50, v_position));
  END IF;
END;
/