I have to run multiple SQL script file in one go.
Like every time i have to write command in SQLPLUS
SQL>@d:\a.txt SQL>@d:\a2.txt SQL>@d:\a3.txt SQL>@d:\a4.txt
is there any way put all file in one folder & run all script file in one go without missing any single file like @d:\final.txt or @d\final.bat
There is no single SQL*Plus command to do that, but you can create a single script that calls all the others:
Put the following into a batch file
@echo off
echo.>"%~dp0all.sql"
for %%i in ("%~dp0"*.sql) do echo @"%%~fi" >> "%~dp0all.sql"
When you run that batch file it will create a new script named all.sql in the same directory where the batch file is located. It will look for all files with the extension .sql in the same directory where the batch file is located.
You can then run all scripts by using sqlplus user/pwd @all.sql (or extend the batch file to call sqlplus after creating the all.sql script)
all.sql and then put all *.sql files in it, you'll end up having all.sql pointing to all.sql) Therefore you better delete that reference to avoid eternal circular executionIf you're using gnu linux, you could use process substitution:
sqlplus USERNAME/PASSWORD@DOMAIN < <(cat a.txt a2.txt a3.txt a4.txt)
# ... or a for loop on input files, inside the process substitution
Alternatively, you can create a .pdc file and list your sql scripts:
-- pdc file
@a.txt;
@a2.txt;
@a3.txt;
@a4.txt;
and call sql plus:
sqlplus USERNAME/PASSWORD@DOMAIN < my_scripts.pdc
Some tricks and command can help you to generate master.sql file and you can run from that location.
c:\direcotory_location\dir *.sql /-t /b >master.sql
Go to the parent directory open master.sql open using notepad++ remove master.sql line and use regular expression to replace
\n with \n @
go to cmd From cmd
C:\root_directory\sqlplus user/password @master.sql
I find this process very convenient if i have 30 to 40 scripts placed in a single directory.
Use *.PDC extension file like this
install.pdc file content
whenever sqlerror exit sql.sqlcode
prompt started!
prompt 1.executing script 1
@@install/01.script_1.sql
prompt 2.executing script 2
@@install/02.script_2.sql
prompt 3.executing script 3
@@install/03.script_3.sql
prompt finished!
where @@install/ points in which directory is the SQL script located
It might be worth the time to write a shell script that runs multiple files.
#!/bin/ksh
sqlplus user/password@instance <<EOF
@a.txt
@a1.txt
exit
EOF
For more on the syntax, look into Here Document
here is similar solution but you do not have to iterate and to have special formated an sql file names. You compose an one sql file and run it once.
cat table_animal.sql > /tmp/temp.sql
cat table_horse.sql >> /tmp/temp.sql
cat table_fish.sql >> /tmp/temp.sql
sqlplus USERNAME/PASSWORD@DOMAIN @/tmp/temp.sql
For Windows try copy /b *.sql +x final.sql
sqlplus user/password @final.sql
