Oracle Parse NCLOB data to Output or New Table

Question

I have an Oracle 11.2.0.4.0 table named LOOKUPTABLE with 3 fields

LOOKUPTABLEID NUMBER(12)
LOOKUPTABLENM NVARCHAR2(255)
LOOKUPTABLECONTENT NCLOB

The data in the NCLOB field is highly validated on insert so I'm certain the data always is a comma separated string with a CRLF on the end so reads exactly like a simple CSV file. Example ([CRLF] is representation of an actual CRLF, not text)

WITH lookuptable AS (
SELECT
1 AS "LOOKUPTABLEID",
'CODES.TBL' AS "LOOKUPTABLENM",
TO_NCLOB('851,ALL HOURS WORKED GLASS,G,0,,,,,,'||chr(10)||chr(13)||
         '935,ALL OT AND HW HRS,G,0,,,,,,'||chr(10)||chr(13)||
         '934,ALL PAID TIME,G,0,,,,,,'||chr(10)||chr(13)) AS "LOOKUPTABLECONTENT"
FROM dual
)
SELECT lookuptablecontent FROM lookuptable WHERE lookuptablenm='CODES.TBL';

"851,ALL HOURS WORKED GLASS,G,0,,,,,,[CRLF]935,ALL OT AND HW HRS,G,0,,,,,,[CRLF]934,ALL PAID TIME,G,0,,,,,,[CRLF]"

I essentially want to have a query that can output 1 row for each line in the CLOB. I'm using an application that will read this SQL and write it to a text file for me but it cannot handle CLOB data types and I don't have the option to write directly to file from SQL itself. I have to have a query that can produce this result and allow my app to write the file. I do have the ability to create/write my own tables so a procedure that would read the CLOB into a new table and then I would select from that table in my application would be acceptable if that's better, its just over my head right now. Desired output below, thanks in advance for any help :)

1. 851,ALL HOURS WORKED GLASS,G,0,,,,,,
2. 935,ALL OT AND HW HRS,G,0,,,,,,
3. 934,ALL PAID TIME,G,0,,,,,,

Answer 1

This is a specific case of a general question "how to split a string", and I link this question a lot for more details on that. In this case, instead of a comma, the delimiter that you want to split on is CRLF, or chr(10)||chr(13).

Here's a simple solution with regexp_substr. It's not the fastest solution, but it works fine in simple scenarios. If you need better performance, see the version in the link above with a recursive CTE and no regexp.

WITH lookuptable AS (
SELECT
1 AS LOOKUPTABLEID,
'CODES.TBL' AS LOOKUPTABLENM,
TO_NCLOB('851,ALL HOURS WORKED GLASS,G,0,,,,,,'||chr(10)||chr(13)||
         '935,ALL OT AND HW HRS,G,0,,,,,,'||chr(10)||chr(13)||
         '934,ALL PAID TIME,G,0,,,,,,'||chr(10)||chr(13)) AS LOOKUPTABLECONTENT
FROM dual
)
SELECT lookuptableid as id, to_char(regexp_substr(lookuptablecontent,'[^('||chr(13)||chr(10)||')]+', 1, level))
FROM lookuptable 
WHERE lookuptablenm='CODES.TBL'
connect by level <= regexp_count(lookuptablecontent, '[^('||chr(13)||chr(10)||')]+')
and PRIOR lookuptableid =  lookuptableid and PRIOR SYS_GUID() is not null -- needed if more than 1 source row  
order by lookuptableid, level
;

Output:

id  r
1   851,ALL HOURS WORKED GLASS,G,0,,,,,,
1   935,ALL OT AND HW HRS,G,0,,,,,,
1   934,ALL PAID TIME,G,0,,,,,,

Answer 2

My example data and format using the recursive CTE without regexp from link provided by @kfinity

WITH lookuptable (lookuptableid, lookuptablenm, lookuptablecontent) AS (
  SELECT
    1,
    'CODES.TBL',
    TO_NCLOB('ID,NAME,TYPE,ISMONEYSW,EARNTYPE,EARNCODE,RATESW,NEGATIVESW,OVERRIDEID,DAILYSW'||chr(13)||chr(10)||
             '851,ALL HOURS WORKED GLASS,G,0,,,,,,'||chr(13)||chr(10)||
             '935,ALL OT AND HW HRS,G,0,,,,,,'||chr(13)||chr(10)||
             '934,ALL PAID TIME,G,0,,,,,,'
              )
   FROM dual
), CTE (lookuptableid, lookuptablenm, lookuptablecontent, startposition, endposition) AS (
  SELECT
    lookuptableid,
    lookuptablenm,
    lookuptablecontent,
    1,
    INSTR(lookuptablecontent, chr(13)||chr(10))
   FROM lookuptable
   WHERE lookuptablenm = 'CODES.TBL'
  UNION ALL
  SELECT
    lookuptableid,
    lookuptablenm,
    lookuptablecontent,
    endposition + 1,
    INSTR(lookuptablecontent, chr(13)||chr(10), endposition+1)
   FROM CTE
   WHERE endposition > 0
)
SELECT
  lookuptableid,
  lookuptablenm,
  SUBSTR(lookuptablecontent, startposition, DECODE(endposition, 0, LENGTH(lookuptablecontent) + 1, endposition) - startposition) AS lookuptablecontent
 FROM CTE
ORDER BY lookuptableid, startposition;