Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)

The concatenation operator is:

This operator concatenates strings, but you can concatenate other types. PL/SQL does implicit conversion, such as converting a number and date type variable to a string. This enables statements to concatenate various types, but only if the concatenated item converts to a string.

DECLARE professor_name VARCHAR2(100) := 'Professor Smith'; hire_date DATE := SYSDATE; dalary NUMBER(7,2) := 10100.50; str VARCHAR2(2000); BEGIN str := professor_name ' was hired on ' hire_date ' with a salary of ' salary' per month. '; dbms_output.put_line(str); END;

You precede each single quote that is part of the string with a quote. For example, your string needs to include a single quote because the final text must be this next line:

A quote like ' is needed.

You produce this output by preceding the quote with a quote. This identifies the character as a quote in the string rather than the end of the string.

DECLARE str varchar2(100); BEGIN str := 'A quote like '' is needed.'; dbms_output.put_line(str); END;

When quotes must begin or end a string, you still have two quotes. You also have the string-terminating quote. You want the output to be:

'A quote like ' is needed.'

You form this string with the following:

DECLARE str varchar2(100); BEGIN str := '''A quote like '' is needed.'''; dbms_output.put_line(str); END;

The SQL*Plus environment uses "&" for parameter notation. When you write PL/SQL in a SQL*Plus environment, the use of this symbol and the text that follows it will be interpreted as a SQL*Plus parameter. This is only an issue in SQL*Plus. To use & in a string, replace it with the CHR function. The CHR function is a built-in function that returns the ASCII character from an equivalent ASCII number. The ASCII integer for & is 38. Refer to Section 11.14, "Miscellaneous String Functions," for a description of CHR and ASCII. If our output is:

'A quote like ' and a & is needed.'

You can display this with the following.

DECLARE str varchar2(100); ch varchar2(1) := CHR(38); BEGIN str := '''A quote like '' and a 'ch' is needed.'''; dbms_output.put_line(str); END;