Re: SQLPLUS: creating a text file with column header and tabel rows?

From: Jim Smith <jim_at_jimsmith.demon.co.uk>
Date: Wed, 10 Aug 2005 09:53:24 +0100
Message-ID: <HdcVxaKECc+CFwDh@jimsmith.demon.co.uk>

In message <1123661497.372272.207690_at_g44g2000cwa.googlegroups.com>, Caja <carsten.jacobs_at_web.de> writes
>Thanx Jim for your answer.
>
>>try "set pagesize 0" to get rid of repeated headers
>but then I get no headline at all
>

Sorry about that.
>What I want is a flat file like this
>
>FAMILY_NAME|SUR_NAME|CITY|ZIPCODE|STREET
>Miller|John|Berlin|12290|Fleetstreet 12
>Trueman|Sandy|3445|Hamburg|Lakestreet
>
>and so on
>
>But on long lines it looks currently like this
>
>FAMILY_NAME|SUR_NAME|CITY|
>ZIPCODE|STREET
>Miller|John|Berlin|12290|
>Fleetstreet 12
>Trueman|Sandy|3445|
>Hamburg|Lakestreet
>
>even if I set linesize to a bigger value.

Works for me.
This may not show up if either of our mail clients wrap the long lines, but the last select after the "set linesize 200" is all on one line.

jim_at_OWINT4> desc address

  Name                    Null?    Type
  ----------------------- -------- ----------------
  FAMILY_NAME                      VARCHAR2(20)
  SUR_NAME                         VARCHAR2(20)
  CITY                             VARCHAR2(20)
  ZIPCODE                          VARCHAR2(10)
  STREET                           VARCHAR2(20)

jim_at_OWINT4> select * from address;

FAMILY_NAME SUR_NAME

-------------------- --------------------
CITY                 ZIPCODE
-------------------- ----------

STREET

---

Miller               John
Berlin               1290

fleetstreet 12

Trueman              sandy
3445                 hamberg

lakestree

FAMILY_NAME SUR_NAME

-------------------- --------------------
CITY                 ZIPCODE
-------------------- ----------

STREET

---

jim_at_OWINT4> set linesize 80
jim_at_OWINT4> select * from address;

FAMILY_NAME          SUR_NAME             CITY                 ZIPCODE
-------------------- -------------------- -------------------- 

---

STREET

---

Miller               John                 Berlin               1290

fleetstreet 12

Trueman              sandy                3445                 hamberg

lakestree

jim_at_OWINT4> set linesize 200
jim_at_OWINT4> /

FAMILY_NAME          SUR_NAME             CITY                 ZIPCODE 

STREET

-------------------- -------------------- -------------------- 
---------- --------------------
Miller               John                 Berlin               1290 
fleetstreet 12
Trueman              sandy                3445                 hamberg 

lakestree

jim_at_OWINT4> spool off

I have been able to set linesize to 20000 without sqlplus complaining. This is on 10.1.0.2 sqlplus on Windows.

> The header I got more than
>once.
>
>I am trying it that way because I want to export tablerows thru an
>view. The tables belong to another schema.
>
>I am trying this under AIX5.2
>Maybe smeone has an idea?

If you are wrapping it in a Unix shell script, use grep or awk to remove the unwanted headers.

You could try the scripts from Tom Kyte that I already suggested. I hear he is quite good.
>
>Regards
>Carsten
>

-- 
Jim Smith
Because of their persistent net abuse, I ignore mail from
these domains (among others) .yahoo.com .hotmail.com .kr .cn .tw
For an explanation see <http://www.jimsmith.demon.co.uk/spam>

Received on Wed Aug 10 2005 - 03:53:24 CDT