# 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.