Spool Fle with spaces all over the place 1

[uparna]

I have a small batch file that runs a query when the user invokes it. This batch file contains sqlplus commands and spools the results of the query into a file called "data.csv". The colsep is set to ',' and so i have a neat little excel-like report for this special user.


Now the problem is this :-
the spool file automatically pads each result cell to its table description. For eg.

Table Flowers :-
-----------------------
Create Table Flowers
(
Flower_ID    number(10),
Flower_Name  varchar2(20),
Flower_date  date
)

data in the table
--------------------------
1     Rose     10-Jun-05
2     Lily     12-Jun-05

query executed by the batch file :-
-------------------------------------
set trims on term off verify off line 10000  colsep ','

spool c:\data.csv
prompt flower_id,flower_name,flower_date 
select flower_id,trim(flower_name),flower_date from Flowers ;
spool off;

result stored in data.csv :-
----------------------------------
flower_id,flower_name,flower_date
[COLOR=red yellow]         [/color]1,Rose[COLOR=red yellow]                [/color],10-Jun-05
[COLOR=red yellow]         [/color]2,Lily[COLOR=red yellow]                [/color],12-Jun-05

as you can see , there are unsightly spaces before "1" and after "Rose"...the total space between commas is the column length -- ie the flower_id field will always have 10 chars (either numbers or padding spaces) in it. The Flower_Name is actually varchar2(255) so you can imagine how awkward it is in the csv file....with 251 spaces after "Rose" and "Lily".

I have tried everything from setting the COL format to A5 , or trimming all over the place....but there is no way i have been able to remove the annoying padding spaces.

I dont intend to touch the base table/create views on it because this is not something we can do in the prod database.

Do you have a solution for this? Please let me know.



Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Jayaram,

What you need is the SQL*Plus (v22.5.7) option:

set DWIMNWIS on

This is the "Do What I Mean, Not What I Say" option. Again, this will be in SQL*Plus version 22.5.7 (available Spring 2057).<grin>

The problem is, when you ask for a multiple-column list (as evidenced by one or more commas) in SQL (e.g. "select flower_id,trim(flower_name),flower_date"), then even though Oracle does not store in the database trailing blank spaces to the maximum column width, SQL*Plus still displays blanks to the maximum column width...And the SQL*Plus "set trimspool on" command applies only to trimming the blanks at the end of the SELECT list, not to individual columns.

Therefore, to achieve what you want, get rid of the commas and change your code to read:

select flower_id||','||trim(flower_name)||','||flower_date from Flowers ;

With the above code, you won't even need to change your colsep to ','.

Let us know if this option gives you joy.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[uparna]

bravo! bravo!

its slightly cumbersome , because the extract pulls out 121 cols (dont ask me who designed the table , i think Adam did).

But the joy of eliminating the damn spaces is worth it all.

Yes , Santa , it did give me joy. You really are "Santa" for me , many times over ..... your posts are as humorous as informative ( i will let the sarcasm pass me by).



Regards,
S. Jayaram Uparna .

 

[sem]

You may write a simple script to get all that columns from data dictionary. Something like:

set heading off trimspool on linesize 30 feedback off
spool cmd.sql
prompt SELECT
select decode(rownum,1,null,'||'',''||')
||'trim('||column_name||')'
from user_tab_columns
where table_name=upper('&1');
spool off

You probably need to refine this code as it may contain typos (did not try it).



Regards, Dima

http://seminihin.narod.ru

 

[uparna]

thanks sem! i did that yesterday for this and ....

"saved a bunch of seconds on my work management by switching to sem's method!"

(those in the US can relate this to the ad on tv),

Thanks again!

Regards,
S. Jayaram Uparna .