Data types in DBMS

Data Types

Data types in DBMS – All the languages in computer world support datatypes. Datatypes defines the domain of the columns in the table or variables in the code. They define whether the column or variable contains numbers, alphabets, boolean values etc. This is very much important because, it controls the misuse of column – if anyone is trying to insert alphabets into numeric column, it does not allow. If the datatypes of column is not defined, we can store any data in any column, hence making the jumble. Any table needs to be well defined and well structured. Datatypes is to help to create a clean database.

There are six types of inbuilt data types

  1. Scalar data types : – Used to store scalar values like numbers, decimals etc.
  2. Composite data types : – It is combination of other datatypes usually scalar datatypes.
  3. Reference data types : – Used to store the information about another datatype. It can be referred as pointers in C.
  4. LOB data types : – Used to store large objects like images, videos etc
  5. Unknown Column types : – when column types are not known, this datatype is used.
  6. User Define data types : – these datatypes are defined by the developer while coding using the above base datatypes. They define new datatypes to ease their coding. This is can be considered as composite datatype too.

Scalar data types

These datatypes are used to store the numeric column values or numeric variables. Suppose we have a column AGE in a table. This column can have only numbers in it. We cannot insert any date or alphabets into it. Hence we declare this column as NUMBER. This restricts the user while they try to insert any other values other than number. This is one of the mechanisms to maintain the domain constraint of a column.

There are four types of Scalar datatypes – Character, Numeric, Date/Time and Boolean.

Character Data type: –This type of datatype is used to store alpha-numeric values like alphabets as well as numbers and special characters. There are different subtypes in this character datatype depending on the storage space and length of the data to be stored.

While creating a table:

CREATE TABLE STUDENT (STD_NAME CHAR (15), ADDRESS VARCHAR2 (50));

In PL/SQL:

DECLARE
		v_name VARCHAR2 (30);
BEGIN ….
DatatypeSub-DatatypeDescriptionStorage
Character Data TypeCHARUsed to store fixed length of string or value.32767 bytes
CHARACTERThis is same as CHAR, and both are used alternatively. CHAR is most commonly used.32767 bytes
VARCHAR2This is also similar to CHAR, where strings are stored. This datatype will define fixed length but the actual length of the string will be the length of the value i.e.; if VARCHAR2(10) is defined on column NAME and if NAME has one value ‘James’, then the length of that column value is 5, instead of 10. In the case of CHAR, it will be always 10; the space after the names will be filled with NULLS.32767 bytes
VARCHAR2 Subtypes: Following sub type defines same length value.
Sub Data typesDescription
STRINGthey are similar to VARCHAR2, only the name difference
VARCHAR
NCHARStores National Character data (Unicode) within the specified length.32767 bytes
NVARCHAR2This is similar to VARCHAR2, but used to store unicode values.32767 bytes
RAWThis datatype is used for such data like music, video, graphics etc where conversion of datatype between different systems is not required. They are representing in bits and bytes.32767 bytes
LONGUsed to store variable length of strings with backward compatibility. We can store very large data in it. But it is usually recommended to use LOB when there is a need for LONG. Because LOB has less restriction on storage and conversions, and have more features added when there is a new release. LONG columns are preferred in SELCT or UPDATE statements than tables.32760 bytes
LONG RAWThis is combination of LONG and RAW datatypes.32760 bytes
ROWIDThe ROWID data type represents the actual storage address of a row. And table index identities as a logical ROWID. This data type used to store backward compatibility.
UROWID[(size)]The UROWID data type identifies as universal ROWID, same as ROWID data type. Use UROWID data type for developing newer applications.4000 bytes

 

Numeric Datatypes: – Stores different types of numeric data – numbers, decimals etc. Depending on the type we have different sub types of numeric datatypes.

While creating a table:

CREATE TABLE STUDENT (
STD_ID NUMBER (8), ADDRESS VARCHAR2 (50));

In PL/SQL:

DECLARE
		v_total PLS_INTEGER;
