Embedding Single Quotes Inside a String
The trickiest part of working with string literals comes when you need to include a single quote inside a string literal (as part of the literal itself). Generally, the rule is that you write two single quotes next to each other inside a string if you want the literal to contain a single quote in that position. The following table shows the literal in one column and the resulting "internal" string in the second column:
| Literal | Actual value |
| 'There''s no business like show business.' | There's no business like show business. |
| '"Hound of the Baskervilles"' | "Hound of the Baskervilles" |
| 'NLS_LANGUAGE=''ENGLISH''' | NLS_LANGUAGE='ENGLISH' |
| '''' | ' |
| '''hello''' | 'hello' |
| '''''' | '' |
Here's a summary of how to embed single quotes in a literal:
· To place a single quote inside the literal, put two single quotes together.
· To place a single quote at the beginning or end of a literal, put three single quotes together.
· To create a string literal consisting of one single quote, put four single quotes together.
· To create a string literal consisting of two single quotes together, put six single quotes together.
Two single quotes together is not the same as a double quote character. A double quote character does not have any special significance inside a string literal. It is treated the same as a letter or number.
Numeric Literals
Numeric literals can be integers or real numbers (a number that contains a fractional component). Note that PL/SQL considers the number 154.00 to be a real number, even though the fractional component is zero and the number is actually an integer. Internally, integers and reals have a different representation, and there is some small overhead involved in converting between the two.
You can also use scientific notation to specify a numeric literal. Use the letter "E" (upper- or lowercase) to multiply a number by 10 to the nth power—for example, 3.05E19, 12e-5.
Boolean Literals
Oracle provides two literals to represent Boolean values: TRUE and FALSE. These values are not strings; you should not put quotes around them. Use Boolean literals to assign values to Boolean variables, as in:
DECLARE enough_money BOOLEAN; -- Declare a Boolean variableBEGIN enough_money := FALSE; -- Assign it a valueEND;You do not, on the other hand, need to refer to the literal value when checking the value of a Boolean expression. Instead, just let that expression speak for itself, as shown in the conditional clause of the following IF statement:
DECLARE enough_money BOOLEAN;BEGIN IF enough_money THEN ...