Tuesday, 9 January 2007

New Line Character Using UTL_FILE

A while ago I came across an issue exporting the contents of a BLOB to a flat file on my file system. My processing was to upload a .csv file into the wwv_flows_files table, and write a file back to my Oracle directory using the UTL_FILE api. Upon inspecting the file produced, a newline character was inserted after every line.

To cut a long story short, a colleague of mine came up with a workaround that involved parsing the newley produced file, removing any spurious lines and creating a new file minus the blank lines. As you can imagine, for large files, this added a fair amount of time to the Upload process.

There is light at the end of the tunnel however.

This article was sent to me by another colleague. At the end there is a solution to the problem. Cheers Kris

"Oracle 10g includes extra open modes (rb, wr, ab) to signify byte mode operation. The "wb" open mode can be used along with the PUT_RAW procedure to prevent extra newline characters being added on a Windows platform"

I shall refrain from any Windows bashing as it would be too easy as the problem does not occur on Linux. If anyone else wants to jump in and have a dig at Microsoft, feel free.

Here is a link to the a thread on the Application Express Discussion Forum as well.

No comments: