Re: Insert value into Oracle table that has a ' in it

gonkowonko_at_gmail.com wrote:
> I need help
>
> I want to insert a value into a ORacle table that has a ' in it for
> example "Children's"
>
> i do a replace in VB to
> courseName = Replace(courseName, "'", "''") and this works for SQL
> server but doesnt work for oracle as i get the error message ORA-00917
> missing comma error.
>
> I have searched and search but cant find nothing to help so please if
> anyone has advice please let me know
>
> Gaz

I have seen your follow up, but you are likely writing sub-standard code against Oracle. Your question is only really likely to arise if later you issue code like the following

strSQL = "select column from tab where course_name = " & courseName

...

rst = cmd.execute(strSQL)

You should be writing this as

strSQL = ""select column from tab where course_name = :courseName" cmd.parameters.add("courseName")
cmd.parameters.("courseName").value = courseName

rst = cmd.execute(strSQL)

that is rather than using string substitution using bound variables.

If you are doing the first then every execution of your program will require the sql statement to be reparsed and reoptimized before execution. Using bind variables this should only be done the once. In addition you'll gain the following useful benefits.

1. You can throw away your checks for special characters.
2. You protect yourself against sql injection
3. You can ensure that the right type of parameter is passed to the sqlstatement.

Not using bind variables is the number one sin that developers new to Oracle commit.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com