Because CHAR variables are fixed length and the preceding strings are each less than 32 characters long, they will be right-padded with spaces. Thus the actual values in employee_name would be

`Jenny Gennick                   `

and

`Jeff Gennick                    `

This point is important to remember, especially when doing string comparisons, because the trailing spaces count as part of the string. Try typing in and executing the input code shown in Listing 3.1.

INPUT/OUTPUT
Listing 3.1. Comparison of CHAR with VARCHAR2.

 1: --Remember to execute: SET SERVEROUTPUT ON
 2: SET ECHO ON
 3: DECLARE
 4:   employee_name_c CHAR(32);
 5:   employee_name_v VARCHAR2(32);
 6: BEGIN
 7:   --Assign the same value to each string.
 8:   employee_name_c := `Jenny Gennick';
 9:   employee_name_v := `Jenny Gennick';
10:
11:   --Test the strings for equality.
12:   IF employee_name_c = employee_name_v THEN
13:     DBMS_OUTPUT.PUT_LINE(`The names are the same');
14:   ELSE
15:     DBMS_OUTPUT.PUT_LINE(`The names are NOT the same');
16:   END IF;
17: END;
18: /
19: The names are NOT the same
20:
21: PL/SQL procedure successfully completed.

ANALYSIS
What happened here? The same value was assigned to both strings, yet they did not test as being equal. This occurred because the CHAR string contains a number of trailing spaces, whereas the VARCHAR2 string does not.

The rtrim function is one you will learn more about on Day 7, "Using Oracle's Built-in Functions."

CHAR Subtypes
Oracle has one subtype defined for the CHAR datatype, and it is called CHARACTER. It has exactly the same meaning as CHAR.

NUMBER

The NUMBER datatype is used for declaring both fixed-point and floating-point numbers. It can be used to represent numbers in the range 1.0E-123 through 9.99E125, and it allows for up to 38 decimal digits of precision. It is very commonly used and is a bit more complicated than the character datatypes discussed earlier.

The Syntax for the NUMBER Datatype

variable_name NUMBER [(precision[,scale])]

In this syntax, variable_name is whatever name you want to give this variable. precision specifies the number of decimal digits used to represent the value internally. The range is 1 to 38, and the default is 38. scale indicates where the decimal point is and where rounding occurs. The range is _84 to 127, and the default is zero.

Here are some examples:

dollar_amount NUMBER (5,2);
no_cents NUMBER (3);
big_floating NUMBER;
shares_traded NUMBER (5,-2);
microns NUMBER (1,6)

The easiest way to understand precision and scale is to think of precision as telling you how many digits are used to represent the number. Then the scale tells you where the decimal point is.

The dollar_amount variable, defined in the preceding example as NUMBER(5,2), would then be precise to five digits, two of which would be to the right of the decimal. All amounts would

be rounded to the nearest hundredth. It could store values such as 123.45, -999.99, and so on. Assigning it a value of 123.456 would result in the value being rounded off to 123.46.

The no_cents variable, defined in the preceding example as NUMBER(3), would take the default scale of zero. Thus it could store no digits to the right of the decimal, and all values will be rounded to the nearest whole number. Assigning it a value of -123.45 would result in it being rounded off to -123.

The big_floating variable, defined only as NUMBER, has no precision and scale specified in its declaration. Use this to define a floating-point value.

The shares_traded variable is interesting because the example declared it with a negative scale, that is, as NUMBER(5,-2). It stores five digits of precision, but all values are in hundreds. It could store values ranging from 0 to 9,999,900, but all values would be rounded to the nearest hundred. Assign it a value of 100, and it will store 100. Assign it a value of 327, and it will be rounded off to 300. Why use a variable like this? It saves a bit of space and allows you to use the 38 digits to represent some very large numbers without making excessive demands on memory. For a real-world example, take a look at the stock market listings in almost any newspaper, and you will see that the number of shares traded is usually reported in blocks of 100.

The microns variable is also a bit unusual because the example specified a scale that is larger than the precision. This is perfectly legitimate and is really the reverse of what was done with shares_traded. It will store values of one millionth, two millionths, and so on up to nine millionths. All values will be rounded to the nearest millionth, so if you assigned it a value of 0.00000016, you would get 0.0000002. Because the precision is only one, trying to assign a value of 0.000001 would result in an error. 0.000001 is 10 millionths, which in this case requires two digits of precision to store.

The NUMBER datatype is the only numeric datatype that is available both at the database level and in PL/SQL. It is stored using a hardware-independent representation and manipulated using hardware-independent code. Oracle guarantees portability of this datatype across the various platforms supported by Oracle.

NUMBER Subtypes
Oracle has defined several subtypes of NUMBER. Most of these have exactly the same meaning as, and can be used interchangeably with, the keyword NUMBER. Table 3.2 shows a complete list of NUMBER subtypes and describes their use.