SQL data types for Oracle/PLSQL

PLSQL datatypes

In this article, we will understand briefly PLSQL datatypes. Each value in PL/SQL such as constant, variable, or parameter has a data type that defines their storage and calculation behavior. PL/SQL has basically two types of data types e.g scalar and composite.

Types of sql data types

Scalar data types

In scalar data type are those data type that stores a single value. Scalar data type has further four data subtypes

  • Number
  • Boolean
  • Character
  • Datetime
Composite data types

Whereas composite data type stores multiple values. There are two data sub types of composite data types are available.

  • Record
  • Collection

In this article, we will be discussing only scalar data type

Oracle number data type

The numeric data type represents real numbers, integer, and floating numbers. It can store numbers in fixed and floating-point format. It provides precision up to 38 decimals. It cal also stores positive and negative numbers along with zero.

In sql while creating a table we can assign column as below

For numeric columns we can specify the column as a floating-point number:

column_name NUMBER

Also, we can specify a precision (total number of digits) and scale (number of digits to right of decimal point):

column_name NUMBER (precision, scale)

Oracle boolean data type

The BOOLEAN datatype has three data values: TRUE, FALSE, and NULL. Boolean values are typically used in control flow structures such as IF-THEN, CASE, and loop statements like LOOP, FOR LOOP, and WHILE LOOP. As SQL doesn’t have boolean data type so we can’t use it in SQL column or SELECT statement or in SQL function

Character data types

The SQL character data types represent alphanumeric text. PL/SQL uses the SQL character data types such as CHAR, VARCHAR2, LONG, RAW, LONG RAW, ROWID, and UROWID.

  • CHAR(n) is a fixed-length character type whose length is from 1 to 32,767 bytes.
  • VARCHAR2(n) is varying length character data from 1 to 32,767 bytes.
  • LONG variable for each row in the table, up to 2^31 – 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility.
  • RAW variable for each row in the table, up to 2000 bytes per row. A maximum size must be specified. Provided for backward compatibility.
  • LONG RAW Variable for each row in the table, up to 2^31 – 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility.
  • ROWID Fixed at 10 bytes (extended ROWID) or 6 bytes (restricted ROWID) for each row in the table.

Datetime data types

The datetime data types represent dates, timestamp with or without time zone, and intervals. PL/SQL DateTime data types are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND.

  • Date format : For input and output of dates, the standard Oracle default date format is DD-MON-YY. To chnage date format we can use TO_DATE function.
  • Time format : Time is stored in 24-hour format#HH:MM:SS.By default, the time in a date field is 12:00:00 A.M. (midnight) if no time portion is entered.

You can read more about SQL data types from this link.

To know about how to install oracle database click here.

Leave a Comment