Unix shell script to invoke .sql file

August 31, 2010 at 07:07:05
Specs: Windows XP
Hi,
Using unix shell script i need to invoke a .sql file to generate a report in excel format,then these files gets saved on one path. Then from that path i need to copy the excel sheet to another server path.
Can this be done...?? i am new to Shell Scripting.. Please advise if you have any solution.
Thanks in advance

Regards,
Prasanna


See More: Unix shell script to invoke .sql file

Report •

#1
September 3, 2010 at 01:08:44
create a file containing your SQL statments, let's say input.sql.
This file generates your output file:
in Oracle: spool output.csv; (1. SQL statement)
sql ... sql ... sql ....
spool off; (last SQL statement)
in MySQL: select ......... into outfile 'output.csv' fields terminated by ',';
Your script should do:
#/bin/bash
su - <dbadmin-id> -c "path-to-sqlplus < input.sql"
scp output.csv remotehost:/target-path

check output.csv if it's really in csv-format (I'm not sure reg. that aspect)

br

wollie


Report •

#2
March 5, 2011 at 06:50:53
# export your ORACLE_SID & ORACLE_HOME variables

$ORACLE_HOME/bin/sqlplus -s /nolog << EOF
set term off
set feedback off
connect username/password@db_connectstring
spool /Your_DIR/your_filename.csv
@/Your_DIR/Your.sql

exit
EOF


NOTE :
Your .sql query should contain quries like below to generate .csv file:
for example :
select name,open_mode from v$database ; should be writen as
select name || ','|| open_mode from v$database ;


Report •
Related Solutions


Ask Question