Automate extraction using shell scripting

You can automate extraction using shell script and sql script in four steps

1. Create a sql file

SET LINESIZE 110
SET TERMOUT OFF
SET ECHO OFF
SET PAGESIZE 0
SET HEADING ON
SET FEEDBACK OFF
SET UNDERLINE OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET VERIFY OFF
SET ARRAY 500
SET WRAP ON
SPOOL /home/username/DateEx/extraction.dsv
SELECT ‘EMPNO;ENAME;JOB;MGR;HIREDATE;SAL;COMM;DEPTNO’ FROM dual;
select EMPNO||’;’||
ENAME||’;’||
JOB||’;’||
MGR||’;’||
HIREDATE||’;’||
SAL||’;’||
COMM||’;’||
DEPTNO
from emp;
SPOOL OFF

Save this script as filename.sql [Ex. auto.sql]

2. Create a shell script to connect sqlplus and run a script

#!/bin/bash
login=scott/tiger@pdborcl
sqlplus $login <<EOF
@auto.sql
exit;
EOF

Save the script as filename.sh [Ex. oracron.sh]

3. Now create another shell scripting so that we can schedule cron job

#!/bin/bash
. /home/username/oracron.sh 2>&1 > /home/username/DateEx/stderr.log

Save this file as filename.sh [Ex. crons.sh]

4. Configuring cronjob

To configure cronjob

enter below command in command line

$crontab -e

and schedule as per your requirement

Example of cronjob

For every day at 12 am

0  0  *  *  * /path/to/script [Ex. 0 0 * * * /home/username/crons.sh]

For 3rd day of the every month at 12 pm

0 12 3 * * /path/to/script

For weekly job for monday to friday at 12pm

0 12 * * 1-5 /path/to/script

For yearly once in december from 1st 10 days at 11 am

0 11 1-10 12 . /path/to/script

You can download the example file from this link : https://github.com/hemanth22/Cron-automation-extraction

In this blog i have used example table which is available in oracle database

Advertisements