Split Oracle CLOB into Lines - Fast Way

Splitting CLOB fields in Oracle is more tricky than VARCHAR2. I found a couple of implementations online, but all of them were reading data line-by-line which is slow. My version reads big chunks (32K) and then splits them.

See process your line here line in the code. If you need to post-process every line in CLOB, this is the place to do it.

CREATE OR REPLACE FUNCTION SplitClobLines ( pclob IN clob ) RETURN varchar2 IS
  c         clob;
  blobPos   number : = 1 ;
  chunkPos  number : = 32767 ;
  pos       number : = 0 ;
  pos2      number : = 0 ;  
  amount    number : = 0 ;
  blobLen   number;
  chunk     varchar2 ( 32767 ) : = '' ;
  chunkRead varchar2 ( 32767 ) : = '' ;
  line      varchar2 ( 32767 ) : = '' ;
begin
  c : = pclob;
  blobLen : = dbms_lob . getlength ( c ) ;  
 
  IF ( dbms_lob . isopen ( c ) ! = 1 ) then
    dbms_lob . open ( c , 0 ) ;
  end IF ;

  WHILE blobPos < blobLen LOOP
    amount : = chunkPos - 1 ;
    DBMS_LOB . READ ( c , amount , blobPos , chunkRead ) ;
    blobPos : = blobPos + amount;
    chunk : = substr ( chunk , chunkPos ) || chunkRead;

    chunkPos : = 1 ;

    while 1 = 1 loop
      pos : = instr ( chunk , chr ( 10 ) , chunkPos ) ;

      line : = '' ;

      IF ( pos > 0 ) then
        line : = substr ( chunk , chunkPos , pos - chunkPos - 1 ) ;
      end IF ;

      IF ( pos = 0 AND blobPos >= blobLen ) then
        line : = substr ( chunk , chunkPos ) ;
      end IF ;

      -- >> process your line here
      DBMS_OUTPUT . put_line ( line ) ;
      -- << process your line here

      IF ( pos = 0 ) then
        chunkPos : = length ( chunk ) ;
        exit;
      else
        chunkPos : = pos + 1 ;
      end IF ;
    end loop;
  END LOOP;

  IF ( dbms_lob . isopen ( c ) = 1 ) then
    dbms_lob . close ( c ) ;
  end IF ;

  RETURN '' ;
 
  exception when others then BEGIN
    dbms_output . put_line ( dbms_utility . format_error_backtrace ) ;
   
    IF ( dbms_lob . isopen ( c ) = 1 ) then
      dbms_lob . close ( c ) ;
    end IF ;
  END;
end;