PL SQL Scalar Datatypes :

In my previous article I explained about the anonymous block with example. In this article I will try to give the idea about different PL SQL Scalar datatypes. The Scalar datatypes are used in different programming languages to define the particular type of data. Just like the other programming languages PL SQL has set of datatypes. The valid datatypes in PL SQL specifies the storage format, constants and valid range of values.PL SQL datatypes are grouped in to four categories:

1. Composite datatype
2. LOB datatype
3. Scalar Datatype
4. Reference Datatype

In this article I will try to explain the Scalar datatypes as well as LOB datatypes. I will try to explain about the Reference datatypes and composite datatypes in other article.

Scalar Datatypes:

As the name specifies ‘Scalar’ means something that has size but no direction. The scalar datatype has no internal component. It has single value such as number and character datatype. The scalar datatypes further classified in to different categories:

1.Numeric Types

2.Character Types

3.Boolean Types

4.Datetime Types

1.Numeric Types:

The Numeric datatypes are used to perform the arithmetic operations in PL SQL. There are following predefined numeric datatypes in PL SQL.

1.1. Number (P, S):

The most used numeric datatype in PL SQL is Number datatype. The P stands for Precision and S stands for Scale. Number (P, S) datatype holds the floating point number value in the range 1E-130 to 1.0E126.

Example :

Average Number (10, 2);

The Average variable which will define the number which has 10 precision vales and 2 scale values.

1.2. PLS_INTEGER:

This datatype is fastest datatype which holds the integer value in range -2,147,483,648 to 2,147,483,647 which will represented in 32 bits format.

1.3.DEC(P,S) :

DEC stands for decimal integer which will hold maximum precesion of 38 digits.This datatype is ANSI specific date format

 Example :

When there are complex calculations and needs the data of more than 30 precisions then it is preferable to use DEC(P,S).

1.4. Binary_Integer:

Binary_Integer is signed integer in the range of -2,147,483,648 to 2,147,483,647 represented in 32 bits.

1.5. Binary_Float:

Binary_Float is also fast datatype which holds the single precision floating point format which is IEEE-754 format.

1.6.Binary_Double:

Binary_Float is also fast datatype which holds the double precision floating point format which is IEEE-754 format.

1.7. Decimal(P,S) :

Decimal(P,S) is IBM specific datatype which will use mostly for IBM applications which will hold 38 precisions.

1.8.Numeric(P,S):

Numeric datatype is also one of the scalar datatype which holds the 38 precision float data.

1.9.DOUBLE PRECISION :

There are lot of calculations needed in many businesses like manufacturing, banking. In that case the DOUBLE PRECISION datatype is used. When user needs the output in more than 38 decimal places then user needs to use DOUBLE PRECISON datatype.DOUBLE PRECISION holds 126 decimal places.

1.10.Float :

This is recommended IBM specific and ANSI specific datatype which will hold the maximum of 126 decimal digits.

1.11.INT:

This is ANSI specific datatype which will hold the integer value of maximum 38 decimal digits.

1.12.INTEGER :

This is also IBM and ANSI specific datatype which will hold the integer value of maximum 38 decimal digits.

1.13.SMALLINT:

This is also IBM and ANSI specific datatype which will hold the integer value of maximum 38 decimal digits.

1.14.Real:

Real number is floating type number which holds 63 binary digits and approximately 18 decimal digits.

Example :

DECLARE 
   RollNo INTEGER; 
   Marks REAL; 
   Temp DOUBLE PRECISION; 
   Average_Marks NLS_INTEGER;
BEGIN 
   null; 
END;

2.Character types :

The Character datatype holds the character values or strings of characters. There are following character types used in PL SQL:

2.1.CHAR:

This is mostly used datatype which holds fixed length character string with maximum size of 32,767 bytes.

2.2.VARCHAR2:

This is variable length character datatype which holds the alphanumeric values up to 32,767

Bytes. The VARCHAR2 datatype is mostly used datatype in PL SQL for character strings.

2.3.RAW:

RAW datatype is variable length binary or byte string with 32,767 bytes which will not interrupted by PL SQL.

2.4.NCHAR :

Here N Stands for national character.NCHAR datatype is used to hold the fixed length national character string with maximum 32,767 bytes.

2.5.NVARACHAR2:

NVARCHAR2 datatype holds the variable length national character string with maximum of 32,767 bytes.

2.6.LONG:

LONG datatype is variable length character string with maximum size of 32,760 bytes.

2.7.LONG RAW:

LONG RAW datatype is used to hold variable length binary or byte string with maximum of 32,760 bytes with no interruption of PL SQL.

2.8.ROWID:

This is physical row identifier which holds the address of the row in ordinary table.

2.9.UROWID:

The Universal row identifier holds the universal address of the row of the ordinary table. Universal identifier is Physical, Logical and foreign identifier.

Example:

DECLARE

Name VARCHAR2(30);

Address LONG;

Address1 NVARCHAR2;
BEGIN

null;

END;

3.Boolean Types :

Boolean datatype is used to store the values true and false.The boolean logic has been implemented by george boolean.There are 3 types of boolean values.

1.True which is indicated by ‘1’

2.False which is indicated by ‘0’

3.Null value

The boolean values are used to check the complex evaluations in PL SQL.

Example :

function false return boolean
is
begin

return 0;

end;

4.Datetime type :

The main datatype used in database to store date is datetime datatype.The DATE datatype is used to store fixed-length datetimes, which include the time of day in seconds since midnight. Valid dates range from January 1, 4712 BC to December 31, 9999 AD.

Valid range of date : 1-JAN-4712 BC (Before Christ) to 31-DEC-9999

The default date format is set by the Oracle initialization parameter NLS_DATE_FORMAT. For example, the default might be ‘DD-MON-YY’, which includes a two-digit number for the day of the month, an abbreviation of the month name, and the last two digits of the year. For example, 01-DEC-17.

Each DATE includes the century, year, month, day, hour, minute, and second. The following table shows the valid values for each field −

Field Name	Valid Datetime Values	Valid Interval Values
YEAR	-4712 to 9999 (excluding year 0)	Any non zero integer
MONTH	01 to 12	0 to 11
DAY	01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the calendar for the locale)	Any nonzero integer
HOUR	00 to 23	0 to 23
MINUTE	00 to 59	0 to 59
SECOND	00 to 59.9(n), where 9(n) is the precision of time fractional seconds	0 to 59.9(n), where 9(n) is the precision of interval fractional seconds
TIMEZONE_HOUR	-12 to 14 (range accommodates daylight savings time changes)	Not applicable
TIMEZONE_MINUTE	00 to 59	Not applicable
TIMEZONE_REGION	Found in the dynamic performance view V$TIMEZONE_NAMES	Not applicable
TIMEZONE_ABBR	Found in the dynamic performance view V$TIMEZONE_NAMES	Not applicable

(Source-Tutorialpoint.com)

Hope you like this article on scalar datatypes of PL SQL. Dont forget to comment in comment section.