Formatting issue

Shell Programming and Scripting
1

I am running this query in a SQL session :

info_dtls=`sqlplus -s  /  <<eof
whenever sqlerror exit 1
set feedback off;
set verify off;
set echo off;
set heading off;
select trim(a.cont_title)||'","'||trim(b.cpgrp_desc)||
'","'||trim(to_char(to_date('20100930','yyyymmdd'),'MM/DD/YYYY'))||
'","'||trim(to_char(to_date('20100930','yyyymmdd'),'MM/DD/YYYY'))||
'","'||trim(to_char(b.cpgrp_dt_start,'MM/DD/YYYY'))||
'","'||trim(to_char(b.cpgrp_dt_end,'MM/DD/YYYY'))||
'","'||trim(to_char(to_date('20100930','yyyymmdd'),'MM/DD/YYYY'))||
'","'||trim(to_char(to_date('20100930','yyyymmdd'),'MM/DD/YYYY'))
from cont a,cpgrp b
where cpgrp_cont_id_alias= '10400112'
and a.cont_num=b.cont_num;
eof` >>  ${LOG_FILE_NAME} 2>&1

All the date values are variables in the original script (and those were alson formatted to delete leading space using the sed command)and I am hard coding here for the ease of understanding.
The problem is that, each time the info_dtls is printing the result like this

HOMJR Levaquin Prpritry ACUTE","HOMJR Levaquin Acute Only","04/01/2008","09/30/2 010","04/01/2008","09/30/2010",
"04/01/2008","06/30/2010

Please, look at the date marked in bold.Every time I am getting this issue.
If I am commenting out the previous line, the next date getting formatted like this. That means, every second date record fetch is getting formatted with an unwanted space.
Please provide me a solltion.

2

Just an observation: the additional blank char is at position 80, which is the default line width for many programs. Maybe it is just a "line break in disguise" and you could get rid of it by adjusting the "size" of the "virtual paper" you are printing to.

I am not sure which SQL client you are using, in Oracles SQLPLUS this is done with "SET LINESIZE <n>" (line width) and "SET PAGESIZE <n>" (number of lines before page break), IIRC.

I hope this helps.

bakunin

1 Like