Are you using Java/JDBC? If you are then just use a PreparedStatement rather than a plain old Statement and all your worries go away!
Maybe I should give an example. Instead of the following (which will break):
Use the following:
The JDBC driver knows that you're supplying a String parameter and takes care of any escaping required for you.
If you're not using Java, what technology are you using with Oracle?
Joined: May 31, 2004
Hi, Sorry for the late response.The different time zones are to blame!
I'm not using Java technology here. I'm using a workflow tool called PEGA which calls Oracle. Since database interactions are not very efficient through PEGA,we directly called the insert query without handling it through any method as suggested by Mark.
Since we want the inserts to be done as a background process without any user interaction, we are facing this problem.We will now have to handle it through scripting in the front end. This defeats our purpose but there doesn't seem to be any workaround.
Avi's example jogged my memory a bit. There is a problem in Oracle (or at least in sql plus) where the '' is only recognized if it is the last character in a string. I had to concatenate the string up to and including the quote with a string containing the rest.