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) ;).

I read in the Fort Worth Star Telegram this morning that the Texas legislature had passed a law (Texas House Bill 171) which requires school administrators to use common sense. I guess there has been a problem with this in the past (actually the bill explicitly states that there has been a problem – “School administrators are allowed to consider mitigating factors but sometimes choose not to exercise common sense.”). At issue was strict “no tolerance” policies in some school districts whereby kids were getting sent to alternative schools for infractions as absurd as leaving a baseball bat in the car, leaving fishing tackle (including a knife) in the car, forgetting to take a pocket knife out of a pocket, getting beat up (that’s right – both participants in a fight get the same punishment).

Oddly enough, no one voted against this bill. How could you vote against “common sense”. Can’t you just see the negative political ad next election …

You know the unflattering black and white images, with the voice over saying something like:  “My illustrious opponent voted against Common Sense. Surely you don’t want to elect someone who doesn’t even believe in Common Sense!” …

During dinner, my daughter asked me what I was going to do at work tomorrow. I told her I was going to try to use common sense, even though it wasn’t required by law in my profession. At least it doesn’t yet… But imagine if we could just get a few more organizations in a few more states to start taking this idea seriously. Just imagine what could happen. Reminds me of that bit from Arlo Guthrie’s song – Alice’s Restaurant:

“… And can you, can you imagine fifty people a day,I said fifty people a day walking in singin a bar of Alice’s Restaurant and walking out. And friends they may think it’s a movement. …”

(by the way, if you’ve never heard “Alice’s Restaurant” you should go buy it from iTunes right now – I mean right now – don’t even finish reading this – do it now!)

Anyway, this common sense thing sounds like a great idea that could really catch on. I can think of a few organizations where common sense could really be valuable (a lot of them are in Washington). Although I’m not sure how they would enforce it… But that’s a job for another day.