Data types in SQL defines a sort of value that a database table column can contain. In a database table, each column is required to have a data type with a name.

In this chapter, you will get reference information on the fundamental elements of Oracle SQL. These elements are the simplest building blocks of SQL statements. So basically, you have to be familiarizing yourself with the basic concepts covered in this chapter.

What Are Data Types in SQL?

Every value controlled by Oracle Database has a specific data type. The data type of value links a fixed set of properties with the desired value. These properties treat values of one data type differently from values of another. Let us take an example, where you can add values of NUMBER data type, but not values of a RAW data type. When you create a table or a cluster, you must have to state a data type for each of its columns. When you create a procedure or stored function, you need to state a data type for each of its arguments. These data types classify the domain of values that each column can hold, or each argument can comprise.

Oracle Database provides many built-in data types as well as several categories for user-defined types that can be used as data types. A data type is either scalar or non-scalar. A scalar type holds an atomic value, whereas a non-scalar, which is also called a "collection," contains a set of values. A large object (LOB) is an unusual form of scalar data type which represents a considerable scalar value of binary or character data.

Types of Data Types

User-Defined Types: User-defined data types employ Oracle's built-in data types and few other user-defined data types as the construction blocks for object types, which models the configuration and actions of data in applications.

  • CHAR (character)
  • DEC (Decimal)
  • INT (Integer)
  • REAL

Oracle-Supplied Types: Oracle offers SQL-based interfaces for defining new types when the built-in or ANSI-supported types are not satisfactory. The performance and working for these types can be implemented in C/C++, Java, or PL/ SQL. Oracle Database repeatedly allows the low-level infrastructure services needed for input-output, varied client-side access for new data types, and optimizations for data transferring between the application and the database. The Oracle-supplied types, along with cross-references to the documentation of their implementation listed as following:

  • Any Types
  • XML Types
  • Spatial Types
  • Media Types

General Data Types in SQL

Data type Description
SMALLINT Integer numerical (no decimal). Precision 5
INTEGER(P) or INT(P) Integer numerical (no decimal). Precision P
INTEGER Integer numerical (no decimal). Precision 10
NUMERIC(P,S) Exact numerical (Same as DECIMAL) Precision P and scale value S.
REAL approximate numerical, mantissa precision 7
DECIMAL(P,S) Exact numerical, 'P' is precision value and 'S' is scale value.
DOUBLE PRECISION double precision floating-point number
FLOAT(N) Approximate numerical, floating-point with at least N digits
CHAR(N) or CHARACTER(N) character string. fixed-length N
VARCHAR(N) character string. Variable length. Maximum length N
BIT(N) 'N' is the number of bits to store
BIT VARYING(N) 'N' is the number of bits to store (length can vary up to N)
DATE stores year, month, and day values
TIME stores hour, minute and second values
TIMESTAMP stores year, month, day, hour, minute and second values
TIME WITH exactly same as time but also store an offset from UTC
TIME ZONE of the time specified
TIMESTAMP WITH same as timestamp but also stores an offset from UTC of
TIME ZONE the time specified.