PL SQL how to escape single quote in a string

Question

In the Oracle PL SQL  how to escape single quote in a string   I tried this way  it doesn t work    declare   stmt varchar2 2000   begin   for i in 1021    6020   loop     stmt     insert into MY TBL  Col  values   ER0002           dbms output put line stmt       execute immediate stmt      commit    end loop  exception   when others then     rollback      dbms output put line sqlerrm   end

User · Answer

Here s a blog post that should help with escaping ticks in strings  Here s the simplest method from said post   The most simple and most used way is to use a single quotation mark with two single  gt quotation marks in both sides  SELECT  test single quote    from dual  The output of the above statement would be  test single quote  Simply stating you require an additional single quote character to print a single quote  gt character  That is if you put two single quote characters Oracle will print one  The first  gt one acts like an escape character  This is the simplest way to print single quotation marks in Oracle  But it will get  gt complex when you have to print a set of quotation marks instead of just one  In this  gt situation the following method works fine  But it requires some more typing labour

User · Answer

EXECUTE IMMEDIATE  insert into MY TBL  Col  values   ER0002      worked for me  closing the varchar string with two pairs of single quotes did the trick  Other option could be to use using keyword  EXECUTE IMMEDIATE  insert into MY TBL  Col  values  text string   using  ER0002   Remember using keyword will not work  if you are using EXECUTE IMMEDIATE to execute DDL s with parameters  however  using quotes will work for DDL s

User · Answer

You can use literal quoting   stmt    q  insert into MY TBL  Col  values  ER0002        Documentation for literals can be found here    Alternatively  you can use two quotes to denote a single quote   stmt     insert into MY TBL  Col  values   ER0002        The literal quoting mechanism with the Q syntax is more flexible and readable  IMO

User · Answer

In addition to DCookie s answer above  you can also use chr 39  for a single quote   I find this particularly useful when I have to create a number of insert update  statements based on a large amount of existing data     Here s a very quick example   Lets say we have a very simple table  Customers  that has 2 columns  FirstName and LastName   We need to move the data into Customers2  so we need to generate a bunch of INSERT statements   Select  INSERT INTO Customers2  FirstName  LastName               VALUES       chr 39     FirstName    chr 39                 chr 39     LastName    chr 39          From Customers    I ve found this to be very useful when moving data from one environment to another  or when rebuilding an environment quickly

[oracle] PL/SQL, how to escape single quote in a string?

Examples related to oracle

Examples related to plsql