SQL="SELECT LPAD(CUST_NUM,5,'0'), RPAD(CUST_DESCRIPTION,30,' ') from
CUSTOMERS order by 1,2;"
SQLSTRING="$SQLSTRING $SQL"
echo $SQLSTRING | sqlplus -s $DB_LOGON >> $OUTPUT_FILE
</SCRIPT>
- The output comes up like the following (the periods represent
spaces, the ~ tabs):
00001MAX'S.WONDERFULL.WORLD........
00002ATTN:~.WHAT.IS.THIS...........
00003TIME..TO........FIND..........
B) When I change the "SET COLSEP |\n" in SQLSTRING, I get the
following:
00001|MAX'S.WONDERFULL.WORLD........
00002|ATTN:...WHAT.IS.THIS..........
00003|TIME..TO........FIND..........
C) When I remove the "SET COLSEP \'\'\n" from SQLSTRING, I get the
following:
00001 MAX'S.WONDERFULL.WORLD........
00002 ATTN:...WHAT.IS.THIS..........
00003 TIME..TO........FIND..........
D) When I remove the "SET COLSEP \'\'\n" from SQLSTRING AND change the
SQL to
SQL="SELECT LPAD(CUST_NUM,5,'0')||RPAD(CUST_DESCRIPTION,30,' ') from
CUSTOMERS order by CUST_NUM, CUST_DESCRIPTION;" I still get the
following:
00001MAX'S.WONDERFULL.WORLD........
00002ATTN:~.WHAT.IS.THIS...........
00003TIME..TO........FIND..........
Things to note:
1) When I rerun the job, the TAB character consistently gets placed in
the same spot.
2) Sometimes it replaces two spaces, sometimes three.
3) I'd rather not do post processing as it's more resource consuming.
4) Using SQL*Plus: Release 9.2.0.1.0
5) I don't think that the DB contains the TAB in the description. If
it did, wouldn't it show up in B or C?
6) I'm trying to recreate a fixed length flatfile.
7) Lastly, it's not always just after a ":" , sometimes it is just the
middle of 5(or 6, or 7,...) spaces.
