Problem with string which contains an “&” in sqlplus

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.

Leave a Reply

Your email address will not be published. Required fields are marked *