BEGIN …
DatatypeSub-DatatypeDescriptionStorage
NUMERIC DATA TypesNUMBER (p, s)NUMBER data type used to store numeric data. Storage Range : Precision range(p) : 1 to 38 and Scale range(s) : -84 to 127
NUMBER Subtypes: This sub type defines different types storage range.
Sub Data typesDescriptionMax Precision
INTEGERThis data types are used to store fixed decimal points. You can use based on your requirements.38 digits
INT38 digits
SMALLINT38 digits
DEC38 digits
DECIMAL38 digits
NUMERIC38 digits
REAL63 binary digits
DOUBLE PRECISION126 binary digits
FLOAT126 binary digits
BINARY_INTEGERBINARY_INTEGER data type store positive and negative values. They require less storage space compare of NUMBER data type values. Storage Range: from -2147483647 to 2147483647.
Sub Data typesDescription 
NATURALOnly Positive values are stored.
POSITIVE
NATURALNNULL values are not stored in this datatype. Only non-null positive values are allowed.
POSITIVEN
SIGNTYPESIGNTYPE allow only -1, 0, and 1 value.
PLS_INTEGERPLS_INTEGER data type used to store signed integers data. They require less storage space compare of NUMBER data type value. Storage Range: from -2147483647 to 2147483647. PLS_INTEGER data type gives better performance on the data. PLS_INTEGER perform arithmetic operation faster than NUMBER / BINARY_INTEGER data type.

Date / Time Datatypes: – These datatypes are used to store date and timestamps in the columns and variables.

DatatypeSub-Datatype

Description

Range

Date / Time Datatype

DATEDATE data type stores valid date-time format with fixed length. Starting date from Jan 1, 4712 BC to Dec 31, 9999 AD.Jan 1, 4712 BC to Dec 31, 9999 AD
TIMESTAMPStores valid date with year, month, day and time with hour, minute, second

Type

TIMESTAMP Type

1

Syntax: TIMESTAMP [(fractional_seconds_precision)]
fractional_seconds_precision optionally specifies the number of digits in the fractional part of the second precision. Range from 0 to 9. The default is 6.
Example: TIMESTAMP ‘2014-04-13 18:10:52.124’

2

Syntax: TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
Example: TIMESTAMP ‘2014-04-13 18:10:52.124 +05:30’
WITH TIME ZONE specify the UTC time zone. Following two values represent the same instant in UTC.
TIMESTAMP ‘1999-04-15 8:00:00 -8:00’ (8.00 AM Pacific Standard Time) or
TIMESTAMP ‘1999-04-15 11:00:00 -5:00’ (11:00 AM Eastern Standard Time) both are same.

3

Syntax: TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE
Example: COL_NAME TIMESTAMP(3) WITH LOCAL TIME ZONE;
WITH LOCAL TIME ZONE specifies when you insert values into the database column, value is stored with the time zone of the database.
The time-zone displacement is not stored in the column. When you retrieve value from Oracle database, returns it according to your UTC local time zone.

Boolean Datatypes: – It is used to store boolean values – TRUE or FALSE. It can also store NULL and is considered as unsigned boolean variable. We cannot compare boolean columns of two tables.

SELECT * FROM EMP
 WHERE IS_EMPLOYED = (SELECT IS_EMPLOYEED 
				FROM EMP WHERE EMP_NAME = ‘John’); -- this query is not valid

Composite data types or User Define data types

Depending upon the need of the program, the developer combines one or more datatype variables into form a one variable. This type of variables will have multiple same or different base datatypes defined in it. These types of variables are used while coding.  This type of datatypes is known as composite or user defined datatypes.

Record is one of the composite datatypes. It can have any number of datatypes within it. One can imagine this as an array with different types of data or as a table itself. Most of the time scalar datatypes are used to create records.

In order to declare a variable as a composite datatype, we have to define the composite datatype according our necessity. Then that datatype can be assigned to other variables to have such records. First, let us see the general syntax for it followed by an example.

TYPE record_type_name IS RECORD 
(Column1 DATATYPE, Column2 DATATYPE… ColumnN DATATYPE);

TYPE rc_emp IS RECORD 
(emp_id NUMBER, emp_name VARCHAR2 (15), date_of_birth DATE); -- An employee record rc_emp with datatypes number, varchar2 and Date is created.

Here rc_emp is the name of the composite datatype.  Above is the syntax for creating the composite datatype. Once it is created, it can be assigned to variable in two methods.

  • vr_emp_record rc_emp; — Declares a variable ‘vr_emp_record’ with three columns of different datatypes. This variable now can hold an array or table of data. Each column inside this variable can be referred as vr_emp_record.emp_id, vr_emp_record.emp_name and vr_emp_record.date_of_birth.
  • Another method is to create individual variables for each column in the record type.
