2 Replies Latest reply: Nov 22, 2012 5:39 PM by axvelazq RSS

    How to escape apostrophe in Oracle PL/SQL  dynamic queries

    axvelazq
      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