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;
/
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.
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 >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 >character. That is if you put two single quote characters Oracle will print one. The first >one acts like an escape character.
This is the simplest way to print single quotation marks in Oracle. But it will get >complex when you have to print a set of quotation marks instead of just one. In this >situation the following method works fine. But it requires some more typing labour.
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.
Source: Stackoverflow.com