Oracle Performance and Engineering

I've decided to upgrade my Oracle certifications to 10g. Though there've been debates regarding the worth of certification - but to me certification value is more inward-oriented (self training) rather than outward (get a paper to market yourself in job market).

I usually give a good part of my daily spare time to learning. But in spite of that, I usually end up with learning things I'm presently working on. For example, I would never learn the RMAN improvements in 10g without having been through a mandatory requirement to just learn it. Which happens before such exams.

My training regimen is simple. Learn from the excellent online e-study guides at Oracle partner network and try it out with my own practice 10g database.

So far, for last three days, it's been a huge amount of learning. I will come out with a series of "New in 10g" or "Improved with 10g" articles later. But here's a neat trick I learned today -

Say you need to read 'Tom Jones' (single quotes included) from or feed into a SQL statement -
10g offers this very cool q operator.

i.e, the query will look like -

SQL> select q'('Tom Jones')'q from dual;
Q
-----------
'Tom Jones'

want to retrieve 'Today is Tom's birthday' ?

SQL> select q'['Today is Tom's birthday']' string from dual;

STRING
-------------------------
'Today is Tom's birthday'

Note:
----
(a) [ and ] are used paired. () or {} can also be used. You can also choose your own delimiter :)
"However, if the delimiter appears in the text literal itself, ensure that it is not immediately followed by a single quotation mark."

(b) Everything, including one or more ' (single quotes) are counted automatically (no special encasing for those)

(c) q is the quotation delimiter

With 9i and before this query would have to be written as -


SQL> select '''Today is Tom''s birthday''' string from dual;

STRING
-------------------------
'Today is Tom's birthday'

i.e, for every occurrance of a single quote, you've to encase it! Which was troublesome and needed more coding to be done.

The new q operator is also much more readable!

q can also be used in PL/SQL as -

v_string := q'Z name like 'DBMS_%'Z';

Note: We've used Z as the delimiter here.

Isn't it cool? I like it. No more counting single quotes anymore..Whoo hoo..

posted by Nilendu  # 6:08 PM