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

oracle plsql
user595234 picture user595234 · Jul 3, 2012 · Viewed 295.3k times · Source

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

Answer

DCookie picture DCookie · Jul 3, 2012

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.

Related questions

SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end... undefine numero set serveroutput on accept numero …

Read More