Suppress spaces at end of each line in spooled file that contains results from an Oracle SQL query running in a sh script
I am running an Oracle/SQL query in a ksh script as follows:
sqlplus -s usr/pword <<EOF
set colsep '|' heading off feedback off verify off pagesize 0 linesize 250 echo off
spool fileA.txt
@scriptA.sql
spool off
The file scriptA.sql generates a two column output, which I want to see in the following format
col1_val1|col2_val1
col1_val2|col2_val2
col1_val3|col2_val3
However, this is what I actually see in the spooled file:
col1_val1|col2_val1
col1_val2|col2_val2
col1_val3|col2_val3
Notice that there are a lot of spaces after col2. Please note that this is NOT a SQL issue, as I am using the correct rtrim/ltrim and ||'|'|| combination. This is a linesize issue. I have noticed that I can change the number of spaces after the last column in each line of the spooled file by changing the linesize value. Now the question: What value should I use for linesize so that there are no spaces after the col2 values ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try trim(col1_val1)||'|'||trim
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
a return at the end of each rows