To execute multiple SQL files in single unix shell script

Question

I have a SQL filequery.sql running thorugh a unix shell script and it directs an ouput to another SQL file called as result.sql

First file will give the sample output of below which will be written in the result.sql,

drop * from table1;
drop * from table 2;
drop * from table 3;  etc..

I need to execute both query.sql and result.sql in a single shell script and this should create a output file called as output.txt. How can I achieve this?

Ed Gibbs · Accepted Answer · 2013-05-29 14:25:07Z

1

Something on this idea should work:

sqlplus -s username/password@servername << EOF
@query.sql
spool output.txt
@result.sql
spool off
EOF

edited May 29, 2013 at 14:25

answered May 28, 2013 at 16:44

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chandru Over a year ago

Hi, Thanks for your comments. Actually the result.sql is the output of the query.sql. here i need to execute the first query file (query.sql) and to get the output in the second file(result.sql). And this two files needs to be executed in the same shell script file and output of the result.sql to be written in the output.txt file.

Chandru Over a year ago

Hi @Ed, My first file query.sql contains the below CONNECT username/password@SID whenever SQLERROR EXIT 1; set linesize 32767 set heading off set termout off set trimout on set pagesize 50000 set trimspool on set feedback OFF SET SQLBLANKLINES ON --set markup html ON echo OFF spool /tmp/chan/result.sql SELECT CONCAT(CONCAT('select count(*) from ',TABLE_NAME),';') AS QUERYSET FROM USER_TABLES WHERE REGEXP_LIKE (LOWER(TABLE_NAME),'^bpjd_job_dependency_(pre|pst)_') order by TABLE_NAME desc; spool off; exit;

Ed Gibbs Over a year ago

Your query.sql seems solid. I've moved the spool output.txt in my answer above so it comes just before the @result.sql; therefore it will only capture output from when you run result.sql. You'll also want to specify your folder when you run it: @/tmp/chan/result.sql.

Collectives™ on Stack Overflow

To execute multiple SQL files in single unix shell script

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related