Task Automation

In today's IT world, you will hear the word Automation day in day out. What the hell is Automation and how do you do it? Automation is a process that takes over a manual approach and process the task itself. This article teaches you all the basics you will need to automate anything like establishing a database connection, running SQL queries, Stored Procedures, passing variables between Korn Shell and database. The concepts presented here mostly applies to database administrator, database developer, reporting guru and all other IT technical folks.

Best DBA automates everything, you should too and you do not have to be a DBA to automate. I automated more stuff when I was developer then as a DBA. There are few things DBA automate using management console or from script.

1 Database backup

2 Patch management

3 Alert and notification

4 Testing

5 Log Management

6 Configuration and much more..

Before beginning with examples,  I would like to start with my favorite quote from Bill Gates.

“I choose a lazy person to do a hard job. Because a lazy person will find an easy way to do it.” -Bill Gates
And I say: I choose a lazy person to do a hard job because a lazy person will automate it. 

Example 1: Establishing database connection

#!/bin/ksh

#
## Example: db connection using kornshell
##

## DB Credential
DB_USER='scott'
DB_PASSWD='tiger'
DB='DEV'

## DB connection
sqlplus -s $DB_USER/$DB_PASSWD@$DB

## End program
exit

Example 2a: Running SQL Script

#!/bin/ksh

#
## Example: db connection using kornshell
## delete_employee.sql has delete statement stored in same folder as this script.

## DB Credential
DB_USSER='scott'
DB_PASSWD='tiger'
DB='DEV'

## DB connection
sqlplus -s $DB_USER/$DB_PASSWD@$DB @delete_employee.sql

## End program
exit

The script makes a database connection to DEV and runs the sql file (delete_employee.sql)

Example 2b: Running SQL Script (Alternative to above)

#!/bin/ksh

#
## Example: db connection using kornshell
##

## DB Credential
DB_USER='scott'
DB_PASSWD='tiger'
DB='DEV'

## DB connection
delete_employee=`sqlplus -s $DB_USER/$DB_PASSWD@$db<<EOF
  DELETE FROM hr.employee;
         COMMIT;
  exit
EOF`
     
## End program
exit

Example 3: Passing Query result to a shell variable.

#!/bin/ksh

#
## Example: db connection using kornshell
##

## DB Credential
DB_USER='scott'
DB_PASSWD='tiger'
DB='DEV'

## DB connection
## DB connection
db_connection=`sqlplus -s $DB_USER/$DB_PASSWD@$DB <<EOF
                set pagesize 0
                set feedback off
                set verify off
                set heading off
                set echo off
                SELECT 'CONNECTED'
                FROM dual;
                exit
EOF`

if [[ $db_connection == 'CONNECTED' ]]; then
        echo "Database Connection Status: Success"
else
        echo "Database Connection Status: Failed"
fi

## End program
exit

Example 4: Writing query result to a file.

#!/bin/ksh

DEPTNO=55
SPOOL_FILE='emplooyee_data.txt'

## DB Credential
DB_USER='scott'
DB_PASSWD='tiger'
DB='DEV'

## spooling employee data
spool_query=`sqlplus $DB_USER/$DB_PASSWD@$DB</dev/null
                set pagesize 0
                set feedback off
                set verify off
                set heading off
                set echo off
                SPOOL emplooyee_data.txt REPLACE
                PROMPT EMPNO|FNAME|MGR|SAL
                SELECT empno
                        || '|'
                        || fname
                        || '|'
                        || mgr
                        || '|'
                        || sal
                FROM hr.employee
                WHERE where deptno=$DEPTNO;
                SPOOL OFF
                exit
        END`
## checking the size of spool file.
IF [[ -s $SPOOL_FILE]]; then
 echo "SPOOL COMPLETED"
else
 echo "SPOOL NOT COMPLETED"
fi

## program end
exit 

Note: >/dev/null works like termout off since termout does not work when using query inside shell.

Example 5: Running stored procedures, sql queries and more..

!#/bin/ksh
DEPTNO=55
SPOOL_FILE='emplooyee_data.txt'

## DB credential
DB_USER='scott'
DB_PASSWD='tiger'
DB='DEV'

running_procedure=`sqlplus $db_user/$db_passwd@$db</dev/null
   @truncate_employee.sql;
   exec hr.update_employee; 
        END`
## end program
exit

Example 6: Querying from Oracle System view ( v$database, v$session)

#!/bin/ksh
output=`sqlplus -s "/ as sysdba" <<EOF
       set heading off feedback off verify off
       select distinct machine from v\\$session;
       exit
EOF`

To prevent extraneous messages from being returned, the above scripts turn off the heading, feedback, and verify options of SQL*Plus. The query is executed and SQL*Plus is exited.

Note the double-backslash before the dollar sign in the view name. These are required escape sequences within the string: The first slash escapes the second slash, which escapes the dollar sign. Again, not pretty, but functional.

If you are going to be writing something that requires extensive database access, shell scripting is not the best option. It might be better to rewrite the script in PL/SQL, Perl (which uses syntax similar to that used in shell scripting), Python, Java, or another language of your choice.

Well, you can connect to database, run queries, functions, procedures, and pass data between shell and database. What can you do next?  Automate your manual task and spend more time on training yourself and on new projects. I ask you to update my example 3 to send a notification to your phone and also sends email whenever the database is down.