Removing leading and trailing spaces from Oracle SQL*Plus spooled output

Oracle's SQL*Plus application spools fields with spaces even if you try to trim the fields, because SQL*Plus is "doing you a favor" by making the fields line up. One way to work around this is to concatenate all fields into one big field so that SQL*Plus spools one big field. However, when I tried this with some very large fields, I got an error implying that I was concatenating too many characters. So how to work around this?

Here's what I did: I let SQL*Plus spool a tab delimited file with leading and trailing spaces (trailing blanks for all fields except for a field which uses STRAGG and had one leading space). Then I created an awk script to remove the leading and trailing spaces with code similar to the following:

#!/bin/awk -f
BEGIN { # Initialize section
FS="\t" # Input field separator
OFS="\t" # Output field separator
}
{
   for (i=1; i<=NF; i++ ){ # For all fields
    sub(/^ +/, "", $i) # Delete leading spaces
    sub(/ +$/, "", $i) # Delete trailing spaces
   }
   print $0 # Print all fields to output
}

Based on what I saw on other web pages on the Internet, I thought that, instead of using the two sub statements, I could just use $i=$i to remove leading and trailing spaces, but that didn't work for me. Maybe my version of awk doesn't support that.

To call this awk script, I just needed to add one line to my shell script which calls the awk script; the line looks similar to this:

awk -f awkscript oldfile.txt > newfile.txt