Re: SQL*PLUS Format Question

On Tue, 02 Jun 1998 14:56:34 -0700, Andy Hering <andy_hering_at_forecross.com> wrote:

>Hi,
>
> I'm using a sqlplus script that will dump the contents of a table into
>an ASCII Flatfile. Everything works fine, except that the first column
>does NOT start at position one, but rather position 2. I tried several
>ways to resolve this, but nothing seemed to work. When I tried ltrim,
>it forced the column in position 1, but put a lot of spaces between
>column 1 and column2. My first column is a number column. Is there any
>way to get the SQL*Plus to start the output at position 1?
>
>Thanks,
>Andy
>
>

I've had exactly the same problem when I was trying to create an ASCII flat file with fixed-width fields. SQL*Plus always formats number columns with a leading space for the sign, even if it doesn't exist. One way I've used to solve the problem is applying the following function to each number field:

substr(to_char(field,'99'),2,2)

The field is of the format '99', i.e., I it to occupy 2 positions. The substr() function is used to eliminate the first space reserved for the sign.

I don't consider this a nice or elegant solution. The nice way would be to disable sign output, but although I've searched the documentation on SQL*Plus, I didn't find any way of doing it.

Parameters which were useful during the output to a flat file:

set colsep ''

set newpage 0

set space 0

set pagesize 0

set echo off

set feedback off

set heading off

set tab off

Hope this helps,

Nuno Guerreiro Received on Wed Jun 03 1998 - 00:00:00 CDT