Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Reading delimited text from a file

Re: Reading delimited text from a file

From: Jason Heinrich <>
Date: Fri, 17 Feb 2006 08:20:29 -0600
Message-ID: <>

On 2/16/06 4:34 PM, Deepak Sharma wrote:

> Which Oracle Version is it?

Oops, sorry.

On 2/16/06 1:30 PM, stephen booth wrote:

> 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.

I'm not really looking to load data into tables, I just want to return some text from the specified file to the caller. I did consider using the external table approach and then just returning a row for each subsequent call, but that would prevent them from using a different delimiter than what they used on the first call. I don't know of anything that does that, but I suppose anything is possible... I've noticed that this also affects my GetBlockData function, which simple returns n characters from a file, because I'm using the same methodology: read n bytes of raw data and convert it to a string. (Yes, I do assume that the file uses a 1-byte character set, which in our case is safe).

A little history: Several years ago a DLL was written to do things that UTL_FILE could not do at the time, like deleting a file. Since a large number of procedures still call the package that wraps this DLL, I'm rewriting the package body to only use functionality that is included in the database. This will allow me to more easily migrate to RAC or even other platforms in the future. I have everything working, except that RAW_TO_CHAR converts CRLFs to LFs.

Jason Heinrich
Oracle Database Administrator
Pensacola Christian College
Received on Fri Feb 17 2006 - 08:20:29 CST

Original text of this message