4

What is wrong here please?

RETVAL=`sqlplus -s user/pass@DB <<EOF
SET TRIMSPOOL ON PAGESIZE 0 COLSEP , FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF              
SELECT process_id, source, destination, type FROM table WHERE process_id IN ('123','456');
EXIT;
EOF`
if [ -z "$RETVAL" ]; then
  echo "No rows returned from database"
  exit 0
else
  echo $RETVAL
fi

The output is:

123,a c,2 456,a c,5

and should be:

123, a, c, 2
456, a, c, 5
Preet Sangha
  • 64,563
  • 18
  • 145
  • 216
Leandro Toshio Takeda
  • 1,599
  • 3
  • 16
  • 15

1 Answers1

6

Did you try

 echo "$RETVAL"

the nature of unquoted variables interpreted on the command-line or in a shell scripts is to strip out "exteraneous" formatting. ;-)

IHTH

shellter
  • 36,525
  • 7
  • 83
  • 90
  • Here is the output now: 123 ,a c ,2 456 ,a c ,5 There are commas missing and breaking the lines... – Leandro Toshio Takeda Jun 28 '13 at 01:17
  • I can't tell from what you have posted here. Why don't you update your question to include the current code and output. I won't have time to help you debug this, so at least others can comment on the difference between the 2 versions. Good luck. – shellter Jun 28 '13 at 01:20
  • I was able to fix it with the "" but I can still see lot of spaces which I cannot see in the database. The TRIMSPOOL ON option is not working. I will check more here and maybe submit a new question. Thansk all!!! – Leandro Toshio Takeda Jun 28 '13 at 02:17
  • The real question is why are you "exporting" data from your DB? Can't you calculate what you want inside the DB and then emit only the final answer? If you have a valid reason to do line-by-line processing on the output (I assume that is why you want 2 lines of output here), then read up about shell pipelines. Something like `plsql .... | while read line ; do echo $line ; done` might help. Good luck! – shellter Jun 28 '13 at 11:59
  • To fix the extra spaces, read about `set column ... `. Good luck. – shellter Jun 28 '13 at 13:37