The scalar data types can be divided into families like
numeric, character, raw, date, rowid, etc. The numeric family stores integer or
real values.
Basic Data Types
Variables of type NUMBER can store both integer and real
values whereas BINARY_INTEGR and PLS_INTEGER can hold only integers.
1) NUMBER: NUMBER data type can hold a numeric value,
either integer or floating point.
Syntax:
NUMBER (P,S)
where P is the precision and S is the scale.
Precision is the number of digits in the value and scale is
the number of digits right of the decimal point. So a data type NUMBER (7.2)
means that the data type can have seven digits out of which 2 will be, decimal
points. Both precision and scale are optional. but If the scale is present,
then precision must also be present. DEC. DECIMAL DOUBLE PRECISION, INTEGER,
INT, NUMERIC, REAL, and SMALLINT are all alternate names of NUMBER (P, S).
For example, i) 1234.5678 ii) 1234.44 iii) 123 iv) 123.4567 v) 1.2345
2) BINARY_INTEGER: The NUMBER data type is stored in a decimal format. This format is optimized for accuracy and storage efficiency, Because of this, arithmetic operations cannot be performed directly on the NUMBER data type. In order to perform numeric calculations, the NUMBER data type is converted into BINARY data type.
The BINARY_INTEGER data type can hold signed integer values in the range —2147483647 to +2147483647. The numbers are stored in a 2's compliment format, which means that they are available for computations without conversion. There are five subtypes for BINARY_INTEGER, but they have limitations on the values they can hold. These subtypes and the values that they can hold are given in the following table 4.1:
3) PLS_INTEGER: PLS_INTEGER have the same range as BINARY INTEGER (-2147483647 to +2147483647). They are also implemented using the 2's compliment format. But if a calculation, which involves a PLS_INTEGER, overflows, an error is raised. But if a calculation that involves a BINARY INTEGER overflows, the result can be assigned to a NUMBER variable, which has a greater range with no error.
Data Types in Character Family
The variables of the character family are used to hold strings or character data. The data types in the character family are:
1) VARCHAR2: This data type is the same as the VARCHAR2 database type. VARCHAR2 type variables can hold variable length character strings with a maximum length of 32,767 bytes.
Syntax:
VARCHAR2(L),where L is the maximum length of the variable.
The length value must be specified, as there is no default. The length of the VARCHAR2 is specified in bytes, not in characters. The subtype of VARCHAR2 is VARCHAR, which is the same as VARCHAR2. Note that the VARCHAR2 database column can hold only 4000 bytes (in Oracle 8) and if a VARCHAR2 PL/SQL variable is more than 4000 bytes, it can be only inserted into a database column of type LONG, which has a maximum length of 2GB.
2) CHAR: Variables of this type are fixed-length character strings. The syntax for declaring a CHAR variable is CHAR(L), where L is the length in bytes. If the length is optional and if it is not specified then it will default to I. So CHAR is the same as CHARM. The maximum length of CHAR variable is 32767 bytes. The maximum length of the CHAR database column is 2000 bytes (in Oracle 8). Therefore if a CHAR of a PL/SQL variable is more than 2000 bytes, it can be only inserted into a database column of typeVARCHAR2 or LONG.
3) LONG: The PL/SQL LONG data type is a variable length string with a maximum length of 32760 bytes. LONG variables are very similar to VARCHAR2 variables.
4) NCHAR and NVARCHAR2: NCHAR and NVARCHAR2 are provided in Oracle 8 and is available in PL/SQL version 8.0. These are National Language Support (NLS) character types CHAR and VARCHAR2. NLS are used to store character strings in different character sets from the PL/SQL language itself.
5) RAW: RAW variables are similar to CHAR variables, except that they are not converted between character sets. The syntax for specifying RAW variable is RAW(L), where L is the length in bytes of the variable. This data type is used to store fixed length binary data. The maximum length of a RAW variable is 32767 bytes. The maximum length of a RAW database column is 255 bytes and hence if the length of the RAW variable is more than 255 bytes it cannot be inserted into a RAW database column.
6) LONG RAW: RAW variables are similar to LONG variables, except that they are not converted between character sets. The maximum length of a LONG RAW variable is 32760 bytes. The maximum length of a LONG RAW database column is 2GB and hence there are no problems in inserting it into a LONG RAW database column.
7) DATE: The DATE data type in PL/SQL behaves the same way as the DATE database type. The DATE type is used to store date and time information. This includes century, year, month, day, hour, minute, and second. A DATE variable is 7 bytes, one byte for each component (century through second). Values are assigned to the date variable using the TO—DATE function. The TO CHAR function can convert a date character.
8) ROWID: The ROWID type is the same as the database ROWID pseudo column type. It can hold a ROWID, which can be thought of as a unique key for every row in the database. ROWIDs are stored internally as a fixed-length binary quantity.
9) BOOLEAN: BOOLEAN variables are used in PL/SQL control structures IF-THEN-ELSE and LOOP statements. A BOOLEAN value can hold TRUE, FALSE or NULL only.
