SQL*PLus: Creating flat files works differently in 7.3.4 and 9.0.1

Columns in table stationbus:
  station varchar2(10)
  bus_no number(7)  

Creating a flat file via following SQL*Plus query,

set feedback off trimspool on verify off echo off heading off set linesize 30 pagesize 0 space 0

spool lameters.txt  

select '"'||rpad(station, 16,' ')||'",', substr( to_char(bus_no, '0999999'),2,7) from
stationbus
and rownum < 10
order by 1
/
spool off

GAVE these results in 7.3.4 Sql*plus

 -rpad 16station- ---------> this is just a comment for this posting.
"ABCDEF ",0610000

.
.
"XYZ ",1349000
GAVE these results in 9.0.1 SQL*Plus

"ABCDEF ",----- 32 blanks -------0610000
.
.
"XYZ ",----- 32 blanks -------1349000

How can I fix it ?

I can concatenate the 2 columns to make one long string which works

  select '"'||rpad(station, 16,' ')||'",'||   substr( to_char(bus_no, '0999999'),2,7)

or create aliases for columns and format the aliased columns which works

  select '"'||rpad(station, 16,' ')||'",' a1,   substr( to_char(bus_no, '0999999'),2,7) a2

but I have lots of flat file creation scripts and I am lookinig for a set command or similar and proper solution to get rid of the blanks. I am not sure if the blanks are from the column station or bus_no.

What has changed? There is a set command named sqlpluscompatibility but the range is between 8.1.0 and 9.2.0. Cannot set it to 7.3.4

Any ideas/suggestions except the above 2 are welcome.

Thanks.

Suresh Bhat Received on Mon Jun 28 2004 - 18:31:36 CEST