rc_emp_id vr_emp_record.emp_id%TYPE;
rc_emp_name vr_emp_record.emp_name%TYPE;
rc_date_of_birth vr_emp_record. date_of_birth %TYPE; -- vr_emp_record is a record type

This method of declaration is useful while we have to assign the datatypes of existing table columns. In such case no need to create record types, we can directly use tables to assign the datatypes like below. This will help the developer to automatically assign the datatype of the table columns, and he need not check the datatype of each columns. Also, if there are any changes to datatype or length of the columns, then it will be automatically reflected in the code. No need to change the code in such cases.

rc_emp_id EMPLOYEE.emp_id%TYPE;
rc_emp_name EMPLOYEE.emp_name%TYPE;
rc_date_of_birth EMPLOYEE. date_of_birth %TYPE; -- Where EMPLOYEE is a table.

Let us consider an example to understand it better.

DECLARE
      TYPE rc_emp IS RECORD 
	(emp_id NUMBER, 
	emp_name VARCHAR2 (15), 
	date_of_birth DATE); -- Declaring a record type with user defined columns
	
	vr_emp_record rc_emp; -- Declaring a variable of datatype rc_emp
BEGIN
	DBMS_OUTPUT.PUT_LINE (‘EMPLOYEE RECORD’);
	DBMS_OUTPUT.PUT_LINE (‘--------------------------‘);
	-- Accessing the columns of datatype rc_emp
	DBMS_OUTPUT.PUT_LINE (‘Employee ID:’ || vr_emp_record.emp_id);
	DBMS_OUTPUT.PUT_LINE (‘Employee NAME:’ || vr_emp_record.emp_name);
	DBMS_OUTPUT.PUT_LINE (‘Employee Date Of Birth:’ || vr_emp_record.date_of_birth);
END;
DECLARE
	rc_emp EMPLOYEE%ROWTYPE; -- Declaring a table record type 
	rv_emp_name EMPLOYEE.emp_name%TYPE; --declaring a variable with table column type
BEGIN
	DBMS_OUTPUT.PUT_LINE (‘EMPLOYEE RECORD’);
	DBMS_OUTPUT.PUT_LINE (‘--------------------------‘);
	-- Accessing the columns of datatype rc_emp
	DBMS_OUTPUT.PUT_LINE (‘Employee ID:’ || rc_emp.emp_id); 
	DBMS_OUTPUT.PUT_LINE (‘Employee NAME:’ || rc_emp.emp_name);
	DBMS_OUTPUT.PUT_LINE (‘Employee Date Of Birth:’ || rc_emp.date_of_birth);
END;

Here both the block of codes is same and gives the same output (imagine EMPLOYEE table has only three columns as we defined first). The first block of code uses user defined column datatypes in the record variable whereas second block of code uses table and its column to define the variable datatypes.

Advantage of ROWTYPE

Disadvantage of ROWTYPE

Need not explicitly define the column datatypes. They will be automatically retrieved from the tables.When a record is created using ROWTYPE, whole of the columns are assigned to the record variable. Memory will be used to create the datatypes of all the columns. The developer might need only few of the columns to code, but other columns are also unnecessarily created in the record.
When table is altered for some column datatypes or column length, it will be automatically reflected in the code. No need to modify the code when there is table change.

This is how we create a datatype. But it will not have any values of the columns of the table. We have to explicitly assign the values to them. In above blocks of code, we have not assigned any values and it will display nothing. That example was just to demonstrate how to declare and access record variables. We can assign and retrieve values of a record type as follows:

Syntax

Usage

record_name.col_name := value;To directly assign a value to a specific column of a record or to directly assign value to a specific column of record which is declared using %ROWTYPE.
SELECT col1, col2 INTO record_name.col_name1, record_name.col_name2 FROM table_name [WHERE clause];To assign the values of each columns to the record columns
SELECT * INTO record_name FROM table_name [WHERE clause];To assign the values of each columns or the whole table to a record
Variable_name := record_name.col_name;To get a value from a record column and assigning it to a variable.

 

DECLARE
	rc_emp EMPLOYEE%ROWTYPE; -- Declaring a table record type 
	rv_emp_name EMPLOYEE.emp_name%TYPE; --declaring a variable with table column type
