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
Hemanth Bitra Blog's 