Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: oracle table to flat file?

Re: oracle table to flat file?

From: Mark Bole <>
Date: Fri, 06 Aug 2004 02:10:29 GMT
Message-ID: <pMBQc.865$>

Joel Garry wrote:

> "Howard J. Rogers" <> wrote in message news:<411020f1$0$25458$>...

>>"Chris" <> wrote in message

>>>>that you'll embedd trailing spaces into your data.

> Don't take it personal, Chris, but I was trying to knock colsep. Any
> quick and dirty solution is going to have to cope with ampersands and
> commas and anything you can set colsep to, at least, and 8-bit
> characters and who-knows-what at worst. So you wind up eating dirt.
> Even old solid tools like exp can be befuddled by the odd stuff - I
> had a scare once when an import stripped the bit off a string that was
> actually being used as a numeric counter by a strange app.
> So I do another silly thing, and concatenate in separators that are
> _extremely_ unlikely to be in _my_ data, like @@@@@, and use awk and
> sed to get it in whatever format I want. Try putting some bizarro
> data in an Excel spreadsheet and export it to csv file and load it
> with sql*loader and then go the other way to get handy with the
> gotchas involved.
> And of course it is silly, because I actually do have some data that
> has @@@@@ in it, and didn't even notice it until I wrote this. It
> wasn't something that would have shot me in the foot until I did this
> on 1000 tables, but there you go. "Howard's way" is better, and the
> topic of Q&D is worth discussion.
> jg
> --


Here's a challenge: convert any table (common relational data types, excluding the more exotic ones such as RAW, LONG, xLOB, BFILE, ROWID, haven't tried TIMESTAMP) from Oracle into a CSV file that can, in turn, be imported into MS Access without error (including commas, newlines (Un*x or Windows style), quotes, punctuation, whatever the Oracle 9i and MS Access charset supports for my locale).

I know it can be done using Perl DBI and other Perl modules (slightly off-topic), but it works for what I think is a high percentage of real-world data.

Bold claim: I don't think it's possible to code a SQL*Plus query that will correctly handle any VARCHAR2(4000) data with the above mentioned border test cases and convert it into a CSV file that MS Access (or Excel, for that matter) can read wihtout error.

Don't neglect byte length semantics vs. character semantics.

--Mark Bole Received on Thu Aug 05 2004 - 21:10:29 CDT

Original text of this message