Solved

Unloading data from Oracle tables

Posted on 1998-04-28
17
1,975 Views
Last Modified: 2012-05-04
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.
0
Comment
Question by:jbiswas
  • 4
  • 3
  • 2
  • +7
17 Comments
 
LVL 3

Expert Comment

by:poncejua
Comment Utility
Hi,

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:

select COLUMN_A||'~'||COLUMN_B FROM MY_TABLE;
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
0
 
LVL 2

Author Comment

by:jbiswas
Comment Utility
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.
0
 
LVL 3

Expert Comment

by:poncejua
Comment Utility
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.


0
 
LVL 3

Expert Comment

by:poncejua
Comment Utility
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.
0
 

Expert Comment

by:dougier
Comment Utility
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!!! ;-)
0
 
LVL 2

Author Comment

by:jbiswas
Comment Utility
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.
Jit  
0
 
LVL 2

Expert Comment

by:frankr
Comment Utility
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
0
 
LVL 3

Expert Comment

by:vlad_impala
Comment Utility
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.

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Author Comment

by:jbiswas
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:frankr
Comment Utility
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.
0
 
LVL 3

Expert Comment

by:mgokman
Comment Utility
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.
0
 

Accepted Solution

by:
mari050598 earned 50 total points
Comment Utility
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.
0
 
LVL 2

Author Comment

by:jbiswas
Comment Utility
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.
0
 

Expert Comment

by:mari050598
Comment Utility
Yes, I would like the dumper program.  send to maricobb@ix.netcom.com.  Thanks so much!
0
 

Expert Comment

by:nickhaider
Comment Utility
Can I have a copy of the dumper program. nickhaider@yahoo.com
Thanks
0
 

Expert Comment

by:norbert_novotny
Comment Utility
Would you be so kind to send me a copy of the dumper.c? Thanx,
norbert_novotny@yahoo.com
0
 

Expert Comment

by:dgallop
Comment Utility
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.

dgallop@nomadsoft.co.uk
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now