1. Create a .sql file using the below template
-- set the output file in the first SPOOL command
-- add your query in between the SPOOL sections

set echo off
set newpage 0
set pagesize 0
set space 0
 set feedback off
set trimspool on
set heading off
set linesize 555
SPOOL /path/to/your/output/file.txt
select ... 'replace this line with your query'  ;
SPOOL OFF
quit

2. Create a simple shell script

#!/bin/bash
sqlplus oracle_user/password @/path/to/your/sql_file.sql

3. chmod the script

 chmod +x oracle_sql.sh

4. Also to get sql*plus working you mayneed to set a few environmental variables

setenv ORACLE_HOME ..
setenv ORACLE_SID ..
setenv ORACLE_OWNER ..
setenv ORACLE_VERSION ..
setenv ORACLE_CONF ..
setenv ORACLE_BASE ..
setenv TNS_PORT ..
setenv ORACLE_ALERT_LOG ..
setenv TEMPORARY_TS TEMP
 setenv TWO_TASK ..
setenv ORA_LIB ..

5. then just add the oracle_sql.sh to your cron

6. done

#!/bin/bash

MUSER="-- Insert Db username here --"
MPASS="-- Insert Db password here --"
MDB="-- Insert Db name here --"
 FILE="/home/user/path/to/your/import_file.sql"
 
# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)
 
TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )
  
for t in $TABLES
do
    echo "Deleting $t table from $MDB database..."
    $MYSQL -u $MUSER -p$MPASS $MDB -e "drop table $t"
done

cat $FILE | mysql -u $MUSER -p$MPASS $MDB
echo "Sql imported"
 

simple way to add the date to the start of a file for archiving etc..

cp test.txt backup/`date +%Y%m%d`.test.txt

#!/bin/bash

INFILE=test.txt
OUTFILE=out.xml

for i in $(cat $INFILE) ; do

    #do some stuff
    echo item: $i

done

while read line
    do
        echo $line;
done < $INFILE

( echo "</oai_dc:dc>" >> $OUTFILE )