Bash - Execute Pl/Sql script from Shell script

Hi, I am Malathi Boggavarapu working at Volvo Group and i live in Gothenburg, Sweden. I have been working on Java since several years and had vast experience and knowledge across various technologies.

In this post we see how to build a bash script which executes pl/sql script, retrieve data and send it to graphite server using NetCat. We also go through various basic bash commands such as do/while, nc and also we use bash functions too.

NetCat is used to connect to the servers and stream out the data to them. You can visit wiki to get basic understanding about it. https://en.wikipedia.org/wiki/Netcat

To understand what graphite is visit the following link
https://graphiteapp.org/

Now let's start with pl/sql script to retrieve data inorder to send it to graphite server.

Pl/sql script

A table holds the size of each table exists in the database every 24hrs. The query is to get the latest record of each table and it's size and some other columns too.

We will see how to get the latest record of each table using pl/sql script. If you see below example table, the query should fetch records where log_time is equal to latest date grouped by object_name.

Example:

OBJECT_NAME    OWNER   LOG_TIME     NUM_ROWS    BYTES
table_1                      owner_1   2015-05-01          12000                 14000
table_1                      owner_1   2015-04-30          11000                 12000
table_1                      owner_1   2015-04-29          10000                 11000
table_2                      owner_1   2015-05-01          1000                   5000
table_2                      owner_1   2015-04-30          900                     4000
table_2                      owner_1   2015-04-29          800                     3000

How to query sql for latest record in the table

SET LINESIZE 32000;
SET PAGESIZE 40000;
SET HEADING OFF;
SET FEEDBACK OFF;
Set ECHO OFF;
SET LONG 50000;
SET VERIFY OFF;

select lower(sys_context('userenv', 'con_name')),
       t1.OBJECT_NAME "TABLE_NAME",
       t1.owner "SCHEMA",
       t1.LOG_TIME "LOG_TIME",
       t1.NUM_ROWS "NUM_ROWS",
       t1.BYTES "BYTES"FROM testdb_1 t1 INNER JOIN (select OBJECT_NAME, max(LOG_TIME) as latest_date from dbtest_1 group by OBJECT_NAME) t2
ON t1.OBJECT_NAME = t2.OBJECT_NAME AND t1.LOG_TIME = t2.latest_date
WHERE t1.NUM_ROWS is not nullORDER BY t1.OBJECT_NAME;
/

exit;

Now we will see how to write a bash script that execute the pl/sql 
script and loop through the results and echo them to the graphite 
server using nc command.

Loop through the records using bash do/while

#!/bin/bash

# written: Malathi Boggavarapu
# created: 2018-06-03
# purpose: Send tablesize metrics to graphite servers.

cd $(dirname $0)
graphitehost=localhost
graphiteport=8080


# Retrieves the data in question from the DB and passes it along.
function retrieveAndSendData()
{
    while read -a row
    do
        local connection_name=${row[0]}
        local table_name=${row[1]}
        local schema=${row[2]}
        local log_time=${row[3]}
        local row_count=${row[4]}
        epoch=$(date -d "${log_time}" +"%s")

        echo "Table name: $table_name ${epoch}" | nc $graphitehost $graphiteport;
echo "Schema : $schema ${epoch}" | nc $graphitehost $graphiteport;
echo "Log time: $log_time ${epoch}" | nc $graphitehost $graphiteport;
echo "Row count: $row_count ${epoch}" | nc $graphitehost $graphiteport;
     
    done < <(sqlplus -S "$1" @graphite_db_tablesize.sql)
}

retrieveAndSendData "malathib/secret@testdb_1"
retrieveAndSendData "malathib/secret@testdb_2"

Convert date formats in bash

In above bash script, we converted yyyy-mm-dd format to yymmdd. We did that using the command
epoch=$(date -d "${log_time}" +"%s")
log_time: 2018-06-03
output: 180603

#convert yesterday date to YYmmdd
date -dyesterday +%Y%m%d

# Mostly recommended
DATE='03 JUN 2018'
date -d"$DATE" +%Y%m%d

Hope it is helpful. Please post your comments below.
Have a nice day!