Programming

#!  /bin/ksh
 #  Author : Saptarshi
 # Date  : 18-Mar-2011
 #  Version : 1.0
  
 Return_op=`sqlplus -s  <<ENDOFSQL
 db_user/db_pass@db_nm
 
 WHENEVER SQLERROR EXIT 1
 
 set  serveroutput on size 1000000
 set  echo off
 set  feedback off
 set  heading off
 set  pages 0
 set  pagesize 1000
 set  linesize 300
 set  verify off
 
 declare
   v_nm  implmnt_dim.implmnt_nm%type ;
   v_cc  implmnt_dim.implmnt_id%type ;
    c_ref_nm sys_refcursor ;
 begin
   OPEN  c_ref_nm
    for
      select implmnt_nm, implmnt_id from implmnt_dim ;
      dbms_output.put_line(rpad(substr('Country Name',1,20),20)||'Country Code')  ;
      dbms_output.put_line('|') ;
      dbms_output.put_line(rpad(substr('------------',1,20),20)||'------------')  ;
      dbms_output.put_line('|') ;
    loop
      fetch c_ref_nm into v_nm, v_cc ;
      exit when c_ref_nm%notfound ;
      dbms_output.put_line('|') ;
      dbms_output.put_line(rpad(substr(v_nm,1,20),20)||v_cc)  ;
   end  loop ;
    end;
 /
 exit;
 ENDOFSQL`
 
 echo  $Return_op|tr '|' '\n'
 
 echo  'End of data extraction..'
 ~
 "test1.ksh" 46 lines, 1015  characters

ksh:0$  ksh test1.ksh
 Country  Name Country Code
  ------------ ------------
 
  Mexico  130
  Chicago 85
  Argentina 135
  Paraguay 136
  Chile  132
  Colombia 133
  Venezuela 134

Country Name        Country Code

---------------------        ------------
Mexico                  130
Chicago                 85
Argentina               135
Paraguay               136
Chile                     132
Colombia               133
Venezuela             134

varDate='date +...'
export spool_file=file_dir/spool_file_${varDate}.txt
db connection started
spool $spool_file
-- your db code here
/
spool off
exit;