There are several methods to put the quote into the string.
The first (and very traditional) one: use 2 quotes.
[cc lang=”oracle8″]
select ‘This ” is quote’ from dual;
[/cc]
If there are more than one quote, it’s difficult to read and write such strings
The second: use chr(39)
[cc lang=”oracle8″]
select ‘This ‘ || chr(39) || ‘ is quote’ from dual;
[/cc]
The third: put the quote into the variable
[cc lang=”oracle8″]
declare
s_quote varchar2(1) := ”” ;
begin
dbms_output.put_line(‘This ‘ || s_quote || ‘ is quote’ ) ;
end;
[/cc]
And, finally, Oracle 10g has added new feature: quote operator.
[cc lang=”oracle8″]
select q'[This ‘ is quote]’ from dual ;
[/cc]
The general form is q’X string X’. Here X is just some character. If the brackets are used, Oracle expects the closing bracket for the end of the string.
Here are the additional examples:
[cc lang=”oracle8″]
select q'(This ‘ is quote)’ from dual ;
select q’|This ‘ is quote|’ from dual ;
select q’#This ‘ is quote#’ from dual ;
select q’#This ‘ is quote#’ from dual ;
select q’?This ‘ is quote?’ from dual ;
select q’TThis ‘ is quoteT’ from dual ;
[/cc]