Today I got a task to automate data extraction for reporting purpose from Oracle SQL Plus. My method for this automation is like below:
1. Create a batch file to call SQL Plus program.
2. The SQL PPlus program will generate a HTML report for my boss to view it.
As simple as that. And my tools required are:
1. A simple *.bat file (which to create a automatic daily folder to restore the report)
2. SQL Plus program
3. A sql script will able to produce a HTML report (this is quite tough one). I can post on how to do this maybe later.
So, my problem is, in my script there is a compulsory string [&]. Which you may already know, the string [&] is being used by ORACLE to identify variable.
This is the solution:
SET DEFINE OFF
This command line needed to be put inside the sql file before reaching [&] string, and it will stop sqlplus interpreting [&].
Thats all for today.