showing complete clob using sql; overcoming the 4000 Character limit in sql

I was working on a project; and needed create indexing using a BI tool. We store a lot of information in CLOB column (Oracle DB). As soon as I embarked on the project; I realized there was an issue. SQL has a 4000 char limit for result sets returned. And I can not set the session variables to increase the size. Therefore had to come with a creative way to get the result set out of the blob. So here it goes

SELECT ROWNUM as chunk_no,ID, SUBSTR (t1.clob_col, (ROWNUM-1)*4000, 4000) AS chunkFROM t1
CONNECT BY (ROWNUM-1)*4000 <= LENGTH(t1.clob_col)

This takes care of the issue; however there is a huge performance hit. If you know a tentative size that your data will not exceed; then you can do something like this:

SELECT SUBSTR (t1.clob_col, 1, 4000) AS pt1,
       CASE WHEN LENGTH (t1.clob_col) > 4000  THEN SUBSTR (t1.clob_col, 4001, 4000) END AS pt2,
       CASE WHEN LENGTH (t1.clob_col) > 8000  THEN SUBSTR (t1.clob_col, 8001, 4000) END AS pt3,
       CASE WHEN LENGTH (t1.clob_col) > 12000 THEN SUBSTR (t1.clob_col, 1201, 4000) END AS pt4FROM t1

credits: Kev for sharing some of the tricks