Run different SQL on multiple DBs using SHELL script


Login or Register to Reply

 
Thread Tools Search this Thread
# 1  
Run different SQL on multiple DBs using SHELL script

Hi Experts,

I have a list of Dbs.In that DBs i need to execute some sql scripts.
each sql script is unique and it should run on particular DB only.
For example. i have DBs like MDC20V,NDC20V,ODC20V and sql scripts like MD.sql,ND.sql,OD.sql.so MD.sql should run only in MDC20V and ND.sql should run only in NDC20V likewise i have 50+ Dbs.I need a single shell script to run this scripts.
# 2  
You need to provide some more details - like what database it is & how you're accessing it, where the list of DBs is, where the SQL scripts are, etc.

Just as an example that might help - a very rough (untested) solution, assuming Oracle/SQLPlus, a file with SIDs, and SQL files (only) in a single dir, and the same DB user everywhere:
Code:
SQLDIR=/someplace/sqls
DATADIR=/someplace/data
LOGDIR=/someplace/log
DBUSER=someuser
DBPASSWD=horriblyinsecure

for DBSID in $(< "${DATADIR}/sids.lst" )
do
   SQLPREFIX=$(echo "$DBSID" | cut -c1-2)
   for SQLFILE in "${SQLDIR}/${SQLPREFIX}"*
   do
      sqlplus ${DBUSER}/${DBPASSWD}@${DBSID} <<EOH
set stuff
spool "${LOGDIR}/${SQLFILE}.out"
@${SQLFILE}
quit;
EOH
   done
done

Login or Register to Reply

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
Run a sql script on multiple $Oracle_SID at once and send an alert if reaches the threshold
jd_2000
I am trying to accomplish following tasks in my KSH script: Run a sql script on multiple $ORACLE_SID at once Sends an alert only if the threshold > 2 for any of the $ORACLE_SID Please advice as to how I can approach this!! Thanks in advance!... Shell Programming and Scripting
1
Shell Programming and Scripting
Run SQL thru shell script: how to get a new line when run sql query?
Kapom
Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database. I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :( What should I do? I'm not sure that...... Shell Programming and Scripting
2
Shell Programming and Scripting
Run Sql plus in shell script
shekhar2010us
Hello, I want to connect to ssh, run a query, and store that into a variable in the shell script. Also I need to pass the variable back to php to display the query results. I have created a public/private key pair for ssh connection and that is working fine. Also I am able to run query in the...... Shell Programming and Scripting
8
Shell Programming and Scripting
shell script to run .sql files
balireddy_77
hi Friends, Please help me in writing shell script to run list of sql files. database is Oracle 9i, unix os is solaris Requirement is 1. sql file must take two inputs a)feed id and b)business date 2.shell script must out put .xls or .csvfile as out put without trimming any column name and...... Shell Programming and Scripting
1
Shell Programming and Scripting
How to run an SQL script inside a shell
stevefox
How do I create a K Shell which would silently (without user input) logon to Oracle and run an SQL script? Any help will be greatly appreciated. Steve... Shell Programming and Scripting
1
Shell Programming and Scripting
UNIX and Linux

Unix (trademarked as the UNIX certification mark) is a very mature family of multitasking, multiuser computer operating systems that evolved from the original AT&T Unix. Unix development starting in the 1970s by legendary Bell Labs programmers Ken Thompson, Dennis Ritchie, and others. Unix was first targeted for the Bell System and AT&T licensed Unix to outside parties. In the 1970s time period, this lead to a variety of for-profit as well as not-for-profit Unix variants.
In the early days, this included the University of California, Berkeley (BSD), Microsoft (Xenix), IBM (AIX), and Sun Microsystems (Solaris). In the early 1990s, AT&T sold their Unix rights to Novell. In 1995 Novell sold their Unix business to the Santa Cruz Operation (SCO). The UNIX trademark was passed to The Open Group, a "neutral" industry consortium. The Open Group promoted the use of the UNIX trademark for certified operating systems that comply with the Single UNIX Specification (SUS). In 2014 Apple's macOS became the Unix version with the largest global install base and macOS remains the largest Unix-user base today.
Linux is a family of open source Unix-like operating systems based on the Linux kernel. The Linux kernel is an operating system kernel first released by Linus Torvalds on 17 September 1991. Linux distributions include the Linux kernel, system software and libraries. Popular free open source Linux distributions include Debian, Fedora, and Ubuntu. Commercial Linux distributions include Red Hat Enterprise Linux and SUSE Linux Enterprise Server. Linux may be freely modified and redistributed. Anyone on the planet may create a Linux distribution for any purpose.
Please enjoy and help our forum community by "showing your work" and posting your code, even when your code is not working as expected. To help others help you, please wrap your code blocks, sample input, sample output, error messages, and other data in CODE tags and wrap your short commands and short data objects in ICODE tags. We were all beginners in the beginning. If you have any questions about how to register or how to post, please contact us in Live Chat. Thank you and enjoy this "forever free" technical support community for UNIX, Linux and computer information technology in general.