simple and elegant..something new from 10g

One more time It happened..I was browsing within Oracle documentation for something else and found something else (of course, got deviated from intended search :))



Single quotes within a string literal is always a messy job in Oracle because you need to escape every single single quote within your string to make Oracle understand its a part of data.

SQL> conn venkat/venkat@ORCL

Connected.

SQL> select * from v$version;


BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production




here is the tradtional way I had known ever since

SQL> ed
Wrote file afiedt.buf

1 select
2 'it''s sunday night '||
3 to_char(sysdate,'hh:MI AM')||
4 '. I am testing single quoting ("''") in Oracle' as TestOutput
5* from dual
SQL> /

TESTOUTPUT
------------------------------------------------------------------------
it's sunday night 09:15 PM. I am testing single quoting ("'") in Oracle

Now here is the same using 10g's Q quote delimiter

SQL> ed
Wrote file afiedt.buf

1 select q'(it's sunday night )' ||
2 to_char(sysdate,'hh:MI AM')||
3 q'(. I am testing single quoting ("'") in Oracle)' TestOutput
4* from dual

SQL> /

TESTOUTPUT
----------------------------------------------------------------------------
it's sunday night 09:17 PM. I am testing single quoting ("'") in Oracle

No more messy escaping quotes.. Neat!! (and am I the only one to catch up with this 10g feature this late? Better late than never!!:))