Kerry Osborne's Oracle Blog

In the “Learn Something New Every Day” category, I ran across this new feature (to me) of 10g today (on Eddie Awad’s Blog). I was trying to write a little script to help me create a rewrite rule using DBMS_ADVANCED_REWRITE and I wanted to be able to pass a string that had quotes embedded in it (i.e. a SQL statement). Of course you all know that quotes can be embedded in a string by doubling them like so:

SQL> select '''This is Kerry''s stuff.''' string from dual;

STRING
------------------------
'This is Kerry's stuff.'

Or perhaps using the chr function like so:

SQL> select chr(39)||'This is Kerry'||chr(39)||'s stuff.'||chr(39) string from dual;

STRING
------------------------
'This is Kerry's stuff.'

The new way is much more straight forward, especially if the goal is to paste in a long SQL statement with potentially lot’s of quotes. It looks like this:

SQL> select q'[What ever stuff you'd like to see, just as you'd like to see it.]' string from dual;

STRING
----------------------------------------------------------------
What ever stuff you'd like to see, just as you'd like to see it.

Not bad, huh? Much easier to read – but that’s not all. Suppose you want to prompt the user for a string that contains quotes.

SQL> select '&string' into :x from dual;
Enter value for string: this is text

'THISISTEXT'
------------
this is text

SQL> /
Enter value for string: where x = 'Y'
select 'where x = 'Y'' into :x from dual
                    *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

So it sees the first quote in the string and thinks it’s at the end of the string. To get around this you could paste your string into a text editor, replace every quote with two quotes (or with ‘||chr(39)||’), and then grab the whole new string and paste it back into your script. But it’s pretty messy. Obviously the Q-quote method makes the whole thing much simpler.

By way of example, here’s the little script I was working on:

SQL> !cat create_rewrite.sql

exec sys.dbms_advanced_rewrite.declare_rewrite_equivalence ( -
name => '&rewrite_name', -
source_stmt => q'[&from_sql_statement]', -
destination_stmt => q'[&to_sql_statement]', -
validate => FALSE, -
rewrite_mode => 'TEXT_MATCH' -
);

SQL> @create_rewrite
Enter value for rewrite_name: DODA
Enter value for from_sql_statement: select * from sapsr3.fkkvk where vkona <> 'Y'
Enter value for to_sql_statement: select * from sapsr3.fkkvk where vkona = 'N'

PL/SQL procedure successfully completed.

The syntax is really simple: Just select q'[ whatever_you_want_here_including_quotes ]’ and that’s it.
Of course there are a few options (such as using your choice of delimiters). Here’s a link to the docs if you want
further details.

Dan Morgan’s Oracle Library has a good example as well here.

I like using the square brackets as the delimiters because I don’t usually see them in strings (and it looks kind of like a regular expression) ;).