How to split a CLOB object using , and : delimiter in Oracle into multiple records
<p>I have a CLOB object sample as shown below. I want to first split this by using delimiter "," and save it in a temporary table for later use.</p>

<pre><code>ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0
</code></pre>

<p>I want to save the result in the below format in each row.</p>

<pre><code>Column_Name
__________________________
ABCDEF:PmId12345RmLn1VlId0
ABCDEF:PmId12345RmLn1VlId0
ABCDEF:PmId12345RmLn1VlId0
</code></pre>

<p>I tried using REGEXP_SUBSTR function</p>

<pre><code>select 
    regexp_substr('ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0', '[^,]+', 1, 1) Column_Name 
from dual;
</code></pre>

<p>The above query gives me single record like below</p>

<pre><code>Column_Name
__________________________
ABCDEF:PmId12345RmLn1VlId0
</code></pre>

<p>Can anyone help me solve this issue.</p>