• Home
  • Testing
  • SAP
  • Web
  • Must Learn!
  • Big Data
  • Live Projects
  • Blog

In oracle, each value or constant is assigned with a data type.

Any data type is associated with the specific storage format and range constraints.

Basically, it defines how the data is stored, handled and treated by Oracle during the data storage and processing.

The main difference between PL/SQL and SQL data types is, SQL data type are limited with table column while the PL/SQL data types are used in the PL/SQL blocks. More on this later in the tutorial.

In this tutorial, you will learn-

CHARACTER Data type:

This data type basically stores alphanumeric characters in string format.

The literal values should always be enclosed between single quotes while assigning them to CHARACTER data type.

This character data type is further classified as follows:

  • CHAR Data type (fixed string size)
  • VARCHAR2 Data type (variable string size)
  • VARCHAR Data type
  • NCHAR (native fixed string size)
  • NVARCHAR2 (native variable string size)
  • LONG and LONG RAW

CHAR Data type:

This data type stores the string value, and the size of the string is fixed at the time of declaring the variable.

  • Oracle will be blank-padded the variable if the variable didn't occupy the entire size that has been declared for it, hence oracle will allocate the memory for declared size even if the variable didn't occupy it fully.
  • The size restriction for this data type is 1-2000 bytes.
  • CHAR data type is more appropriate to use where ever fixed size of data will be handled. .

    PL/SQL Data Types

Syntax Explanation:

  • The first declaration statement declares the variable 'grade' of CHAR data type with the maximum size of 1 byte (default value).
  • The second declaration statement declares the variable 'manager' of CHAR data type with the maximum size of 10 and assigned the value 'guru99' which is of 6 bytes. Oracle will allocate the memory of 10 bytes rather than 6 bytes in this case.

VARCHAR2 Data type:

This data type stores the string, but the length of the string is not fixed.

  • The size restriction for this data type is 1-4000 bytes for table column size and 1-32767 bytes for variables.
  • The size is defined for each variable at the time of variable declaration.
  • But Oracle will allocate memory only after the variable is defined, i.e., Oracle will consider only the actual length of the string that is stored in a variable for memory allocation rather than the size that have been given for a variable in the declaration part.
  • It is always good to use VARCHAR2 instead of CHAR data type to optimize the memory usage.

    PL/SQL Data Types

Syntax Explanation:

  • The above declaration statement declares the variable 'manager' of VARCHAR2 data type with the maximum size of 10 and assigned the value 'guru99' which is of 6 bytes. Oracle will allocate memory of only 6 bytes in this case.

VARCHAR Data type:

This is the synonymous with the VARCHAR2 data type.

  • It is always a good practice to use VARCHAR2 instead of VARCHAR to avoid behavioral changes.

    PL/SQL Data Types

Syntax Explanation:

  • The above declaration statement declares the variable 'manager' of VARCHAR data type with the maximum size of 10 and assigned the value 'guru99' which is of 6 bytes. Oracle will allocate memory of only 6 bytes in this case. (Similar to VARCHAR2)

NCHAR Data type:

This data type is same as CHAR data type, but the character set will of national character set.

  • This character set can be defined for the session using NLS_PARAMETERS.
  • The character set can be either UTF16 or UTF8.
  • The size restriction is 1-2000 bytes.

    PL/SQL Data Types

Syntax Explanation:

  • The above declaration statement declares the variable 'native' of NCHAR data type with the maximum size of 10.
  • The length of this variable depends upon the (number of length) per byte as defined in the character set.

NVARCHAR2 Data type:

This data type is same as VARCHAR2 data type, but the character set will be of national character set.

  • This character set can be defined for the session using NLS_PARAMETERS.
  • The character set can be either UTF16 or UTF8.
  • The size restriction is 1-4000 bytes.

    PL/SQL Data Types

Syntax Explanation:

  • The above declaration statement declares the variable 'Native_var' of NVARCHAR2 data type with the maximum size of 10.

LONG and LONGRAW Data type:

This data type is used to store large text or raw data up to the maximum size of 2GB.

  • These are mainly used in the data dictionary.
  • LONG data type is used to store character set data, while LONG RAW is used to store data in binary format.
  • LONG RAW data type accept media objects, images, etc. whereas LONG works only on data that can be stored using character set.

    PL/SQL Data Types

