Posted By: Anonymous
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; /
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.