BEGIN
	rc_emp.emp_id: = 100; -- Assigning the values
	rc_emp.emp_name: = ‘John’;

	DBMS_OUTPUT.PUT_LINE (‘EMPLOYEE RECORD’);
	DBMS_OUTPUT.PUT_LINE (‘--------------------------‘);
	-- Accessing the columns of datatype rc_emp
	DBMS_OUTPUT.PUT_LINE (‘Employee ID:’ || rc_emp.emp_id);
	DBMS_OUTPUT.PUT_LINE (‘Employee NAME:’ || rc_emp.emp_name);
END;

Similar to Records, we have one more composite datatype called collections. It is similar to arrays and it will have same datatypes. There are three types of collections.

Associative Array or Index by Table: – These are two dimensional array with (key, value) pair. Key is a position identifier of each value. The key can be integer or character/string here. We can have any number of (key, value) pair in it. There is no limit for the number of data in this array, hence unbounded. This is the most frequently used type of collections.

TYPE index_table IS TABLE OF value_datatype [NOT NULL] INDEX BY key_datatype;
v_ index_table index_table;

Above syntax creates a collection with name index_table with values of value_datatype and key with key_datatype. Once the collection is created, collection variable v_index_table is created.

DECLARE
TYPE salary_col IS TABLE OF NUMBER INDEX BY VARCHAR2 (20);
   v_salary_list salary_col;

v_name VARCHAR2 (25);
BEGIN
   -- Assigning the data to tables
   salary_list (‘Sophia’):= 45000;
   salary_list (‘John’):= 75000;
v_name:= salary_list. FIRST; -- Retrieving the first data
DBMS_OUTPUT.PUT_LINE (‘Salary of ‘|| v_name || ‘is ‘|| salary_list (v_name)); --it will display output as ‘Salary of Sophia is 45000. It will not display John’s detail. In order to get his details, we have to reassign v_name:= salary_list. Next and then display
END;

Nested Tables: – These are like one dimensional array, but we can have any number of records in it. The size of this type increases dynamically as and when we get records. Records are added one after the other in a sequence, but when we delete any record from it, the space for deleted record is not deleted. Hence this type of tables will be dense while inserting the data, and gradually becomes sparse. Nested tables can be used as a column in the table as well as can be used in PL/SQL code.

Syntax for this is as below:

TYPE nt_name IS TABLE OF record_datatype [NOT NULL];
table_name nt_name;

where record_datatype can be an any DBMS datatypes or it can be any column type of a table.

E.g.; TYPEnt_salary IS TABLE OF NUMBER;

OR    TYPE nt_salary IS TABLE OF EMPLOYEE.SALARY%TYPE;

Below program shows how to declare, create and access nested tables.

DECLARE
TYPE nt_salary IS TABLE OF NUMBER;
TYPE nt_empname IS TABLE OF VARCHAR2 (20);

   nt_salary_list nt_salary;
 nt_emp_list nt_empname;
BEGIN
   -- Assigning the data to nested tables
 nt_emp_list: = nt_empname (‘Sophia’, ‘James’, ‘Bryan’);
nt_salary_list: = nt_salary (20000, 40000, 60000);

-- Retrieving the records of nested table
FOR i IN 1... nt_emp_list.count LOOP -- nt_emp_list.countwill give total number of records in the nested table
      DBMS_OUTPUT.PUT_LINE (‘Salary For ‘||nt_emp_list (i) || ‘: ‘||nt_salary_list (i)); 
END LOOP;
END;

The Output would be:

Salary for Sophia: 20000

Salary for James: 40000

Salary for Bryan: 60000

Variable array (varray): – This is also similar to nested array and can be considered as one dimensional array, provided it has fixed number of records. Unlike nested tables, we have to declare the number of records in this array in advance and can be used in the program. In addition, we cannot delete the individual records of the array. Because of these features, developers are less likely to use this array. They prefer associative array or nested tables in their programs.

Syntax for Varray is:

TYPE varray_name IS VARRAY (index) OF DATATYPE;

E.g.: – TYPE va_salary IS VARRAY (4) OF NUMBER;

va_salary_list va_salary;

Below program shows how to declare, create and access varray.

DECLARE
		TYPE va_salary IS VARRAY (4) OF NUMBER;
		TYPE va_empname IS VARRAY (4) OF VARCHAR2 (20);
		
   va_salary_list va_salary;
 va_emp_list va_empname;
