I hate single-quotes

When you use SQL, you must enclose string values with Single-Quotes.

This sounds fair and square.

Things get messy when your strings themselves contain single-quote. A new technique has been introduced by Oracle to circumvent the hurdles. However, before describing that solution, let me describe the problem and its classical solution before showing the enhancement

Example (let me start with the normal straight forward case)
If I am using Oracle SQL and I want to display the following text

The Customer said YES

Then

SQL> SELECT ' The Customer said YES ' AS title from dual;

TITLE
-----------------------
The Customer said YES

Now, if the output should read

The Customer said 'YES' -- The difference her is that YES is surrounded with single-quotes

In this case, you cannot simply expect to just add a single quote around YES and pray that things shall work fine. You will be confusing the hell out of the parser because it cannot tell which quotes are to be displayed or the quotes that end the string

I mean the following SQL is a NO NO situation

SQL>SELECT 'The Customer said 'YES' ' AS title from dual;

*ERROR at line 1:ORA-00923: FROM keyword not found where expected

Traditionally, we could use two consecutive single-quote around YES to tell the parser that this is not the end of the string but rather an actual single quote character that needs to be displayed

1* SELECT ' The Customer said ' 'YES' ' ' AS title from dual;

TITLE
------------------------
The Customer said 'YES'

This will become even messier, if the entire string needs to be surrounded by single quotes

'The Customer said 'YES' '

New Approach, The Q-quote mechanism
In this approach, you can define your Single-Quote character which enables you to specify q or Q followed by a single quote and then another character to be used as the quote delimiter.

i.e. I want to define a character that indicates the beginning of the string and the terminator of the string.
If, for example, you want to utilize # as the single quote mark, then this is what you need to do when you encounter the beginning of the string, write the letter q followed by a single-quote followed by # and when the string is finished you just add # followed by ' as shown below

AMMAR>SELECT q'# The Customer said 'YES' #' as TITLE from dual;

TITLE
-------------------------
The Customer said 'YES'

Ammar Sajdi