• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1879
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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