oracle-databaseplsql

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

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;
/
Solution

  • 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.