oracle function for escaping

Question

I would like to know if there is an Oracle function to escape special characters.

Here is the example :

request := 'update tvire set nbqe = ''' || var_my_value  
dbms_sql.parse(curseur_ref_2, request, dbms_sql.native);
response := dbms_sql.execute(curseur_ref_2);

if var_my_value contains quotes, there is an error during execution.

Is there an Oracle function that prevent that : xx(var_my_value)

thank you

Answer 1

Use bind variables and execute immediate:

execute immediate update t1 set c1 = :p1 where c2 = :p2 using var_my_value1, var_my_value2

dbms_sql should be used only in rare cases. For example when dynamic query has varying number of parameters.

PS: even dbms_sql has procedure bind_variable.

Answer 2

Don't attempt to escape, instead use bind variables.