How to execute different select queries at once in Oracle database using SQL*Plus command line?

 (SQL*Plus) is a command-line tool for accessing Oracle Database using a terminal.

It enables you to enter and execute SQL, PL/SQL, and SQL*Plus commands to do the following things.

• Query, insert, and update data
• Execute PL/SQL procedures
• Examine table and object definitions
• Develop and run batch scripts
• Perform database administration

There may be a requirement that you need to execute several queries at once and write the output to a file.  That requirement can be fulfilled in the following way.

Create the script file

First, you have to create a script file adding the required SQL queries. You can create a script file in two ways.


1. Using SQL*Plus command-line

First you can connect into the Oracle database using following command by replacing <username>, <password>, <protocol>, <hostAddress>, <port>, <serviceName> tags by corresponding values.


sqlplus '<username>/<password>@(DESCRIPTION=(ADDRESS=(PROTOCOL=<protocol>)(Host=<hostAddress>)(Port=<port>))(CONNECT_DATA=(SERVICE_NAME=<serviceName>)))'


Then you can use the EDIT command which allows you to invoke the text editor and add required SQL queries


EDIT queries.sql


This will create a file named queries.sql in your current directory and you can add the file content, save and exit by pressing Esc to enter Command mode, and then type :wq .


2. Using default terminal


Use vi command to create a new file from scratch and add required SQL queries.

Add SQL queries to the file

As the first step, you have to add the following lines to manually alter the settings one time prior to your required queries.


set markup csv on;
set colsep ,
set headsep off
set pagesize 0
set trimspool on
set linesize 2
set numwidth 5


You can get a brief idea about these setting parameters from this article.

As the next step, we need to insert the spool command. The spool command is used to store the output of any query to the specified file. Since I need to save the Employee table content into a txt file named employee-data.csv I added the following command.


spool employee-data.csv


After that, we have to add the select queries to retrieve the information.


select * from data.employee where empName Like ‘%alex%’;
select * from data.employee where empName Like ‘%peter%’;
select * from data.employee where empName Like ‘%andrew%’;
select * from data.employee where empName Like ‘%jonny%’;
select * from data.employee where empName Like ‘%siril%’;



Important- If you are only adding the select queries, your output file only contains the result of the executed SQL query. Then you may in trouble finding out what are the results of a particular SQL query. To resolve that issue you can use the ‘prompt’ command to print some wording along with your SQL query results. It allows you to provide informative descriptions of what a script is about to do.


Ex -

prompt select * from data.employee where empName Like ‘%alex%’;
select * from data.employee where empName Like ‘%alex%’;



This will guide to print the same sql query in the output file and its results in the next line.

As the final command, you have to add the spool off command to close the file output.


spool off



You can save the file content and exit by pressing Esc and type :wq! And Enter.

After adding all the SQL commands, file content should be as follows.

Execute the sql script file

Now you can execute that script by running the following command in the SQL*Plus command line. Here we have to use the script file name without file extension after @ sign to execute the script file.


@queries


As the result of that execution, you could find a file named employee-data.csv in your working directory with the requested content from the employee table.