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?

Improve this question

2 Answers 2

Reset to default
1

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
Improve this answer
1
  • 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! :) Commented Nov 5, 2015 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
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.