UNIX Desktop Questions & Answers

set colsep '   '       -- that is: a tic a tab a tic; you cannot see a tab; this makes csv format
set head off          -- don't print the header crud
set pages 0 
set lines 999          -- do no wrap on col 80, this can be set larger
set trimspool on
set feedback off
spool myoutputfile.txt
select fld1, fld2, fld3 from mytable   -- obviously this is made up
   where fld3='something';
spool off

sqlplus myusername/mypassword@mydbname
@one
exit

#!/bin/ksh  # or #!/bin/bash 
  sqlplus myusername/mypassword@mydbname  <<EOF
  @one
  exit
EOF

chmod +x one.sh

./one.sh

 
def make_csv()
sqlstr = <<-'SQL'
select SITEID, CELLID, "Cell Identity" from
(
select ce.siteid, ct.plancell as "CELLID", ct.allocatedci as "Cell Identity", ct.status
from mtnis.CI_translation ct
left outer join cell ce
on ct.plancell = ce.cellid
)
where status in ('Operational','Ready for service')
SQL
f_out = File.new("/tmp/#{@module}.sql", "w+"); f_out.puts sqlstr; f_out.close
result = `cat /tmp/#{@module}.sql | sqlcsv #{@verbose} \
--headers \
--user="#{TNUSER}" \
--password="#{TNPASS}" \
--dsn="#{TNDSN}" \
--stdin \
2>/dev/null`
#> #{@dsxdir}csv/#{@module}.csv.tmp1`
FileUtils.rm( "/tmp/#{@module}.sql", :force => true )
puts result if @verbose

@arri = result.split("\n")
end

 
select * from importcell

sqlplus -s username/passwd@dbname <<EOF
set colsep '   '
set feedback off
set lines 9999
spool results.csv
set trimspool on
set pages 999
select * from importcell;
exit
EOF

#!/bin/bash


sqlplus -s datasafe/datasafe@DS61MTN <<EOF 
set colsep ' ' 
set feedback off 
set lines 9999 
spool /var/local/dsx/import/results.csv 
set trimspool on 
set pages 999 
select * from AFFECTLEVEL; 
exit 
EOF

#!/bin/ksh

touch tmpFile

sqlplus -s usr/passwd@sid << HERE | egrep -v "altered" > tmpFile
set feedback off
set linesize 9999
set colsep ","

select * from importcell;

HERE

sed -e "s/     //g" tmpFile | grep -v '^$' | grep -v "\-\-" > outfile.csv
rm tmpFile

 
$ ./dbconnect.sh
sed: -e expression #1, char 11: unknown option to `s'