A quoted new line is treated as a new line in the csv line, not part of the cell value

[GoogleCodeExporter]

What steps will reproduce the problem?

Execute the following code:
select *
from table(csv_util_pkg.clob_to_csv (
    p_csv_clob  => '1,"And this' || chr(13) || chr(10) || 'as well",2'
));

What is the expected output? What do you see instead?

The expected output is for this to only have one tuple since the new line is 
contained within the quoted text.  A new line in quotes should be treated just 
like comma in quotes: it is part of the cell value and does not play a part in 
the structure of the file.

What version of the product are you using? On what operating system?

Please provide any additional information below.

Attached is the fix that I implemented in the code to allow for such behavior.  
It counts the number of quotes before the new line.  If there are an odd number 
of quotes, then the loop that gets the line, skips processing and looks for the 
next new line.

Also, the replace that was being done on CHR(13) and CHR(10) was changed into a 
loop that looks at the characters on the end of the string until it finds a non 
CHR(13) or CHR(10) character.  It then removes all CHR(13) and CHR(10) 
characters from the end of the line.  This is done to preserve the new lines 
that are possibly quoted earlier in the line.

The removal of the trailing new line can be done more simply/cleaner if you 
just do a substring of the line that is less the size of l_line_separator.  
However, this assumes all new lines are in the same format (something that the 
old code did not assume since you replaced CHR(13) and CHR(10) individually).

Hope this helps!

Original issue reported on code.google.com by jason.ly...@gmail.com on 14 Oct 2014 at 2:30

Attachments:

• csv_util_pkg_tweak.pls