BEGIN
   -- Assigning the data to varray
 va_emp_list: = va_empname (‘Sophia’, ‘James’, ‘Bryan’);
va_salary_list: = va_salary (20000, 40000, 60000);

-- Retrieving the records of varray
FOR i IN 1... va_emp_list.count LOOP -- va_emp_list.countwill give total number of records in the varray
      DBMS_OUTPUT.PUT_LINE (‘Salary For ‘||va_emp_list (i) || ‘: ‘||va_salary_list (i)); 
END LOOP;
END;

The Output would be:

Salary for Sophia: 20000

Salary for James: 40000

Salary for Bryan: 60000

Comparison of three types of collections is given below.

Collection TypeNumber of RecordsKey TypeDense or SparseWhere CreatedCan Be Object Type Attribute
Associative array (or index-by table)UnboundedString or integerEitherOnly in PL/SQL blockNo
Nested tableUnboundedIntegerStarts dense, can become sparseEither in PL/SQL block or at schema levelYes
Variable-size array (Varray)BoundedIntegerAlways denseEither in PL/SQL block or at schema levelYes

Reference data types

This datatype refers to the existing data in the program. It acts like pointers to the variable. One of the examples of reference datatype is refcursor. They are the cursor variables used to reference and access the static cursor. We can pass this variable to procedures / functions and get the values from function as a refcursor. In short it acts a variable, but reference to the query defined at the runtime.

We can see the difference between cursor and refcursor in below program. We will learn more about it in Advanced SQL article.

DECLARE
           TYPE rc_cursor is ref cursor; 
	CURSOR c_course IS
		SELECT * FROM COURSE;
           l_cursor rc_cursor;
         n_ID NUMBER;
BEGIN
	IF n_ID = 10 THEN
	-- Dynamically opens the cursor for student ids less than 10
		Open l_cursor FOR ‘SELECT * FROM STUDENT WHERE STD_ID<= 10’;
	ELSE	
	-- Dynamically opens the cursor for student ids greater than 10
		OPEN l_cursor FOR ‘SELECT * FROM STUDENT WHERE STD_ID > 10’;
	 END IF;
	-- Opens static cursor c_course
	 OPEN c_course;
END;

LOB data types

These types of datatypes are used to store very large amount of data in a column / variable. It can store file like music, graphics etc. We have following types of LOB datatypes:

DatatypeSub-DatatypeDescriptionStorage

LOB Data type

BFILEUsed to store large binary objects into Operating System file. BFILE stores full file locator’s path which are points to a stored binary object with in server. BFILE data type is read only, you can’t modify them.Size: up to 4GB (232 – 1 bytes)
Directory name: 30 character
File name: 255 characters
BLOBIt is same as BFILE, used to store unstructured binary object into Operating System file. BLOB type fully supported transactions are recoverable and replicated.Size: 8 TB to 128 TB
(4GB – 1) * DB_BLOCK_SIZE
CLOBLarge blocks of character data are stored into Database using this datatype. Store single byte and multi byte character data. CLOB types are fully supported transactions, and are recoverable and replicated.Size: 8 TB to 128 TB
(4GB – 1) * DB_BLOCK_SIZE
NCLOBNCLOB data type to store large blocks of NCHAR data into Database. Store single byte and multi byte character data. NCLOB type fully supported transactions are recoverable and replicated.Size: 8 TB to 128 TB
(4GB – 1) * DB_BLOCK_SIZE

Unknown Column types

We have seen this in the user defined datatypes to declare the variables. This is called unknown columns because this type of datatypes is not base datatypes and is defined by the user or tables. The developer does not predict the datatype of it just by seeing name or declaration. He has to see the definition of record datatype to understand it.

DatatypeSub-Datatype

Description

Unknown Column Datatypes

%TYPEUsed to store single column with unknown datatype. Column is identified by %TYPE data type.
E.g. EMP.ENO%TYPE
%ROWTYPEStores the datatype of all the columns in a table. All columns are identified by %ROWTYPE datatype.
E.g. EMP%ROWTYPE–> a table will be assigned with the EMP table’s entire column datatypes
%ROWIDROWID is data type. ROWID is two type extended or restricted. Extended ROWID return 0 and restricted ROWID return 1 otherwise return the row number.
Some functions are defined on ROWID and are defined in DBMS_ROWID package.

Function ROWID

Description

