Re: How to unload data from a table

From: Antony Sampson <sampson_at_dtf.sa.gov.au>
Date: 1996/03/12
Message-ID: <3c7cc$c3a9.208_at_MURDOCH>#1/1

banglea_at_onr.com wrote:

>Oracle does not provide a tool that will directly unload a table
>to a flat file. If your requirement only involves moving data
>from one Oracle DB to another, then you can utilize EXP/IMP to
>physically get the data from one place to another, then utilize
>the SQL*PLUS COPY command to create a new object that is a subset
>of the rows/columns from the original table.
 

>In fact, if your machine(s) are using SQL*Net, there is no need
>to use EXP/IMP. You can simply execute the SQL*Plus COPY command
>over the network.
 

>If your requirement involves non-Oracle databases, then you would
>likely need to roll your own using Pro*C or the OCI interfaces.
 

>Hope this helps.

>In article <4h06uv$bun_at_news.nznet.gen.nz>, slc@stevensons.co.nz says...
>>
>>What's the easiest way unload some data from a table to a
>>text file, that could then be loaded in another database or
>>table via SQL*Loader.
>>
>>With Informix you can say:
>>unload to "filename" select ...
>>
>>The rows & columns returned by the select statement
>>would be stored in pipe-delimitered format in the specified
>>file.
>>
>>Is there a similar facility in Oracle 7? I'm not sure if the
>>"exp" and "imp" utilities are suitable, because I might
>>not require all rows or columns from the source table.
>>
>>Any help much appreciated. Please reply via email.
>>
>>Steve Chell
>>slc_at_stevensons.co.nz
>>
>>

SQL*plus writes to a flat file pretty well, all you have to do is set some of the variables such that no other crap is being returned, then use a spool command to write to the file in question.

You will need to set heading off, set pagesize > number of rows in table, linesize > width of table, confirm off, pause off there are others, just look through the list in the sql*plus manual.

I have ported heaps of tables using this method. Just use loader at the other end.

Another useful way is to query the table into excel, using microsoft query, then save the spreadsheet as a text file, delimited as you prefer.

Hope this helps,

---

Tony Sampson
DBA/software terrorist, South Australian Government Financing Authority sampson_at_dtf.sa.gov.au

                   The unnatural, that too is natural.
                                  Goethe

Received on Tue Mar 12 1996 - 00:00:00 CET