Assuming that you want to wind up with a bunch of “rows” – each containing one “line” of text, you need to parse it in two steps.
First, keep in mind that the field can contain a nearly unlimited amount of text, while the internal limit on VARCHAR and VARCHAR2 length is 32767 characters. Thus, you will need to read the data into a CLOB structure, then break that into smaller pieces using the dbms_lob packaged functions.
Second, the text contains Carriage Return/Line Feed (CR/LF – 0x0d/0x0a) characters at the end of each line – you can break on these characters to separate the text into lines.
Also, note that the text contains Tab (HT – 0×09) characters as well – depending on what you want to do with the text, you may want to expand the Tab characters to a fixed number of spaces. There will also be leading and trailing spaces on lines – the field contains the text exactly as typed originally.
cCRLF constant CHAR(2) = chr(13) || chr(10);
select text into clob_memo from dba_views
where owner = ‘Something’
and view_name = ‘Something’;
iMemoLength := dbms_lob.getlength(clob_memo);
iCurrentPosition := 1;
exit when iCurrentPosition >= iMemoLength;
iEndLine := dbms_lob.instr(clob_memo, cCRLF, iCurrentPosition);
if iEndLine > 0 then
– found next CRLF, grab text up to there
vLine := dbms_lob.substr(clob_memo, iCurrentPosition, iEndLine); — vline has current line, minus CRLF
iCurrentPosition := iEndLine + 2; — skip over the CRLF
vLine := dbms_lob.substr(clob_memo, iCurrentPosition, 32767); — get last chunk, in case no terminator
iCurrentPosition := iMemoLength + 1; — set loop terminate condition
– do something here with “vLine”, which now contains a VARCHAR2 string with the line data
Caveat – I did this mostly from memory and don’t have an Oracle system handy to test this, so there may be some syntax errors in the above.