Unloading data from Oracle tables

I am running on version 8.02 of Oracle. I want to unload data from an Oracle table to a "~" delimited ASCII file so that I can bcp in the data to SQL Anywhere. Is there a similar facility(like bcp) in Oracle by which I can just output the data to a data file? I am a total novice at it so please bear with me if this is a stupid question to ask. I tried the export facility but that unloaded the data in binary format.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


I really don't know if the following answer solve your problem, but I think that I could helps you.

Suppose you have a table called MY_TABLE with two columns (COLUMN_A, COLUMN_B). If I want to download the data I must run the sqlplus or sql worksheet (Oracle Enterprise Manager); then I spool the output to a file with the spool command (e.g. "spool c:\my_file"). Then I execute the following command:

spool off;

This command generates a file (c:\my_file); you can edit it and delete the titles and blank rows. You have the option to extract the information without the titles (the command depends on the software you're using -sqlplus or sql worksheet-).

I hope this helps you. If not, please don't hesitate to contact me directly to my e-mail address: jmponcemartinez@hotmail.com

Best regards,

Juan Manuel Ponce Martinez
DBA - Buenos Aires - Argentina
jbiswasAuthor Commented:
I have tried using the spool command to output the data. However the data comes out in a formatted pattern, which has to be massaged a lot. For a table that has 100000 rows this could mean sitting down and doing this for the rest of your life. Also this table has 84 columns, I really have no wish to list all of them in a select clause. Doesn't Oracle provide any tool to take the data out of a table in just a ASCII flat file format? Surprises me, 'coz this is an oft done thing by any DBA.In Sybase and MS SQL Server this is so easy with the bcp(bulk copy) command.
There isn't a tool like bcp in Oracle.

To format the fields you have many functions like substr,  to_date, to_char, lpad, rpad, ltrim, rtrim, etc. in order to do it.

I know that it's a complex task, but there isn't another way to do it.

I will not be unhappy if you reject my answer, but it's the true.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

I forgot to ask you something: what application are you runing ?

This is important because if you have PeopleSoft or SAP this soft's have tools to retrieve data.
I don't know of an output utility for Oracle that is like the the bcp utlitity however just for you info there is an input utility.  The input utility is call sql*loader (sqlloadMost Oracle people do use the sqlplus option with some options set upon entry.  The options to set are

set head off
set linesize <XXXX> (row width)
set pagelength 0
set feedback off

unfortunately you will have to use queries and sql .... unless there is any scripts out there!

We Oracle people don't have to move data out of the database 'cos it is soooooo good!!! ;-)
jbiswasAuthor Commented:
I understand what you guys are saying, but that still does not answer my question. Dougier, I tried the options you gave here, but that still does not do what I want to do. It still pads the file with all spaces based on the linesize. Why does such a simple thing have to be made so difficult, beats me! I finally went to Access and just linked the table and saved the output to a .csv file. That certainly did it. I love your confidence in Oracle, but I hate to say it, the bcp command in both Sybase and MS SQL Server is a neat way to unload data. I'm sure there is some third party tool to do this easily, somebody must know. By the way Juan, thank you for your responses, and yes I do know about the SQL Loader for loading data.
Just a couple of idle suggestions (I won't classify these as an answer, 'cause they're more like general ideas how you might approach this).

There's a table_to_comma procedure supplied in the DBMS_UTILITY package (and that's as much as I know about it).  Might be worth looking at, although I don't know, for example, if you can specify the '~' as the delimiter.

Building on previous suggestions, you might consider creating a SQL-to-create-SQL utility that, given a table name as input, uses the data dictionary to build the proper SQL*Plus script to create the file...  Don't think of it as manually dealing with 84 columns, but as dealing with however many data types you need to support, and having SQL do the work.  It's a very powerful approach, and if you're interested I could point you toward some samples.  To get an idea what I'm driving at, see http://www.moug.org/Migr_scr.htm
Of course if you had a couple of hours to spare, and you know forms 4.5, I'm sure there should be a way of dynamically building your query and using TEXT_IO create your file.
It might be worth the effort if you need to do this a lot.

jbiswasAuthor Commented:
Why would someone go though the painful experience of setting up any of the utilities supplied by Oracle to do something this simple. I finally just used Access to link to that table and saved the file in .csv format. This is the easiest route that I found out as yet. I had to get this file for data entry into a SFA application written in SQL Anywhere.
Access is a good solution in this case, but that doesn't mean that, overall, Oracle is a bad product.  We're trying to support Access as a client-side tool for just these kinds of situations.  Different products have different strengths, and none of them are perfect.  Good thing, too, 'cause anything that did everything would be too expensive to buy.  For example, neither Access nor SQL Server are able to support nearly the volume of data nor transaction rates that Oracle does.

Creative solutions, such as your own, are what talented professionals, with a wide range of experience, can bring to the table.
BCP is not a panacea. SQL Plus is much more powerfull in what you need to do. You need to check the SET command in SQL*PLUS manual. It will allow you to produce an output file in just about any format you need including not padding your records with blanks.
Install the Oracle ODBC driver into MS Query (part of Excel).  Create the query in MS Query and export to Excel or Access or whatever and then the world is your oyster.

Or check out www.toadman.com - he has this GREAT free Oracle utility.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jbiswasAuthor Commented:
I am awarding this to you since I don't want any more responses  to this question. Before you mentioned the MS Query option I had done this with Access, but recently I got a unsupported program from Oracle which is called Dumper. If any of you want this utility I will be glad to pass it on. It is called dumper.c and all you have to do is compile this code and run it with the right parameters. Pretty simple..just what i was looking for.
Yes, I would like the dumper program.  send to maricobb@ix.netcom.com.  Thanks so much!
Can I have a copy of the dumper program. nickhaider@yahoo.com
Would you be so kind to send me a copy of the dumper.c? Thanx,
Would it be possible to also send me this utility (dumper.c) , sounds a lot easier than manipulating the output by writing my own script.
Thanks in advance.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.