0

I can resolve this problem, but I don't understand why it happens.

My task is: (in ksh)

  • 1st step: Query a number from a DB with sqlplus,
  • 2nd step: Format some text for a mail,
  • 3rd step: send a mail with these.

This is a part of my code:

psnr() {
sqlplus -s USR/PASS@PROD << EOS
SET PAGESIZE 0
SET COLSEP ";"
SET FEEDBACK OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
  select max_number-last_number from postalslip
  where state = 'OPEN'
/
exit
/
EOS
}

I got a number, 1234567, when I call this function.

PSNR=$(psnr)

I write a word for a mail:

echo "Today's result is: $PSNR" > $MAILTMP

if I want to use this variable MAILTMP I must use tr -d '\040\011' what I found here, in stackexchange, 'cause the result without tr will be this:

od -c mailtmp.17872.txt
0000000   T   o   d   a   y   '   s       r   e   s   u   l   t       i
0000020   s   :      \t                               1   2   3   4   5
0000040   6   7  \n
0000043

That tab '\t' is what I don't understand: where does it come from, from sqlplus? Possibly DB specificity? (I checked in TOAD, both coloumns are number(18). Maybe the function in ksh have some special features?

2

I've also come across this unwanted whitespace when working with a different database. Clearly, shell scripts and tools like SQLPlus are the wrong thing to use for extracting data from databases but there are occasions when it's convenient.

My solution is to strip leading and trailing whitespace from the result:

psnr() {
  sqlplus -s USR/PASS@PROD << 'EOS' | sed -e $'s/^[ \t]*//' -e $'s/[ \t]*$//'
SET PAGESIZE 0
SET COLSEP ";"
SET FEEDBACK OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
 select max_number-last_number from postalslip
 where state = 'OPEN'
/
exit
/
EOS
}

If ksh doesn't support the $'...' string syntax it's easy enough to replace:

# Original style
sed $'s/^[ \t]*//'

# Alternative style
T=$(printf "\t")
...
sed "s/^[ $T]*//"

An alternate quick and dirty fix is to use the shell to strip unwanted whitespace. Leaving your psnr() function unchanged, instead of this:

echo "Today's result is: $PSNR" > $MAILTMP

you would write this:

echo "Today's result is:" $PSNR > $MAILTMP
  • Man! your solutions... I'm ruined. :) You are awesome, really! I tried to use sed but it hasn't worked, maybe my concept had an error. sed "s[\t[[g" Your fisrt solution is very elegant, but the second one... the double quote... this killed me. Very simple, very clever. Thank you! I can not format this comment, sry! :) – Apex Nov 5 '15 at 7:47
0
sqlplus <<'EOF' | perl -pe 's/\t//g'
Some queries here
EOF

This is provided space is tab. My sqlplus queries output just space and in csv format, so I use this to remove unwanted space

 

echo 'foo bar       ,baz      ,foobar   ' | perl -pe 's/ +,/,/g ; s/ +$//g'
foo bar,baz,foobar

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.