Oracle- Parsing data in a specific row into a temp table
I have used
select text from dba_views
where owner = 'Something'
and view_name = 'Something';
I get back a row with the word (MEMO) in it. However that row contains a lot of text when clicked on. Such as something like
SELECT
/*
SCCS Rev 1.1
10/14/2002 by Blah
SCCS Rev 1.2
10/24/2002 Blah
SCCS Rev 1.3
11/08/2002 D.Dolganenko
1. Time scan
How can i parse each row of that text, into a temp table?
Software/Hardware used:
Software/Hardware used:
ASKED:
July 21, 2009 3:44 PM
UPDATED:
July 23, 2009 4:31 PM
Answer Wiki:
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 - 0x09) 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.
declare
clob_memo CLOB;
vLine VARCHAR2(32767);
cCRLF constant CHAR(2) = chr(13) || chr(10);
iMemoLength integer;
iCurrentPosition integer;
iEndLine integer;
begin
select text into clob_memo from dba_views
where owner = 'Something'
and view_name = 'Something';
iMemoLength := dbms_lob.getlength(clob_memo);
iCurrentPosition := 1;
begin
loop
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
else
vLine := dbms_lob.substr(clob_memo, iCurrentPosition, 32767); -- get last chunk, in case no terminator
iCurrentPosition := iMemoLength + 1; -- set loop terminate condition
end if;
-- do something here with "vLine", which now contains a VARCHAR2 string with the line data
end loop;
end;
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.
To see all answers submitted to the Answer Wiki: View Answer History.
How do you want to parse it ?
Could you give us an example of what information you would like to have in each field of the temp table ?