Tuesday, August 03, 2010

Effect of a SQL* Plus environment variable: set pagesize 0

Last night, I troubleshooted a ksh shell script, in which a variable is assigned a value that is returned from SQL* Plus commands. I found that without 'set pagesize 0', the value returned had an undesirable leading space. Below is a test case that demonstrate this effect.


SQL> select col1, col3 from t;
 
      COL1 C
---------- -
         1 Y


$ cat test3.sh 
#!/bin/ksh 
 
RET1=` sqlplus -s  / << EOF
set  echo off head off feed off verify off term off  
select col3 from t
where  col1=1;
EOF
`
 
echo "RET1  >>>>"$RET1"<<<<"
if [[ $RET1 == 'Y' ]]; then
  echo "Yes"
else
  echo "No"
fi
 
RET2=` sqlplus -s  / << EOF
set echo off head off feed off verify off term off 
set pagesize 0
select col3 from t
where  col1=1;
EOF
`
 
echo "RET2  >>>>"$RET2"<<<<"
if [[ $RET2 == 'Y' ]]; then
  echo "Yes"
else
  echo "No"
fi


$ ./test3.sh 
RET1  >>>> Y<<<<
No
RET2  >>>>Y<<<<
Yes

No comments: