#!/bin/ksh
#======================================================
# anonomous pl/sql
#======================================================
RETVAL=`sqlplus -s scott/tiger@dev <b><<</b>EOF
set serveroutput on
WHENEVER SQLERROR EXIT 1
declare
x varchar2(8);
begin
x := to_char(trunc(sysdate)-1, 'yyyymmdd');
dbms_output.put_line('the_result_is '||x);
end;
/
exit;
EOF`

echo $RETVAL
X=`echo $RETVAL | awk '{print $2}'`
echo $X

#======================================================
# sql query
#======================================================
sqlplus -s scott/tiger@dev <b><<</b>EOF | read RETVAL
set echo off heading off feedback off pagesize 0
WHENEVER SQLERROR EXIT 1
select to_char(trunc(sysdate)-1, 'yyyymmdd') from dual;
/
exit;
EOF

echo retval is $RETVAL

#!/bin/ksh
sqlplus -s scott/tiger@dev <b><<</b>EOF > tmp.txt
set pagesize 0 feedback off verify off heading off echo off
SELECT table_name from user_tables where rownum < 6;
exit;
EOF

while read reslt_line
do
  echo "==>"$reslt_line
done < tmp.txt