Re: sql*plus column format remove whitespace

Turkbear <john.g_at_dot.spamfree.com> wrote:

>alok_bisani_at_yahoo.com (Alok Bisani) wrote:
>
>>Yes I know. But what is the format parameter to this command which
>>will give a trimmed output in SQL*PLUS query result? As far as I know
>>the only way to get a trimmed CSV output in SQL*PLUS is to query a
>>single string concatenating with ',' all the actual columns required.
>>But then that may have limitations to size of the string (4000 for
>>varchar2)??
>>
>>ed.prochak_at_magicinterface.com (Ed prochak) wrote in message news:<4b5394b2.0410281119.191766ff_at_posting.google.com>...
>>> alok_bisani_at_yahoo.com (Alok Bisani) wrote in message news:<f28905db.0410280225.23ca2f21_at_posting.google.com>...
>>> > Hi,
>>> > Is there a column format in SQL*PLUS to trim whitespace? For eg.
>>> > the following query output
>>> > SQL> select owner, table_name, tablespace_name, cluster_name from
>>> > all_tables where rownum < 2;
>>> >
>>> > OWNER TABLE_NAME
>>> > ------------------------------ ------------------------------
>>> > TABLESPACE_NAME CLUSTER_NAME
>>> > ------------------------------ ------------------------------
>>> > SYS UNDO$
>>> > SYSTEM
>>> >
>>> >
>>> > SQL>
>>> >
>>> > should be something like this.
>>> > SQL> select owner, table_name, tablespace_name, cluster_name from
>>> > all_tables where rownum < 2;
>>> >
>>> > OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME
>>> > ----- ---------- --------------- ------------
>>> > SYS UNDO$ SYSTEM
>>> >
>>> >
>>> > SQL>
>>> >
>>> > This is required to output a CSV file from a query. I can do a string
>>> > concatenation of all the columns with a comma in between, but sure
>>> > there is a 4000 or someother limit to that?
>>>
>>> There is a command for formatting, called, strangely enough, COLUMN.
>>>
>>> HTH,
>>> ed
>Set pagesize 0
>set linesize 1000
>set heading off
>set feedback off
>spool 'c:\myfiles\outpiut.csv'
>then
>Use the TRIM() function for each fields and spool out ot the text file..
>
>select trim(f1),trim(f2), etc..
>
>spool off
>
>
>
OOPs - forgot the set colsep part that other posters mentioned..

Also apparently forgot to spell check.. Received on Mon Nov 01 2004 - 16:10:06 CET