Although this challenge was posted in past, I thought to revive its magic. It was posted by Tom Kyte on Oct 31, 2007 4:52 AM. Without Tom or even AskTom I don’t think Oracle geeks would have some good repository of technical advantage. Here in this article I will rephrase and revive the magic of a small challenge for Oracle geeks. Find out from the following list of versions and features, when they where introduced in Oracle.

Of course you could read the link above and find the same. But try this as an exercise, and you will be surprised (for most of geeks they will find new features, for others they will battle with their memories).

We make use of single quotation mark in SQL and PL/SQL to identify sting literals. If the literal itself contains a single quote, we need to add one more quote next to it. This additional quote acts as an escape character and removes conflict with the outside quotes that are enclosing the string.

Oracle realises that long complex strings having lot of single quotes can turn out to become cumbersome and prone to errors that may not be caught during testing.

Release 10g onwards, a new quoting mechanism is provided in the form of "q". This new quote operator allows us to choose our own quotation mark delimiter.

Here are some examples -

SQL> select 'amar's web blog. It's personal..' str from dual;

select 'amar's web blog. It's personal..' str from dual

*

ERROR at line 1:

ORA-00923: FROM keyword not found where expected

What we normally do:-

SQL> select 'amar''s web blog. It''s personal..' str from dual;

STR

--------------------------------

amar's web blog. It's personal..

1 row selected.

Here is use of Q - quote operator. The above statement can also be represented as any one of the below.

select q'(amar's web blog. It's personal.)' str from dual;

select q'[amar's web blog. It's personal.]' str from dual;

select q'Aamar's web blog. It's personal.A' str from dual;

select q'/amar's web blog. It's personal./' str from dual;

select q'Zamar's web blog. It's personal.Z' str from dual;

select q'|amar's web blog. It's personal.|' str from dual;

select q'+amar's web blog. It's personal.+' str from dual;

(New added)

select q'zamar's web blog. It's personal.z' str from dual;

And so on. After the Q-quote is specified, provide the single quote along with a unique character at the beginning of the string. To close the string, provide the same character followed by the single quote. The single and the provided character form the two character string enclosure.

If you decide to use braces, Oracle expects matching enclosures, i.e., Open brackets should be represented by closed brackets at the end of the string. Other characters can be represented as they are at both ends.

All we need to take care of is that the last two character delimiter does not show up in the string itself. For instance the below will throw error as the closing characters get repeated within the string.

SQL> select q'aamar's web blog. Ita's personal.a' str from dual;

ERROR:

ORA-01756: quoted string not properly terminated

The same can be used in PL/SQL also.

SQL> declare

2 l_str varchar2(100) := q'[amar's web blog. Ita's personal.]';

3 begin

4 dbms_output.put_line(l_str);

5 end;

6 /

amar's web blog. Ita's personal.

PL/SQL procedure successfully completed.

I recently came across a program that framed dynamic INSERT statement to copy data from one database to another. Unfortunately, one of the record columns had a single quote embedded in the string and this resulted in the program unit to fail in production. This happened in release 9i and the only alternative available was to remove/double the single quotes from the string literal. From release 10g, Q-quote could also be used to prevent this problem. I will simulate the same scenario and try this out.

In this article we will see how to create queues and to enqueue and dequeue messages. In this article I will not discuss what Advanced Queuing is about and why it is used. This is just a step-by-step guide to generate a message and to read it. The agenda of this article is:

Disclaimer

This blog contains things about technology that I jot about, keep for my records. Some articles in this blog are not owned by me. They are for my reference only. In older posts the original links may not be there, its not intentional. Thanks for understanding.