CODEWITHSUNDEEP
bash
Ranjeet
Ranjeet

Reputation: 375

How to iterate sql query result set in bash script

I am trying to write a bash script which will iterate through output of below sql query (we are using oracle database). Depending upon value of module column, we need to pass value of any one of the below columns to curl call. Columns - process_instance, parent_process_instance, root_process_instance.

SQL query:

SELECT PROCESS_INSTANCE, PARENT_PROCESS_INSTANCE, ROOT_PROCESS_INSTANCE, STATUS, MODULE, START_TIME FROM PROCESS_INFO WHERE STATUS='pi_halted' ORDER BY START_TIME DESC;

Till now I have came up with below bash script. Can you please help me to iterate the result set so that I can pass required value depending upon module column value.

#!/bin/bash
#Update below properties as per enviornment
export ENV=DEV
export DBHOST=localhost
export DBPORT=1737
export DBSID=TEST01
export DBUSER=TESTUSER
export DBPASS=TESTUSER
export LOGDIR=/usr/app/$USER/data/logs

#-------------------------------------------------------------------------
#----------- DO NOT EDIT AFTER THIS LINE ---------------------------------
#-------------------------------------------------------------------------

#create directory structure if not exists for storing log files
mkdir -p $LOGDIR/process_instance_data
mkdir -p $LOGDIR/process_instance_data/logs
mkdir -p $LOGDIR/process_instance_data/logs/halted
export halted_process_instance_logfile=$LOGDIR/process_instance_data/logs/halted/log_$(date "+%Y%m%d%H%M%S").log

#execute sql query
echo
echo "Enviornment : $ENV"
echo
echo "Connecting to - $DBUSER/$DBPASS@$DBHOST:$DBPORT/$DBSID"
echo "Getting halted process instance data logged before : $(date -d "1 days ago" +%d/%m/%Y) 00:01:00"
echo
sqlplus -s $DBUSER/$DBPASS@$DBHOST:$DBPORT/$DBSID << FIN_SQL > $halted_process_instance_logfile
set head off
set line 1024
set pages 9999
SET SERVEROUTPUT ON;
SELECT PROCESS_INSTANCE, PARENT_PROCESS_INSTANCE, ROOT_PROCESS_INSTANCE, STATUS, MODULE, START_TIME FROM PROCESS_INFO WHERE STATUS='pi_halted' ORDER BY START_TIME DESC;
SET SERVEROUTPUT OFF;
FIN_SQL

echo "Please check log file for details : $(readlink -f $halted_process_instance_logfile)"

Upvotes: 0

Views: 660

Answers (1)

Bash Stack
Bash Stack

Reputation: 519

basically parse your output file with

cat $halted_process_instance_logfile |while read variablename;do myfoo ${variablename} ;echo foo done;done

  • in normal mode this will work line-by-line ( Internal Field Separator IFS='\n') , but you can change IFS or use multi-line sed

  • be aware that while() is a subshell and thus cannot write to the variables in it's parent (your script) , so you might also do

    for "$(cat $halted_process_instance_logfile )" in variablename;do myfoo ${variablename} ;echo foo done;fi

Upvotes: 1

Related Questions