Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Not able to execute many SQL scripts within a shell script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Not able to execute many SQL scripts within a shell script
# 1  
Old 06-03-2010
Not able to execute many SQL scripts within a shell script
I am using HP-UX:

I have written a ksh script where I need to connect to sqlplus and execute few sql scripts. Part of this code is -

Code:
sqlplus user/temp1234 <<!

set serverout on
set feedback off
set pages 1000
set colsep ,
set echo off

spool /home/supp1/pks/output.csv
@/home/supp1/pks/a1.sql
@/home/supp1/pks/a2.sql
@/home/supp1/pks/a3.sql
@/home/supp1/pks/a4.sql
@/home/supp1/pks/a5.sql
@/home/supp1/pks/a6.sql
@/home/supp1/pks/a7.sql
@/home/supp1/pks/a8.sql


spool off
exit
!

The problem is only a1.sql is executed and disconnecting from sqlplus. Any idea why remaining scripts a2 to a8 are not being executed ?
Is there any error in the syntax?
Last edited by Scott; 06-03-2010 at 10:37 AM.. Reason: Code tags, please...
# 2  
Old 06-03-2010
Hi.

The syntax looks fine.

Does a1.sql file have an exit in it?
# 3  
Old 06-03-2010
No. 'a1.sql' has commit at the end
# 4  
Old 06-03-2010
Quote:
Originally Posted by Sriranga
...
Code:
sqlplus user/temp1234 <<!
 
set serverout on
set feedback off
set pages 1000
set colsep ,
set echo off
 
spool /home/supp1/pks/output.csv
@/home/supp1/pks/a1.sql
@/home/supp1/pks/a2.sql
@/home/supp1/pks/a3.sql
@/home/supp1/pks/a4.sql
@/home/supp1/pks/a5.sql
@/home/supp1/pks/a6.sql
@/home/supp1/pks/a7.sql
@/home/supp1/pks/a8.sql
 
 
spool off
exit
!

The problem is only a1.sql is executed and disconnecting from sqlplus. Any idea why remaining scripts a2 to a8 are not being executed ?
...
Post the contents of the following files:

Code:
/home/supp1/pks/output.csv
/home/supp1/pks/a1.sql
/home/supp1/pks/a2.sql

Also, have you posted the entire content of this script ? Is there a "whenever sqlerror..." or "whenever oserror..." sqlplus command in your script ?

tyler_durden
# 5  
Old 06-03-2010
Sorry guys, can't share the complete script( its illegal :P). I just verified the code and there are no "whenever sqlerror..." or "whenever oserror..." sqlplus command in the script.
# 6  
Old 06-03-2010
Interchange the order of the .sql files and check whether it will struck at the end of first .sql file execution.
# 7  
Old 06-03-2010
Quote:
Originally Posted by Sriranga
Sorry guys, can't share the complete script( its illegal :P). I just verified the code and there are no "whenever sqlerror..." or "whenever oserror..." sqlplus command in the script.
Fair enough Smilie

Is it SQL or PL/SQL?

If the latter is it being executed?

i.e.
Code:
BEGIN
 ....
 ....
EXCEPTION
  WHEN .....
END;
/
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script to execute sql query.

Hi Experts, Need your support. Not able to use sql query alias in shell script. Could you please help me in finding right way to use alias with sql query in shell script. Below is the code i am using. #!/bin/bash sqlplus -s abc/abc@abc << EOF> bcd.csv set trimspool on select zone_id... (4 Replies)
Discussion started by: as7951
4 Replies

2. Shell Programming and Scripting

Help with shell script to execute a sql cursor

Hi All, I want some help in writing a shell script which will be in the below form: A logical representation of shell scripts: ->Shell scripts start ->Connect to database ->Execute a sql and store the output in a cursor(need to know is there any concept of cursor in unix) ->Using a loop... (2 Replies)
Discussion started by: Chitragupta
2 Replies

3. Shell Programming and Scripting

How to execute a sql querry from shell script?

hello forum , I am new to shell scripting. as part of my assignment i have to execute a sql querry from the shell script. please help me. example. sql queerry:db2 "select emp_id from tsble emp". i have to execute the above querry from the script. advance thanks to forum Siva ranganath (1 Reply)
Discussion started by: workforsiva
1 Replies

4. UNIX and Linux Applications

how to execute multiple .sql scripts from within a shell script using sqlplus

using sqlplus I want to execute a .sql script that has dbms_output statments in rhe script. I want to write the dbms_output statements from .sql file to a log file. is this possible. thanks any help would be appreciated :wall: (1 Reply)
Discussion started by: TRS80
1 Replies

5. Shell Programming and Scripting

Need to execute different scripts with shell script

Hi All, Am in need of some inputs to overcome the following problem, A tar file(/var/execute/scripts/ that contains different types of scripts(may be perl,shell, python etc...). I have written a shell script which is located @ /var/execute.sh to untar the file in some location say... (1 Reply)
Discussion started by: SMNK
1 Replies

6. Shell Programming and Scripting

Execute multiple SQL scripts from single SQL Plus connection

Hi! I would like to do a single connection to sqlplus and execute some querys. Actually I do for every query one connection to database i.e echo 'select STATUS from v$instance; exit' > $SQL_FILE sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT echo 'select VERSION from v$instance;... (6 Replies)
Discussion started by: guif
6 Replies

7. Programming

how to execute the sql quires from shell script?

hi all, I'm new to shell scripting, i want to know how to use the sql statements from the shell script? is there any documents or pdf to learn from the begining? plese provide me some useful links on the same. Thanks all in advance..,:b: (3 Replies)
Discussion started by: vij_krr
3 Replies

8. Shell Programming and Scripting

Shell Script to execute SQL's

Hi people, i need youre help on this. I need to create an Shell SCript to list in a directory some sql files and then execute them one by one . Also i need to have Logs from the executed Sql's to send to our costummers. Can anyone help me on this Regards, osramos (2 Replies)
Discussion started by: osramos
2 Replies

9. 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

10. Shell Programming and Scripting

How to execute a .sql file with shell script

hi everybody... can anyone help me in executing the .sql file with shell scripting.... thanx in advance (2 Replies)
Discussion started by: abuanas
2 Replies
Login or Register to Ask a Question