Shell Programming and Scripting

hi,

i am using below query to generate the fixed length txt file. this sql is being called from shell script.
This is supposed to be a fixed record file with the below definitions. There must be 2 byte filler after the CAT_ID AND each line should have total of 270 bytes.

Code:

field         length
------------------------
EMAIL_ADDR  1-255
CAT_ID      256-268
FILLER      269-270

issues
-------
1) i am not getting 270 byte file if there is only one record.
2) 3rd col doesn't have 2 byte length as it is null.the cursor doesn't go beyond 268 position.

please advise.


test.sql

Code:

set linesize 1000;
set newpage 0;
SET PAGESIZE 0;
SET ECHO OFF;
SET FEEDBACK OFF;
SET HEADING OFF;
SET VERIFY OFF;
whenever sqlerror exit failure;
whenever oserror exit failure;
alter session enable parallel dml;
SELECT DISTINCT rpad(EMAIL_ADDR,255),rpad(CAT_ID,13),rpad(' ',3) filler
  FROM T1;

first off, 265 + 13 +3 = 271

Code:

SELECT DISTINCT rpad(EMAIL_ADDR,255), rpad(CAT_ID,15)
  FROM T1;

This will give 270 characters plus one "\n" newline character in UNIX, plus two characters in Windows: carriage return and newline.

I do not know how you define fixed length. It may or may not have carriage control
characters.