This discussion is archived
2 Replies Latest reply: Nov 22, 2012 3:39 PM by axvelazq RSS

How to escape apostrophe in Oracle PL/SQL  dynamic queries

axvelazq Newbie
Currently Being Moderated
I have an script that receives an string as a parameter, for example:
@C:/myScript.sql "AXEL";
@C:/myScript.sql "AXEL DAVID";
@C:/myScript.sql "o'neal";

my scripts basically constructs a Parameterized query an execute it. That's all. It works most of the time, except when the parameter contains apostrophes
DEFINE myparameter = &&1
...
myquery := "Select * from myTable where x = :p1";
EXECUTE IMMEDIATE myquery into results USING myparameter;
...
When trying the following;
@C:/myScript.sql "o'neal";
I get the following error:
Bind Variable "p1" is NOT DECLARED

Of course if I change the parameter as "o''neal" It will work, but I will end up with other issues later on, so I would like to know how can I deal with apostrophe on dynamic queries.

I also tried to use the following:
...
   myquery := "Select ....  where x = " || Q'#:p1#';
...
but not working.

any hint will be appreciated =)

Edited by: user13679988 on Nov 22, 2012 3:12 PM

Edited by: user13679988 on Nov 22, 2012 3:38 PM

Edited by: user13679988 on Nov 22, 2012 3:45 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points