• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1869
  • Last Modified:

Connect to Oracle DB and execute sql statements through bash script

Hi Experts.

I'm trying to create a bash script(in linux) which should connect to an Oracle DB and execute a couple of sql statements. The script should look as follows;

1. Connect to DB oracle ON SERVER 10.10.10.10 with password and login.
2. Execute one.sql, two.sql and three.sql (files)
3. Give some feedback.

I'm novice in this field so please give me good pointers or example code.

Thanks!
0
jide85
Asked:
jide85
2 Solutions
 
woolmilkporcCommented:

Hi,
try something like this -

#!/bin/bash
/path/to/sqlplus [your_connect_string] >> /path/to/feedback.log 2>&1 <<EOF
@/path/to/one.sql

@/path/to/two.sql

@/path/to/three.sql  
quit
EOF
exit
 
wmp
0
 
DavidSenior Oracle Database AdministratorCommented:
Are the sql jobs sequential, so that if one fails the remaining abort?   What about your ORACLE_SID setup and selection?
 
0
 
omarfaridCommented:
try

sqlplus username/password >> /path/to/file.log 2>&1 <<EOF
spool
@/path/to/one.sql
@/path/to/two.sql
@/path/to/three.sql  
quit
EOF
0
 
jide85Author Commented:
Thanks you for your answers. And for those who are interested this is my final script:

#!/bin/bash
echo 'connecting to db...'
sqlplus "user/pass@(DESCRIPTION=(failover=on)(ADDRESS_LIST=(load_balance=on)(ADDRESS=(PROTOCOL=TCP)(HOST=ip1)(PORT = 1521))(ADDRESS=(PROTOCOL=TCP)(HOST=ip2)(PORT = 1521))) (CONNECT_DATA=(SERVICE_NAME=db1)))" << EOF
SPOOL
@MNCP_XML_DATA.sql
@SORT_SUBUNITS_EVENLY.sql
@SORT_UNITS_EVENLY.sql
@CHANGES.sql
quit
echo 'done'
EOF
0
 
jide85Author Commented:
=D
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now