Oracle- Parsing data in a specific row into a temp table

50 pts.
Tags:
Oracle
Oracle 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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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 ?
    69,835 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following