Oracle 10g introduced the ability for user-defined quote characters. Well, prior to Oracle 10g, if you wanted to include quotes in text, you had to ‘escape’ the quote with another quote such as:
Select 'My Name is Alwyn D''Souza having '' after D' from dual;
This technique becomes inconvenient when you write a very long dynamic sql string.You miss one 'escape' character and the code runs into error during run-time and we waste lot of time finding the missing 'escape' character.
Now with Oracle 10g, you can re-write this as:
Select q'{My Name is Alwyn D'Souza having ' after D}' from dual;
Select Q'{My Name is Alwyn D'Souza having ' after D}' from dual;
Note that the quoted strings starts with the letter q/Q, followed by a single quote and the new quote character. It ends with the new quote character and a single quote. I’ve used a flower bracket ({) as my quote character, but you can use other characters if you’d like. Now you can put any quoted text in between your quote characters.
You can use this in PL/SQL as well.
set serveroutput on
declare
svar varchar2(100) := q'{My Name is Alwyn D'Souza having ' after D}';
begin
dbms_output.put_line(svar);
dbms_output.put_line (q'{My Name is Alwyn D'Souza having ' after D}');
end;
/
The Q-quote delimiter can be any single- or multibyte character except space, tab, and return. If the opening quote delimiter is a [, {, <, or ( character, then the closing quote delimiter must be the corresponding ], }, >, or ) character. In all other cases, the opening and closing delimiter must be the identical character.
The following character literals use the alternative quoting mechanism:
q'(name LIKE '%DBMS_%%')'
q'<'Data,' he said, 'Make it so.'>'
q'"name like '['"'
nq'ïŸ1234ï'
It would be cool if more languages adopted this Perl syntax gem.
ReplyDelete