Travis Travis - 1 year ago 65 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


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.