Re: How to create a Cobol flatfile in sqlplus

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: 2000/06/24
Message-ID: <39550483.CDF8AABE@0800-einwahl.de>#1/1

You can specify that the column separator is an empty string.

create table mstest (

	t varchar2 (5)
	, ii 	number (7, 2)
	, i number (3)

)
/

insert into mstest (t, ii, i) values ('M', 7000 + 77/100, 49); insert into mstest (t, ii, i) values ('K', 17000 + 17/100, 59);

set colsep ''
set pagesize 0
set trimspool on

column ii format a7
column i format a3

select

	t
	, to_char (ii * 100, 'FM0999999') as ii
	, to_char (i, 'FM099') as i
from
	mstest

/

yields

M 0700077049
K 1700017059

Martin

Manfred Friedrich wrote:
>
> I would like to create a flatfile out of sqlplus
> from the following table:
>
> SQL> desc mstest
> Name Null? Type
> ------------------------------- -------- ----
> T VARCHAR2(5)
> II NUMBER(7,2)
> I NUMBER(3)
>
> SQL> select * from mstest;
>
> T II I
> ----- ---------- ----------
> M 7000,77 49
> K 17000,17 59
>
> The Output which I would like to see is
>
> M 0700077049
> K 1700017059
>
> I tried:
>
> SET NEWPAGE 0
> SET SPACE 0
> SET PAGESIZE 0
> SET ECHO OFF
> SET FEEDBACK OFF
> SET HEADING OFF
> column i format 000
> column ii format 00000v00
>
> and the result is
>
> M 0700077 049
> K 1700017 059
>
> There is one extra space before each mumeric value.
>
> How can I avoid this?
>
> TIA.
>
> Manfred
Received on Sat Jun 24 2000 - 00:00:00 CDT