I recently wrote some SQL INSERT scripts to add some information to a table I had created. The insert scripts were in a text file and I attempted to execute that text file in sqlplus with the @ directive. Everything ran fine but when it got to the "&" character, ORACLE prompted me for the new value from the command line. HUH?? I don't want a new value I want the string I entered. I tried escaping out the & with a "\" but that just now put a "\" in.
PROBLEM: How to insert a "&" character into a string in ORACLE sqlplus without being prompted to reenter the value.
SOLUTION: Place this command at the top of your text file: set scan off; This turns off the ORACLE defines and other similarly annoying features and allows your text file to run as you intended.
I am sure that ORACLE DBA's would say well of course but being a simple software engineer and only using sqlplus when I have to this was quite the annoying little problem.
So why was I need to insert a "&" well I really needed to insert a "≥" and "≤" into the string so greater than equal (≥) and less than equal (≤) signs would be outputted to the browser in my web app. After I overcame the above problem, I thought I was home free but alas I also need to set this as the value of a radio button. When I used value="≥" for my value Internet Explorer did just that and inserted the symbol instead of the string. So, when the greater-than-or-equal was passed through POST from the form, IE then converted it to another representation and by that time when I tried to insert it into the DB using JSP my prepared statement the whole thing would blow up. By blow up I mean that Tomcat would return some obscure error message which would be caught by my try{} catch{} and do me no good.
PROBLEM: How to pass a greater-than-or-equal symbol HTML representation (or other HTML entity) from ORACLE DB to a form and back successfully.
SOLUTION: I used the ISO-8859-1 decimal Code representation for the symbol. So instead of passing "≥" I passed ≥ which worked because IE did not convert it but would display the symbol as I needed.
This solution would also work if you need to display "≤" you could use ≤ and for any other symbol where the "&___" HTML format is needed. The moral here is good luck if you need to start passing HTML special characters as strings to and from a DB because the handling of those can be creative.