Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 15440 invoked from network); 6 Jun 2006 23:02:12 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 6 Jun 2006 23:02:12 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DC93A34DABB; Wed, 7 Jun 2006 00:02:12 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 10336-05; Wed, 7 Jun 2006 00:02:12 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5B3AB34D8D5; Wed, 7 Jun 2006 00:02:12 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 07 Jun 2006 00:01:29 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4326234DAD6 for ; Wed, 7 Jun 2006 00:01:29 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 10040-07 for ; Wed, 7 Jun 2006 00:01:29 -0400 (EDT) Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.181]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F1D9934DA37 for ; Wed, 7 Jun 2006 00:01:28 -0400 (EDT) Received: by py-out-1112.google.com with SMTP id i49so95267pyi for ; Tue, 06 Jun 2006 21:01:28 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Tn0oUNaJWqDxhv0zSk6/Lng76jVRH0tAf9elPm3tbxjjRC7V32PvOkqbLl7PZu7O06RFBhKKAoOqTJYPL41LI/UZKhFFUhhVZtX5hEWA+A/y4wRgdUUvbFNAVEQiHupptdX6KGFlvLa7K1NHUE3+8jtG3h35X73OKBjbJyygihk= Received: by 10.35.27.1 with SMTP id e1mr140391pyj; Tue, 06 Jun 2006 21:01:28 -0700 (PDT) Received: by 10.35.88.10 with HTTP; Tue, 6 Jun 2006 21:01:28 -0700 (PDT) Message-ID: <706468990606062101ye5ceccasdfda257178fd7173@mail.gmail.com> Date: Wed, 7 Jun 2006 12:01:28 +0800 From: "Yongping Yao" To: "Graeme.Farmer@mincom.com" Subject: Re: SPOOL Problem Cc: oracle-l@freelists.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5879_6710676.1149652888429" References: X-archive-position: 35696 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: yaoyongping@gmail.com Precedence: normal Reply-to: yaoyongping@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at avenirtech.net ------=_Part_5879_6710676.1149652888429 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanks a lot. I will try UTL_FILE. I "unload" data to feed a linux-based program. That program uses text files as input and I cannot modify it :( . I'm now reading http://asktom.oracle.com/~tkyte/flat/index.html. It seems a useful solution. BTW, I tried to search on Metalink. There's no hits. On 6/7/06, Graeme.Farmer@mincom.com wrote: > > You would probably be best to write a PL/SQL procedure to achieve this > making use of UTL_FILE to write directly to a file rather than via the SPOOL > mechanism in SQL*Plus. > > > > Using PL/SQL gives the ability to perform BULK fetches from the source > data too which can significantly improve performance. > > > > If you look hard enough, you will probably find some code on the web that > will unload to CSV or SQL*Loader format text files. > > > > What is the purpose of writing the tables to text file? Do you need to > archive the data in a portable format? Loading data to another system? > Different platform/rdbms? > > > > Cheers > > * * > > *Graeme Farmer * > -- Yao Yongping Learning Oracle, UNIX/Linux... Love Reading, Classical Music, Philosophy, Economics etc. Blog: http://spaces.msn.com/members/yaoyp/ ------=_Part_5879_6710676.1149652888429 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanks a lot.
I will try UTL_FILE.
I "unload" data to feed a linux-based program. That program uses text files as input and I cannot modify it :( .
I'm now reading http://asktom.oracle.com/~tkyte/flat/index.html. It seems a useful solution.

BTW, I tried to search on Metalink. There's no hits.

On 6/7/06, Graeme.Farmer@mincom.com <Graeme.Farmer@mincom.com> wrote:

You would probably be best to write a PL/SQL procedure to achieve this making use of UTL_FILE to write directly to a file rather than via the SPOOL mechanism in SQL*Plus.

 

Using PL/SQL gives the ability to perform BULK fetches from the source data too which can significantly improve performance.

 

If you look hard enough, you will probably find some code on the web that will unload to CSV or SQL*Loader format text files.

 

What is the purpose of writing the tables to text file? Do you need to archive the data in a portable format? Loading data to another system? Different platform/rdbms?

 

Cheers

 

Graeme Farmer


--
Yao Yongping
Learning Oracle, UNIX/Linux...
Love Reading, Classical Music, Philosophy, Economics etc.
Blog:  http://spaces.msn.com/members/yaoyp/ ------=_Part_5879_6710676.1149652888429-- -- http://www.freelists.org/webpage/oracle-l