Want to learn/use SQLPLUS in shell script

saurabhmehan · 09-15-2010, 12:54 AM

Hi All,

How i will use sqlplus in shell script?
Can any one provide sample code which explain following:
1. Connect to oracle DB
2. Exceute select * from tablename
3. Release connection to the DB
4. Append output in file everytime when query executes.

Thanks in advance

druuna · 09-15-2010, 02:08 AM

Hi,

You can use a HERE-DOCUMENT in a bash script:

Code:

#!/bin/bash

sqlplus -silent <<SQLScript
USER/PWD
set linesize 122
set pagesize  0
set heading off

spool /path/to/output.file

select * from tablename

spool off
exit 0
SQLScript

Also have a look at these links:
- http://www.dbsnaps.com/oracle/oracle...-bash-scripts/
- An Introduction to Linux Shell Scripting for DBAs

Hope this helps.