You can use set esc to specify an escape character to SQL*Plus. This will often be a back slash (\). Then, if you have a variable e.g. $TAX_YEAR, which has a value of 2011, SQL*Plus will interpret $TAX_YEAR as 2011 but it will interpret \$TAX_YEAR as a string of characters with a value of $TAX_YEAR. I have an example somewhere demonstrating this and, when I find it, I will put it in a blog post. In the meantime, here is an example showing how NOT to use it. A few days ago, I was given a data patch to run. It contained a set esc / statement so the escape character was a forward slash (/). After I had run it, I found I could not reconnect to the database. To show how this happened, I need a couple of users:
SQL> conn / as sysdba
Connected.
SQL> create user andrew
2 identified by reid
3 /
User created.
SQL> create user andrewreid
2 identified by andrewreid
3 /
User created.
SQL> grant create session to
2 andrew, andrewreid
3 /
Grant succeeded.
SQL>
Then I connect as the first user, andrew. Note that esc is off at the start:
SQL> show esc
escape OFF
SQL> conn andrew/reid
Connected.
SQL> show user
USER is "ANDREW"
SQL>
Next I set esc /, just like the data patch did:
SQL> set esc /
SQL> show esc
escape "/" (hex 2f)
SQL>
Finally, I try to reconnect to the database. Oracle normally treats a forward slash as the separator between the username and password in a connect string. However, if the forward slash is the escape character, it does not do this any more. When this happened for real, I was unable to connect at all. For the example I have changed things to show exactly what happens. I try to connect as user andrew, who has a password of reid. Oracle treats the forward slash as the escape character and assumes I want to connect as andrewreid. I enter the password for andrewreid and login successfully. Then I run a show user command to verify that the username is really andrewreid, not andrew:
SQL> conn andrew/reid
Enter password:
Connected.
SQL> show user
USER is "ANDREWREID"
SQL>
