Re: How to get a variable length ASCII file from SQL*PLUS output

Bu Yun Fei <yfbu_at_ctlsg.creaf.com> wrote in article <01bccede$df8c32a0$76365fc6_at_buyunfei.ctlsg.creaf.com>...
> Hi,
>
> I'm currently doing data conversion from Oracle database and I need to
> output data to a variable length ASCII file, which means at the end of
> each record inside the file, there shouldn't be any trailing space
> behind. It seems to me that the output from SQL*PLUS is always fixed
> length.

Yeah - that's a bitch because of SQL*Plus using the LINESIZE parameter to determine how many bytes to output per line.

> Appreciate if anyone could tell me how to convert a fixed length text
> file to a variable length file by using certain UNIX command.

I've always been able to load variable length records in a fixed length file without problems in Oracle and SQL-Server. But if you do need to convert the file, you may want to look at the Unix sed command. Maybe you can replace all trailing spaces with nulls?

Or you can pipe the file thru awk. If the field seperator is a comma you can make FS=, and do a '{print $1 $2 $3}' if you have 3 columns per line. The last $4 (or $n) field in awk will contain the trailing spaces. Pipe the awk output to file and you should have a variable length file.

The other alternative is to write a small C program to do it.

regards,
Billy Received on Fri Oct 03 1997 - 00:00:00 CDT