First post about Oracle: no more quoting nightmare in 10g

So here I am with my first blog after a while when everyone seems to have been blogging for at least half of their internet-enabled life. Let's see how it goes.

Anyway... Today I've been at the first day of Tom Kyte's 2 days seminar in Munich. I've enjoyed the day very much and I'm looking forward for tomorrow already. There is one thing that caught my attention - Oracle 10g Release 1 introduced a nice feature to string literals format. I can now forget about troublesome quoting in SQL and PL/SQL gererating dynamic SQL code. It works similar to Perl's Quote Operator.

Imagine my PL/SQL procedure to create new partitions for next day in some table t1. Now instead of
stmt : = 'ALTER TABLE t1 SPLIT PARTITION p_last AT (TO_DATE('''
|| TO_CHAR (TRUNC(sysdate+2), 'YYYY-MM-DD HH24:MI:SS')
|| ''',''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) INTO (PARTITION p_'
|| TO_CHAR(sysdate+1,'YYYYMMDD') ', PARTITION p_last);'

I can write simple
stmt : = q'[ALTER TABLE t1 SPLIT PARTITION p_last AT (TO_DATE(']'
|| TO_CHAR(TRUNC(sysdate+2), 'YYYY-MM-DD HH24:MI:SS')
|| q'[','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (PARTITION p_]'
|| TO_CHAR(sysdate+1,'YYYYMMDD') ', PARTITION p_last);'
Well, still it's not that readable and elegant as in Perl. RTFM for details - Oracle® Database SQL Reference10g Release 2.
PS: After some editing I still couldn't get the formating to look like I wanted it... WYSIWYG editor for blogger.com seems to be not the best one. Also some tags break the layour of the whole page even though closed correctly withing the post. What anoyed me most is that pipes || are getting constantly removed by WYSIWYG editor!