quote this

Quotes, quotes, quotes.

Frustration, SQL and crontab go together again.

The SQL script was running well but then I have to go and add a date to the crontab line.

Arrggghhh.

No execution. No errors. No output to file. Cron is usually quite nice and throws and error that finds its way via email to me. But not today.

What have I missed?

Aha. What was that thing Sandra Bullock said in the widely panned movie "The Net" ... "Escape the system". Well I would if I could or was willing to get paid less but this is the solution.

If you want to embed this sort of thing

date +%w

you have to escape the % character to avoid it being interpretted by cron

date +\%w

The full line with my script name scrubbed to avoid potential embarrassment and with a few extra things thrown in for confusion or good measure, depending on your experience.

54 22 * * 1-6 csh -c "setenv ORACLE_HOME $ORACLE_BASE/product/8.1.6;setenv ORACLE_SID instance1;rm /tmp/datafile_`date +\%w`.dat;sqlplus user/password @ /myscripts/extractData.sql `/myscripts/lastWeek.pl` 07:00:00 > /outdir/datafile_`date +\%w`.dat"

The script lastWeek.pl returns the date 7 days back from today in the format dd/mm/yyyy.

Sanity restored. Sleep ensuing.