How to iterate over SQL result in shell script
We sometimes need to iterate SQL result in shell script to do something. For example, if we need to re-format data or need to run a Linux command against SQL result for each record, we need to iterate SQL result in shell script. In this article, we are going to run a SQL in Oracle and get the result, then iterate them in a shell script.Check SQL and its result
First off, let's see how SQL and its result look like.The following SQL gets a list of Oracle system tables by querying "all_tables" table.
Below is the SQL and its result. We are going to iterate the result in shell script.
SQL> SELECT TABLESPACE_NAME, TABLE_NAME, NUM_ROWS FROM all_tables WHERE TABLESPACE_NAME = 'SYSTEM' AND rownum <= 5; TABLESPACE_NAME TABLE_NAME NUM_ROWS ----------------- ------------------ ---------- SYSTEM ICOL$ 4221 SYSTEM CON$ 5944 SYSTEM UNDO$ 70 SYSTEM PROXY_ROLE_DATA$ 0 SYSTEM FILE$ 26
Create a shell script
The following shell script iterates SQL result records. Please create "test.sh" with the following contents.
#!/bin/bash
sqlplus -s username/password <<EOS | sed -e 's/[\t ]//g' | while IFS=',' read tablespace_name table_name num_rows ; do
SET FEED OFF
SET HEAD OFF
SET LINES 1000
SET PAGES 0
SET VERIFY OFF
SELECT
TABLESPACE_NAME || ',' ||
TABLE_NAME || ',' ||
NUM_ROWS
FROM all_tables
WHERE TABLESPACE_NAME = 'SYSTEM'
AND rownum <= 5;
QUIT
EOS
cat <<EOD
=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
[Table Information]
tablespace_name : ${tablespace_name}
table_name : ${table_name}
num_rows : ${num_rows}
EOD
done
What the script does is that run SQL and get the result, then the result is passed to pipe (|), so that the result is iterated.
Run shell script
Once we created the test.sh script, let's run. As a result of that, we can see how data is processed one by one in the shell script.$ chmod 744 ./test.sh $ ./test.sh =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+= [Table Information] tablespace_name : SYSTEM table_name : ICOL$ num_rows : 4221 =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+= [Table Information] tablespace_name : SYSTEM table_name : CON$ num_rows : 5944 =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+= [Table Information] tablespace_name : SYSTEM table_name : UNDO$ num_rows : 70 =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+= [Table Information] tablespace_name : SYSTEM table_name : PROXY_ROLE_DATA$ num_rows : 0 =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+= [Table Information] tablespace_name : SYSTEM table_name : FILE$ num_rows : 26
Please modify the shell script based on a requirement. We are also able to run like the shell script through cron job.
