sqlplus - Access multiple query results from shell

In one of our earlier articles, we saw how we can execute a SQL query by connecting to sqlplus. In this, we will see how we can execute multiple queries and access the values inside shell.


Assume we have an EMP table with the below data.

SQL> select * from emp;

        ID NAME              SAL
---------- ---------- ----------
         1 Ram              1000
         2 Laxman           2000
         3 Blogger          3000

SQL>

The following is the sample code of executing 2 SQL queries:

$ cat test.sql
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SELECT  SAL FROM EMP WHERE ID = '&1';
SELECT NAME FROM EMP WHERE ID = '&2';
EXIT;
$

Now, we run the sqlplus command to check the output:

$ x=`sqlplus -s $USER/$PASSWD@$INST @test.sql 2 3`
$ echo $x
2000 Blogger
$ echo $x | awk '{print $1}'
2000
$ echo $x | awk '{print $2}'
Blogger
$

The above method becomes tedious once the number of queries inside the SQL file is more. Hence, the file based approach shown below will help us better. In the below example, all the SQL output is captured in the file.

SET SERVEROUTPUT ON
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SPOOL out.lst
DECLARE
S number(4);
N varchar2(20);
BEGIN

SELECT SAL INTO S FROM EMP WHERE ID='&1';
SELECT  NAME INTO N FROM EMP WHERE ID='&2';
DBMS_OUTPUT.PUT_LINE(S );
DBMS_OUTPUT.PUT_LINE(N );
END;
/
SPOOL OFF
EXIT;

In the above SQL, we are writing the output to a out.lst output file. Now, run the sqlplus command from the shell:

$ sqlplus -s $USER/$PASSWD@$INST @test.sql 2 3

The file out.lst contains the result. We can extract the salary and name from file as shown below:

$ sal=`head -1 out.lst`
$ name=`tail -1 out.lst`