Thursday, October 08, 2009

http://asktom.oracle.com/tkyte/flat

How Can I unload data to a flat file

Many times we are asked

  • "Does anyone know an easy way of dumping the data from an Oracle table into a delimited(comma, tab etc) ascii file?"
  • "Does anyone know an easy way to unload data in a format for sqlldr to reload later?"

Well here is a way to do it into Excel or any spreadsheet that understands the industry standard SYLK file format.

Here is a pro*c program that does it to a flat file very fast.

and here is a PLSQL routine that uses utl_file to do the same

A reader (Andy Rivenes) offers this more "robust" plsql implementation based on the original code.

And lastly, SQLPlus can do this quite easily but it is a pain to have to write a script/table. What I've done is setup scripts for UNIX and NT that allow you from the command line to execute things like:


$ sqlldr_exp scott/tiger dept
LOAD DATA
INFILE *
INTO TABLE dept
REPLACE
FIELDS TERMINATED BY '|'
(
deptno
,dname
,loc
)
BEGINDATA
10|ACCOUNTING|NEW YORK
20|RESEARCH|DALLAS
30|SALES|RESTON
40|OPERATIONS|BOSTON

As you can see, this script unloaded the scott.dept table into a format that sqlldr can easily reload. All you would need to do is execute:


$ sqlldr_exp scott/tiger dept > dept.ctl

to create a control file that can be moved somewhere else and reloaded back into a dept table very quickly.

I also use a slight modification of this script called "flat". Flat does the same thing as sqlldr_exp does mostly except that it dumps the data into a tab delimited format without the sqlldr extras at the top. This makes the extract usable in spreadsheets and such.

In both cases some things you need to be aware of are with regards to this script:

  • There is an absolute limit of 2000 bytes in 7.x and 4000 bytes in 8.x per line/row for unloaded data. The total size of the unloaded data is unlimited -- the maximum size of an individual row of data is what is limited.
  • It makes no attempt to unload dates with the century or time component -- you must change your default NLS_DATE_FORMAT if this is a problem.
  • Beware of data with pipes or tabs in it!
  • Beware of data with newlines as well...
  • The NT scripts (.cmd files) need modifications if your command line sqlplus is not called SQLPLUS (eg: its plus33 or something similar)
  • On NT, you need to set your SQLPATH environment variable and put these files into that directory OR you need to run flat and sqlldr_exp from those directories so sqlplus can find the corresponding flat.sql and sqlldr_exp.sql files.
download that script here
POST A COMMENT

7 Comments:

Anonymous Martijn (Hoek(stra)) said....

Hi and sorry to bother you, you seem quite busy, Tom, but:

What's going on?
Some classic or 'pet peeve' AskTom-links are gone (some are 'Googe-able, but no cache seems to exist anymore), and you're moving the 'nice classic stuff' to your blog?
Is this a temporary thing or...?

Thu Oct 08, 04:13:00 PM EDT  

Blogger Thomas Kyte said....

I gave up my server (quite quite VERY willingly)

I do not have a "public_html" directory anymore as a result of that - so, I'm putting what I need to host into my files area on asktom and relinking to it all from this page - which is basically the old page.

This gets most everything into the database and over time - the plan is to remove the need for the blogspot "file system" stuff altogether.

I'll write a program to update the links in asktom ultimately - soon - after Oracle OpenWorld.

I have two sets of links to fix there -

a) ones with /ask/ in them instead of /asktom/ - the /ask/ stuff is the old old format - need to update that to the new one

b) ones with /tkyte/ or /~tkyte/ in them - need to update them to point to blogspot or to the files on asktom

Thu Oct 08, 04:19:00 PM EDT  

Anonymous Martijn (Hoek(stra)) said....

Thanks for elaborating in the midst of your operation.

Besides the '~tkyte links', what really wonders me: some searches on AskTom deliver unexpected/different results since you gave up your server.
( I've been reading and searching for a 'big while' on AskTom, but my 'auto-search-pilot' just doesn't seem to work anymore ;) )

Some great stuff just doesn't show up anymore in the search-results.
Could that be some 'index-ism'?

I hope to be able to attend Oracle OpenWorld next year, by the way, it's about time by then.

All the best fixing your sets!
( Oh well, no doubt you'll manage that ;) )

Thu Oct 08, 06:34:00 PM EDT  

Blogger Carmen said....

Hello,

Thanks for this interesting information.

I have a question:
Inside the Excel toolbar, Web ADI defines the Oracle tap where the Upload and Download options are. Where Web ADI defines the Oracle tap? Can I modify that menu and unify in the same button the upload and download processes.

Thanks in advance,
Carmen.

Fri Nov 06, 04:16:00 AM EST  

Blogger Thomas Kyte said....

@Carmen

I don't really cover Oracle Applications at all - sorry..

Fri Nov 06, 07:53:00 AM EST  

Anonymous Anonymous said....

Do you know who cover Oracle Application and Oracle Deloper?

Tue Nov 20, 11:27:00 AM EST  

Blogger Thomas Kyte said....

@Anonymous

otn.oracle.com -> discussion forums would be a place to start.

Tue Nov 20, 12:00:00 PM EST  

POST A COMMENT

<< Home