How to run sql queries in SQL Developer or TOAD and extract result using bat file

Question

Is there a way through which i can execute SQL query in SQL Developer/TOAD and extract the result in any format using .bat file. I m working on a client machine so using any other software other than SQL Developer/TOAD is not a option. Please suggest how to create a bat file for the same.

Answer 1

If SQL Developer is installed I venture to guess so is SQL Plus. SQL Developer is simply a graphic interface to the database connections. Try opening a command window, I assume you can since you would like to run .bat programs, and typing in sqlplus. If this comes back with version numbers and a prompt for a user name you should be able to use this for your script.

See this answer on Stack Overflow for more tips on how to run .bat programs from SQL Plus with native SQL Plus spooling.

Answer 2

You can put the following types of sqlcmd statements into a batch file. After running the results are saved to a txt file. This example executes a SQL file already created and saves the results to a txt file.

Step 1: Create SQL file which you want to execute.

Step 2: Execute following sqlcmd command on prompt:

sqlcmd -i SQLFile.sql -S ServerLocation -E -o File.txt

If you are using username and password run following script

sqlcmd -i SQLFile.sql -S localhost -U username -P password -o File.txt

You can run something like this for SQL plus:

sqlplus user/pwd@mydb @SQLFile.sql > File.txt

Answer 3

What version of Toad? If the version of Toad you have has the Automation Designer then you can setup an action to export query results to many different formats. See my answer in this question for steps to export query results to XLS. In step #3 you can choose other formats. Your configured actions can be scheduled or executed by .bat file. Toad's help covers command line execution of these actions.