Help with SQL Shell script


 
Thread Tools Search this Thread
# 1  
Help with SQL Shell script

Hi,

I need to excute two .sql scripts sequentially within file.sh

sqlplus user/pass@db1 @/opt/Infor/Outbound_Marketing/7.2.2/custom/iadeploy1.sql

THEN

sqlplus user/pass@db2 @/opt/Infor/Outbound_Marketing/7.2.2/custom/iadeploy3.sql


Thanks,
Soph
# 2  
What happens when you just put them both in the shell script, as shown below? That makes the first one run, and then when it finishes, the second one starts running. Is there an error when you do that?
Code:
sqlplus user/pass@db1 @/opt/Infor/Outbound_Marketing/7.2.2/custom/iadeploy1.sql
sqlplus user/pass@db2 @/opt/Infor/Outbound_Marketing/7.2.2/custom/iadeploy3.sql

Do you want to run them at the same time? In that case, put the first one in the background, and the second one will start running immediately (in the foreground):
Code:
sqlplus user/pass@db1 @/opt/Infor/Outbound_Marketing/7.2.2/custom/iadeploy1.sql &
sqlplus user/pass@db2 @/opt/Infor/Outbound_Marketing/7.2.2/custom/iadeploy3.sql

# 3  
I've tried the first recommended option and not working - it runs the first "iadeploy1.sql" then tries the second "iadeploy3.sql" but it hangs with no error exception..

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

63
# 4  
Try with the && control operator in the file.sh

sqlplus user/pass@db1 @/opt/Infor/Outbound_Marketing/7.2.2/custom/iadeploy1.sql &&
sqlplus user/pass@db2 @/opt/Infor/Outbound_Marketing/7.2.2/custom/iadeploy3.sql


Is they work correctly and give the outputs from commandline when run individually ?
# 5  
Write a generic wrapper to run all the sql commands.(put the "sqplus" command in the wrapper which accepts dbname, sql file etc as parameters)
and call the wrapper twice in this file.sh script of yours with iadeploy1.sql and iadeploy3.sql as parameters. Run them in the background, by capturing the exit code.

If you have more than 2 sql scripts to run, I would recommend loop them using an array of sql filenames.
 

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #253
Difficulty: Easy
The Defense Advanced Research Projects Agency (DARPA) is an agency of the United States Department of Defense originally designed by the Jefferson Airplane Company.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to pass Oracle sql script as argument to UNIX shell script?

Hi all, $ echo $SHELL /bin/bash Requirement - How to pass oracle sql script as argument to unix shell script? $ ./output.sh users.sql Below are the shell scripts and the oracle sql file in the same folder. Shell Script $ cat output.sh #!/bin/bash .... (7 Replies)
Discussion started by: a1_win
7 Replies

2. Shell Programming and Scripting

Run SQL thru shell script: how to get a new line when run sql query?

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... (2 Replies)
Discussion started by: Kapom
2 Replies

3. Shell Programming and Scripting

pass null value to sql script from korn shell script

There are 4 parameters that I have to pass from korn shell to sql script. 1) I have to check if $1 , $2 , $3 and $4 are null values or not . How can I do that ? 2) Once its determined that these values are null (in the sense they are empty) how can I pass null values to sql script... (11 Replies)
Discussion started by: megha2525
11 Replies

4. Shell Programming and Scripting

LIKE in SQL shell script

sqlplus -s <<__END__ ${USER}/${PASS}@${DB} WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK WHENEVER OSERROR EXIT FAILURE ROLLBACK SET ECHO ON SET TIMING ON SET SERVEROUTPUT ON SIZE 1000000 select count(*) from table where start LIKE '10-JUN-10'; EXIT __END__ result=$? echo $resultThis gives... (9 Replies)
Discussion started by: sandy1028
9 Replies

5. Shell Programming and Scripting

How to grep sql error in shell script and exit the script?

I need help in the following script. I want to grep the sql errors insert into the error table and exit the shell script if there is any error, otherwise keep running the scripts. Here is my script #!/bin/csh -f source .orapass set user = $USER set pass = $PASS cd /opt/data/scripts echo... (2 Replies)
Discussion started by: allinshell99
2 Replies

6. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

Hi, I need help urgently for following issue. Pls help me to resolve this issue. I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql) I given... (6 Replies)
Discussion started by: praka
6 Replies

7. Shell Programming and Scripting

Calling SQL LDR and SQL plus scripts in a shell script

Hi- I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment... I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert... (5 Replies)
Discussion started by: rajagavini
5 Replies

8. Shell Programming and Scripting

Shell Script - SQL

Guys,Please look at a simple shell which validates presence of a customer table in OASIS schema. SQL Query for validation is wrong(Table name in quotes should not have schema name OASIS.). And shell is working correctly (with exit 1) as it will never get count=1 with this query. But it is... (2 Replies)
Discussion started by: bhush782003
2 Replies

9. Shell Programming and Scripting

SQL in shell script

Hi, I am able to insert the values into a database table from a flat file using SQL loader. Now can I do the same using only shell script.Can this be done without using sql loader and only with shell scripting(ie use of awk). Please put some light on this. Thanks in advance (3 Replies)
Discussion started by: sendhil
3 Replies

10. UNIX for Dummies Questions & Answers

sql in shell script

Hey I have this shell script that I am trying to run and receive a value from a sql query.Then later use the value in an if stmt... I cannot get this to work for nothing.. Here is the script (sqlplus -s $usrname/$passwrd <<! set head off feed off pagesize 0 select b from table; set head... (9 Replies)
Discussion started by: devaris22
9 Replies

Featured Tech Videos