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 3The 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`
No comments:
Post a Comment