ROWID_VERIFYVerifies that if the ROWID can be extended.
ROWID_TYPE0 = ROWID, 1 = extended.
ROWID_BLOCK_NUMBERBlock number that contain the record return 1.
ROWID_OBJECTNumber of the object that are in the record.
ROWID_RELATIVE_FNUMBERReturns the Relative file number that contains record.
ROWID_ROW_NUMBERRow number of the Record.
ROWID_TO_ABSOLUTE_FNUMBERReturn the absolute file number.
ROWID_TO_EXTENDEDConverts the ROWID to extended format.
ROWID_TO_RESTRICTEDConverts the ROWID to restricted format.

Variables

Variables are used to store the data temporarily in a program. They act as an intermediary place holder to hold the results while manipulating the data in the program. Each variable will have datatype depending on the kind of data that we are going to store. Datatypes will be same as column datatypes that we saw above.

Syntax for declaring a variable is

Variable_name DATATYPE;

We can even assign default value to the variable. When a variable is declared as NOT NULL, default value has to be assigned.

Variable_name DATATYPE: = value; 
Variable_name DATATYPE NOT NULL: = value; 

n_age NUMBER NOT NULL: = 18;
v_name VARCHAR2 (25);

In a program, we can either directly assign value to a variable or we can use it in a query to get the value of columns into it.

Scope of variables

In a PL/SQL program, we can have multiple blocks i.e.; it has multiple BEGIN – END blocks one inside another.  When variables are declared in these blocks, they act with respect to their blocks.

A variable declared inside a block is accessible to that block alone. Such variables are called local variables. They cannot be accessed by any outer blocks. When we have inner blocks, then we can access the variables declared by its outer blocks. Such variables are called global variables.

DECLARE
	n_age NUMBER; -- Global Variable
BEGIN
	SELECT AGE INTO n_age FROM PERSON WHERE SSN = 323234;
	DECLARE
	v_name VARCHAR2 (30); -- Local Variable, accessible only inside this block
	BEGIN
		SELECT PERS_NAME 
			INTO v_name 
		    FROM PERSON WHERE SSN = 323234;
		DBMS_OUTPUT.PUT_LINE (‘Name of the Person is ‘|| v_name);
	END;
DBMS_OUTPUT.PUT_LINE (‘Age of the Person is ‘|| v_name);
END;

Constants

As the name suggests, they are constant in the program. They are variables with literal value and their remains unchanged throughout the program. These are used to reduce the use of actual values again and again in the program. Declaring a constant and assigning a value once at the beginning, and then using constant throughout the program, makes program clean and understandable. Also, if there is any change to the constant value, we have to change only at one place rather than changing all the code till the end.

Syntax for constant is:

constant_name CONSTANT DATATYPE: = value; -- value has to be assigned without fail, since it is a constant
	pi CONSTANT NUMBER (2, 2): = 3.14;
	incr_percentage CONSTANT NUMBER: = 10;
	declared_holiday CONSTANT VARCHAR2 (7): = ‘SUNDAY’;

In a program these constants are used as any regular variable. Only difference from variable is that we cannot change the value of the constant.

Summary

DATATYPES

 

 

 

SUB DATATYPES

DATATYPES IN SQL

SUB DATATYPES IN SQL

Scalar Datatypes

Character

CHAR

 

CHARACTER

 

VARCHAR2

STRING

VARCHAR

NCHAR

 

NVARCHAR2

 

RAW

 

LONG

 

LONG RAW

 

ROWID

 

UROWID[(size)]

 

Numeric

NUMBER (p, s)

INTEGER

INT

SMALLINT

DEC

DECIMAL

NUMERIC

REAL

DOUBLE PRECISION

FLOAT

BINARY_INTEGER

NATURAL

POSITIVE

NATURALN

POSITIVEN

SIGNTYPE

PLS_INTEGER

 

Boolean

 

 

Date / Time

DATE

 

TIMESTAMP

 

Composite Datatypes / User Define data types

RECORD

 

 

COLLECTION

Associative Array / Index by Table

 

Nested Tables

 

Variable Array (Varray)

 

Reference Datatypes

 

Ref Cursor

 

LOB Datatypes

 

BFILE

 

 

BLOB

 

 

CLOB

 

 

NCLOB

 

Unknown Column Types

 

%TYPE

 

 

%ROWTYPE

 

 

%ROWID

 

Reference

Translate »