Travis Travis - 1 year ago 108 Question and Oracle single quote auto escape

Normally ASP.Net (I think it's .NET doing it) will do magic with single quotes when calling a procedure. For example the following will work just fine...

dim aThing as string = "you're not as cool as your dog"
cmd.CommandText = "pkg_cool.s_you"
cmd.Parameters.Add(New OracleParameter("iSomeVar", OracleDbType.Varchar2)).Value = aThing

The package has a simple update query

insert into someTable (colName),(iSomeVar)

Now if I do the same thing in ASP, but change the package to use
execute immediate
the single quotes need to be escaped; I did it in asp
. Can someone explain why?

-- the following will work fine if the asp variable "aThing" does not contain an apostrophe.
execute immediate
'insert into ' || vTableName || '(' || colName || ')values(''' || iSomeVar || ''')';

SQL Error: ORA-00917: missing comma

Answer Source

It's the RDBMS (Relational Database Management System) that escapes the apostrophe, not .NET. I'm guessing execute immediate uses the literal string because it's designed for assembling dynamic SQL.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download