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

One thing to keep in mind if you are using SQL*Plus spool function is that you want to execute a 'set term off' before actually executing your select script. Remember that it takes time to display the rows, and the term off eliminates that time.

-----Original Message-----

From:	Steve McClure [SMTP:steve_at_pactr.com]
Sent:	Friday, January 12, 2001 2:08 PM
To:	Multiple recipients of list ORACLE-L
Subject:	RE: Different ways to transfer data in a table to a FLAT file

>>SQL*Plus spool. Works fine when used cleverly. Jared's dump.sql
>>is a good example and is fast

SQL*Plus spool is faster than UTL_FILE? It is not that I found UTL_FILE to produce output with blazing speed, but it never occurred to me that simple, or even clever, spooling would be faster. I cannot count the times that I have used UTL_FILE to export data, sometimes with procedures would run for eight or ten hours at a time. I guess I will head back out to Jared's site and check this out.

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com] On Behalf Of sfaroult_at_oriole.com

Sent:	Friday, January 12, 2001 8:46 AM
To:	Multiple recipients of list ORACLE-L
Subject:	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).

>

Ce mel a ete envoye avec Meloo http://www.meloo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: sfaroult_at_oriole.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).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve McClure
  INET: steve_at_pactr.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