Split CLOB into multiple VARCHAR2

Question

I currently am working with some data that is being stored as a CLOB between 4000 and 6000 characters. I would like to perform the TRANSLATE function on it but I learned that it's not possible with a CLOB. So my thought is to split the CLOB into multiple VARCHAR2's, and perform the TRANSLATE operation on each part. Say I have a 6000 character CLOB. I started by using SUBSTR(COL,4000,1) and storing the first 4000 in a variable. However, I'm having trouble getting the characters between 4000 - 6000. How can I go about splitting this CLOB?

Answer 1

Look at DBMS_LOB.SUBSTR and pay close attention to the differing definition from the SUBSTR you are familiar with.