Syntax Explanation:

  • The above declaration statement declares the variable 'Large_text' of LONG data type and 'Large_raw' of LONG RAW data type.

Note: Using LONG data type is not recommended by Oracle. Instead LOB data type should be preferred.

NUMBER Data type:

This data type stores fixed or floating point numbers up to 38 digits of precision. This data type is used to work with fields which will contain only number data. The variable can be declared either with precision and decimal digit details or without these information. Values need not to enclose within quotes while assigning for this data type.

PL/SQL Data Types

Syntax Explanation:

  • In the above, the first declaration declares the variable 'A' is of number data type with total precision 8 and decimal digits 2.
  • The second declaration declares the variable 'B' is of number data type with total precision 8 and no decimal digits.
  • The third declaration is the most generic, declares variable 'C' is of number data type with no restriction in precision or decimal places. It can take up to a maximum of 38 digits.

BOOLEAN Data type:

This data type stores the logical values. It represents either TRUE or FALSE and mainly used in conditional statements. Values need not enclose within quotes while assigning for this data type.

PL/SQL Data Types

Syntax Explanation:

  • In the above, variable 'Var1' is declared as BOOLEAN data type. The output of the code will be either true or false based on the condition set.

DATE Data type:

This data type stores the values in date format, as date, month, and year. Whenever a variable is defined with DATE data type along with the date it can hold time information and by default time information is set to 12:00:00 if not specified. Values need to enclose within quotes while assigning for this data type.

The standard oracle time format for input and output is 'DD-MON-YY' and it is again set at NLS_PARAMETERS (NLS_DATE_FORMAT) at the session level.

PL/SQL Data Types

Syntax Explanation:

  • In the above, variable 'newyear' is declared as DATE data type and assigned the value of Jan 1st, 2015 date.
  • The second declaration declares the variable current_date as DATE data type and assigned the value with current system date.
  • Both these variable holds the time information.

LOB Data type:

This data type is mainly used to store and manipulate large blocks of unstructured data's like images, multimedia files, etc. Oracle prefers LOB instead of the LONG data type as it is more flexible than LONG data type. The below are the few main advantage of LOB over LONG data type.

  • The number of column in a table with LONG data type is limited to 1, whereas a table has no restriction on number of columns with LOB data type.
  • The data interface tool accepts LOB data type of the table during data replication, but it omits LONG column of the table. These LONG columns need to be replicated manually.
  • The size of LONG column is 2GB, whereas LOB can store up to 128 TB.
  • Oracle is constantly improvising the LOB data type in each of their releases according to the modern requirement, whereas LONG data type is constant and not getting much updates.

So, it is always good to use LOB data type instead of LONG data type. Following are the different LOB data types. They can store up to the size of 128 terabytes.

  1. BLOB
  2. CLOB and NCLOB
  3. BFILE

BLOB:

This data type stores the LOB data in the binary file format up to the maximum size of 128 TB. This doesn't store data based on the character set details, so it can store the unstructured data such as multimedia objects, images, etc.

PL/SQL Data Types

Syntax Explanation:

  • In the above, variable 'Binary_data' is declared as BLOB.

CLOB and NCLOB:

CLOB data type stores the LOB data into the character set, whereas NCLOB stores the data in the native character set. Since these data types uses character set based storage, these cannot store the data like multimedia, images, etc. that cannot be put into a character string. The maximum size of these data types is 128 TB.

PL/SQL Data Types

Syntax Explanation:

  • In the above, variable 'Charac_data' is declared as CLOB data type.

BFILE:

  • BFILE are the data types that stored the unstructured binary format data outside the database as an operating-system file.
  • The size of BFILE is to a limited operating system, and they are read-only files and can't be modified.

Summary

We have covered the different simple data types that are available in PL/SQL along with their syntax. We will learn about complex data types in further topics.

 

YOU MIGHT LIKE:
PL-SQL

Loops in PL/SQL

In this chapter, we are going to see the loop concept in PL/SQL and flow of control in loops. You...

PL-SQL

Packages in PL/SQL

PL/SQL package is nothing but a logical grouping of a related subprogram (procedure/function) into a...