Re: Stripping of spaces in SQL Plus

[Quoted] 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

> _____________________________________________________________

> 

> 

>