Oracle SQL Delimiter

It's been a while since I made a real post, one that has some technical merit. This one just barely qualifies.

There is a nice classic report type "Function Returning a SQL Query" which allows you to write a block of code that returns a select statement. That select statement is the basis for the report. I often use it to simplify building a where clause. It might look something like this:

declare
l_q varchar2(32767); -- This is the return variable. It will hold a select statement

begin
l_q := 'select d.name department, e.name employee
from dept d, emp e
where d.deptno = e.deptno ';

if :P1_DEPTNO is not null then
l_q := l_q || ' and d.deptno = :P1_DEPTNO ';
end if;

-- insert more if statements like above

return l_q;
end;

With Interactive Reports this has become less likely, but it still happens.

Sometimes, though, the query is a bit more complicated, something like this:

begin
l_q := 'select d.name department, e.name employee,
to_date(to_char(e.hiredate,''yyyymm'' ||''01''),''yyyymmdd'') first_day_of_month
from dept d, emp e
where d.deptno = e.deptno
and e.status in (''NEW'', ''PENDING'', ''FOO'')
and d.status = ''NEW'' ';

if :P1_DEPTNO is not null then
l_q := l_q || ' and d.deptno = :P1_DEPTNO ';
end if;

-- insert more if statements like above

return l_q;
end;

You get the idea, a lot of strings and a lot of double ticks (''). There is an easier way, though: a custom SQL Delimiter. Usually a single tick (') indicates the start of a string. If you use a tick within it, you need two ticks ('') to escape it (see code above). But you can avoid that with a custom delimiter. You turn it on with q', that's all, just the letter q followed by a tick. The trick is that the very next character is the delimiter, and a few are special. You end the statement with followed by a tick. Examples are the easiest, so here are a few:

1. l_q := q'! I don't need to escape the ' in the word don't !';
In this case the ! is the special character. q'! starts the string and !' ends it.

2. l_q = q'{ I don't need to escape the ' in the word don't }';
This is a little different. If your special character is { then you use }' to turn it off. It is pretty obvious which are special: [], {}, <>.

So, the code above becomes:

begin
l_q := q'{ select d.name department, e.name employee,
to_date(to_char(e.hiredate,'yyyymm' ||'01'),'yyyymmdd') first_day_of_month
from dept d, emp e
where d.deptno = e.deptno
and e.status in ('NEW', 'PENDING', 'FOO')
and d.status = 'NEW' }';

if :P1_DEPTNO is not null then
l_q := l_q || ' and d.deptno = :P1_DEPTNO ';
end if;

-- insert more if statements like above

return l_q;
end;

You can use this just about anywhere in sql or pl/sql where you want a string.