Defining one’s own string de-limiter in Oracle 10g

Oracle 10g provided one new enhancement for string literals. We can define our own string delimiter character. Normally, when a string containing a single quote needs to be assigned to some variable, we have to put an extra single quote in front of the existing single quote of the string – that acts as the escape character. Example:

SET SERVEROUTPUT ON
DECLARE
v_Str1 VARCHAR2(30);
BEGIN
v_Str1 := ‘Decipher”s blog’;
DBMS_OUTPUT.PUT_LINE(v_Str1);
END;
/

Connect through SQL*Plus and run above query. It will give following output.

Decipher’s blog

Notice the quote in the string it self. We prefixed it with another single quote so that string can be displayed as it with single quote. In 10g, we can define our own string delimiter which relieves the need of putting extra quote in the string. Only thing to remember is that, the character you want to use for delimiter should not exist in the string. Let us see that.

DECLARE

v_Str1 VARCHAR2(30);
v_Str2 VARCHAR2(30);
v_Str3 VARCHAR2(30);
v_Str4 VARCHAR2(30);

BEGIN

v_Str1 := ‘Decipher”s blog’;
v_Str2 := q’@Decipher’s blog@’;
v_Str3 := q’1Decipher’s blog1′;
v_Str4 := q’*Decipher’s blog*’;

DBMS_OUTPUT.PUT_LINE(v_Str2);
DBMS_OUTPUT.PUT_LINE(v_Str2);
DBMS_OUTPUT.PUT_LINE(v_Str3);
DBMS_OUTPUT.PUT_LINE(v_Str4);

END;
/

In above example, we are defining our own string delimiter (@, 1 and *) with quote operator ‘q’. This quote operator allows us to definer our own delimiter. None of these characters is part of the original string. When we are using these characters as string delimiter, we are not prefixing extra quote in front of the quote of the string. All of the above gives the same output as shown below.

Decipher’s blog
Decipher’s blog
Decipher’s blog
Decipher’s blog

This becomes very helpful when we have stings which contain lots of double quotes.