want to run multiple SQL script file in one go with in SQLPLUS

how to run multiple sql queries at once in oracle
execute all sql scripts in a folder sql server
how to execute multiple sql query in unix shell script
how to run sql script in sqlplus command line in windows
sqlplus run multiple commands
sqlplus run sql file
how to run sql script in sqlplus command line in linux
how to run sql script in sequence

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)

To execute multiple SQL files in single unix shell script, How do I run multiple SQL queries in Unix shell script? Running Scripts. The START command retrieves a script and runs the command(s) it contains. Use START to run a script containing SQL commands, PL/SQL blocks, and SQL*Plus commands. You can have many commands in the file. Follow the START command with the name of the file: START file_name

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.

, drop * from table1; drop * from table 2; drop * from table 3; etc.. Issue is because of SQLplus was running from the oracle client home and it was crashing on running the startup upgrade command. Run the sqlplus from the non-client oracle home and tried the commands then it works as expected. so resolution is to run the sqlplus from the main oracle home instead of oracle client home.

If 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

This Video will show you how to execute Scripts in Multiple Schemas through Command Prompt Duration: 5:46 Posted: Oct 18, 2017 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.

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

I would like to do a single connection to sqlplus and execute some querys. How to use for loop to execute multiple .sql files while using SQLPLUS for db connection. rpt_per_typ_c go GRANT SELECT ON sec_afs TO developer_read_only. You can also do this in your shell script. sqlplus /nolog <<EOF connect user/pass @run_some_file.sql select * from dual; EOF You might need to escape the ";" with a \.

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

Question: I am trying to run two SQL files in a DOS Windows script, but it hangs: sqlplus "/as sysdba" @C:\Users\Test\1.sql @C:\Users\Test\2.sql. How do I  With the right setup, you can even put a shortcut on your desktop or run a scheduled task, but even this is better if it runs directly on the server rather than a remote session. I run scheduled tasks on the server to call sqlplus for exports all the time. but usually one export script at a time. cmd file called export.cmd

sqlplus : multiple scripts in argument (batch execution) sql*plus. I have two scripts (test1.sql and test2.sql) that need to be executed one after the other. Wed, 13 August 2008 02:40 Go to previous message Go to next message I'm in the command prompt, I execute your code, sqlplus starts, then trows  Answer: To execute a script file in SQLPlus, type @ and then the file name. For example, if your file was called script.sql, you'd type the following command at the SQL prompt: SQL > @script.sql. The above command assumes that the file is in the current directory. (ie: the current directory is usually the directory that you were located in

Hi masters, I want to run 4-5 SQL queries one by one in SQL*Plus i.e. query1 query2 query3 Put them in begin and end clause save it as .sql file and run the file. If one query is wrong another one well execute because we are using GO in file. (for example like script.sql) sqlplus login/password@server @filename​. I need to run some scripts as sysdba and wanted to put them all in a script to run them automaticly so I have this sqlplus -s /nolog<<_EOF_ connect '/ as sysdba' @script1 @script2 @script3 @script4 @script5 _EOF_ I usually run the scripts in this manner but with the sysdba id nothing comes back sqlplus idname/password@sid<<_EOF_ @script1

Get a list of the scripts in the order you'd like to run them. Then, edit the file Oh, and use SQL/Plus. Reply never thought to do it to multiple sql files at a time. Run that bat file, as a result of that a bcp file will get generated in Temp folder. Then Create a another bat file named as Import_Data.bat with the following command: bcp.exe [Destination_DataBase_Name].[dbo].[TableName] IN "C:\Temp\TableName.bcp" -S "Computer Name" -U "SQL Server UserName" -P "SQL Server Password" -n -q Pause. And here we go!

Comments
  • have you tried windows batch file ? (open notepad, write the commands in sequence line by line) and then save the file as FileName.bat (make sure you have selected All Files option from bottom of the save dialogue. Running the batch file by double click shall do the trick. Or you may need a little advanced batch commands inside that bat file
  • @rahuljain: sorry, I have no idea what you are talking about.
  • i ran the batch file. all.sql file is created fine. i open this file this file have code @"D:\test_SQL\Patch03.01.486.txt" @"D:\test_SQL\Patch03.01.487.txt" @"D:\test_SQL\Patch03.01.488.txt" if i run file like @all.sql is this will working fine? i have to run this all txt files in prod
  • This helps me a lot, great answer
  • Fun fact: if you first create 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 execution
  • I tried this solution and it works. However i haven many files numbered file1.sql, file2.sql .. fileN.sql and they are listed in all.sql file like all.sql --@file1.sql --@file2.sql --... --@fileN.sql The problem is the files are not executed in the order in which they are listed. How can i force it to use the order of the files like listed in the all.sql file ? Thanks for your answer,
  • Similarly, if you want to concatenate all of the SQL files in a directory, you can use cat *.sql > temp.sql.