Sunday, August 26, 2007

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!!:))

No comments: