Re: Reading delimited text from a file

On 16/02/06, Jason Heinrich <jheinrich_at_pcci.edu> wrote:
> I am attempting to create a function that will return a string from a text
> file, based on a user-specified delimiter such as a page-break or "|" or
> even "bob". As part of this process I am using UTL_FILE.GET_RAW to read a
> block of the file and converting it to text with UTL_I18N.RAW_TO_CHAR. The
> problem is, RAW_TO_CHAR converts DOS line breaks (CRLF) into Unix line
> breaks (LF), and I want to retain the original intact. I've done some
> hunting through the documentation and Google and so far I haven't seen any
> solutions, either to read the data for me or to convert from raw to char
> properly.
>
> So I have 2 questions:
> 1) Is there something already freely available to read a text file based on
> a user-specified delimiter (am I reinventing the wheel)?

Have a look in the docs for External Tables. Might be what you're looking for. Alternatively there's always good old SQL*Loader, load the data into a table and away you go.

Depending on what you're looking to do it might be worth looking at tools like sed and awk to either preprocess the data before loading it into the database (to get it into a more paletable form) or for the entire process.

Stephen

--
It's better to ask a silly question than to make a silly assumption.

http://stephensorablog.blogspot.com/
--
http://www.freelists.org/webpage/oracle-l