A developer on my team tried to write a SQL script to populate a new database table for our system. One of the values in one of the columns needed an ampersand in the text. As you may already know, the ampersand is a special character in Oracle SQL scripts that represents a substitution variable and not the ampersand character itself. There was a lot of commotion with this new script. It first got passed to the DBA Team for implementation. Of course it did not work. So the script came back to the developer. Another developer and a DBA tried to help get it to work with no success. Then they called in a consultant who recommended we do not use an ampersand.
I figured this had gone on long enough. So I suggested escaping the character by placing a backslash before it. I also said they needed to add a SET ESCAPE ON to the beginning of the script to ensure the backslash was treated as an escape character. This got them past the ampersand problem. But this only brought them to the next problem with the script that prevented it from running.
The lesson from this exercise was that it is acceptable to write and debug scripts using advanced tools such as PL/SQL Developer. However when you perform your unit tests, you better run the scripts with the same program that will be used in Production (Oracle SQL*Plus). Otherwise you have no way to tell whether your tests are valid.
