Re: Different ways to transfer data in a table to a FLAT file

You have basically three means :
 o PL/SQL with UTL_FILE. You are limited by the line size. An advantage is that everything happens on the server (no data transfer across the network).
 o SQL*Plus spool. Works fine when used cleverly. Jared's dump.sql is a good example and is fast, I think I have seen others mentioned on www.lonyx.com
 o Third party products. Oriole has one, pdqout <plug>free for up to 250,000 rows starting with version 2.1 available next week</plug>, CA has one, BMC has one ...

By and large it's a matter of volume. If all you want to do is inject 2,000 rows in an Excel spreadsheet, the first solution is perfect. In the tens of thousand rows I'd switch to SQL*Plus. With really large volume yo may appreciate a product (pdqout runs about 4/5 times faster than SQL*Plus and generates smaller files, so the upload is also faster). If your download takes 10mn with SQL*Plus, you have no reason to spend your hard-won dollars on a faster tool. If it takes 4 hours or more and your maintenance window is short, it is worth considering a 3rd party product. Frequency also matters a lot. You are not going to buy a product for a once-in-a-lifetime operation. If you want something which runs everyday, it's a bit different - or at least can be. Another important point is flexibility. If all you want to download is always the same table, a bit of PL/SQL coding is OK. If it's a series of very different tables, SQL*Plus or a third party product is what you need.

To be complete, some products such as Quest's Toad also allow you to create a sql script doing inserts - not exactly a flat file, but another alternative way to imp if that is what you have in mind, although not suitable for large volumes.

HTH Stephane Faroult
Oriole Corporation

>Hi gurus,
>
>Please suggest me a different ways to transfer data from the table
to a FLAT
>file...
>
>Thanks
>Ram Sankaranarayanan
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Sankaranarayanan, Ramasubramanian
> INET: Ramasubramanian.Sankaranarayanan_at_compaq.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051

>San Diego, California -- Public Internet access / Mailing Lists

>--------------------------------------------------------------------

>To REMOVE yourself from this mailing list, send an E-Mail message

>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may

>also send the HELP command for other information (like subscribing).
Received on Fri Jan 12 2001 - 17:53:54 CST