Re: Stripping of spaces in SQL Plus
Date: 1997/03/27
Message-ID: <333ad0f2.2361195_at_newshost>#1/1
Starting with the sqlplus that comes with version 7.2 and up you can issue:
SQL> set trimspool on
this will trim (remove trailing blanks) from lines written to the spool file.
On 26 Mar 1997 23:29:47 GMT, "Martyn Cavett" <cavett_at_globalnet.co.uk> wrote:
>Hi Alf -
>
>Yes I've come across the same situation when spooling to an output file
>from SQL*Plus.
>
>The problem is SQL*Plus pads lines out to the size specified by your
>linesize environment variable, typically 80 characters - this can be set
>using the 'SET LINESIZE n' SQL*Plus command. The output is not affected by
>anything in the select statement (annoying isn't it?!) - i.e. the problem
>does not originate in the select statement. . . (see note at end)
>
>POSSIBLE SOLUTIONS:
>1. I'm going to have a play at work tomorrow, but I suggest try having a
>SQL*Plus command:
>
>SET LINESIZE 0 (zero)
>
>at the top of your script, and see if that helps. Setting LINESIZE zero
>has other effects which slip my mind at the moment - have a look at the
>SQL*Plus manual, SET section
>
>2. Failing that, in our case we ended up using a work around by making our
>output file effectively comma delimited - by concatinating commas between
>each output column, including tagging one on the end, then using this as a
>source file for our target environment (EXCEL) as a .csv file. You end up
>with a blank column in the target environment but so what?! So, with your
>sample data:
>
>SELECT OWNER||','||TABLE_NAME||',' FROM ALL_TABLES;
>
>Of course this might be suitable for your application. . .
>
>I hope this has helped you out!
>
>--
> - Martyn Cavett
> Senior ORACLE Analyst
> LGT Asset Management
> London, UK.
> home: cavett_at_globalnet.co.uk
> "These are my own views, not those of my employees zzzzz...."
>
>ps. I've just thought this might actually require a bit of pseudo-dynamic
>sql which I'll have to get back to you on....ummm - I don't have access to
>our systems at home.
>
>Can you please email me at work if you need further assistance
>
>- work email: Martyn.Cavett_at_gtplc.com
>
>Alf-Kenneth Aabel <alf-ka_at_online.no> wrote in article
><5hb2g7$glb$1_at_bone.global-one.no>...
>> Hi.
>>
>> I'm wondering if anyone know how to strip trailing whitespaces from a
>> SELECT statement.
>>
>> results in
>>
>> RTRIM(OWNER||'-'||TABLE_NAME)
>> ---------------------------------------------------------------
>> SYS - DUAL
>> SYS - SYSTEM_PRIVILEGE_MAP
>> SYS - TABLE_PRIVILEGE_MAP
>> SYS - STMT_AUDIT_OPTION_MAP
>> SYS - AUDIT_ACTIONS
>>
>> The problem is that these lines are padded with spaces when spooled to
>> a file. I want them to be variable length rows with no trailing
>> spaces. Anybody know how this can be done in Server Manager or
>> SQLPlus? All help would be appreciated.
>>
>> Alf-Kenneth
>>
>>
>> _____________________________________________________________
>> Name: Alf-Kenneth Aabel
>> Title: Senior Software Engineer
>> Company name: PRIDE AS
>> Address: Wdm. Thranesgt. 77
>> 0175 OSLO
>>
>> Ph work: +47 22 20 12 50
>> Fax: +47 22 20 70 39
>> Pager: +47 965 41021
>> Email: alf.kenneth.aabel_at_pride.no
>> _____________________________________________________________
>>
>>
>>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Mar 27 1997 - 00:00:00 CET