2

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,,,,,,
sandsawks
  • 199
  • 9

2 Answers2

2

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,,,,,,
kfinity
  • 8,581
  • 1
  • 13
  • 20
  • Thanks @kfinity. This information lead me to my solution. As you mentioned your example worked fine on such a simple and small data set. my NCLOB record had almost 1500 CRLFs in it though so was crucially slow. I went to the link and section you mentioned with recursive CTE and no regexp. Ill post that method with my example data and layout in case anyone comes across this example. – sandsawks Jan 09 '20 at 14:43
1

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;
sandsawks
  • 199
  • 9