Oracle Internal Datatypes with Examples
Oracle Internal Datatypes with Examples:
In order to create a table we need to specify a datatype for individual columns in the create table command. Oracle supports the following datatypes, to achieve the above requirements.
Character Datatypes
The following are the character datatypes supported by Oracle.
Char datatype
The Char datatype is used when a fixed length character string is required. It can store alphanumeric values. The column length of such a datatype can vary between 1 – 2000 bytes. By default it is one byte.
♦ If the user enters a value shorter than the specified length then the database blank-pads to the fixed length.
♦ In case, if the user enters a value larger than the specified length then the database would return an error.
Varchar2 datatype
The Varchar2() datatype supports a variable length character string. It also stores alphanumeric values. The size for this datatype ranges from 1 – 4000 bytes. While defining this datatype we should specify the size. Using varchar2 saves disk space when compared to char. This statement can be justified with the help of an example. Considering a column assigned with varchar2 datatype of size 30 bytes, if the user enters 10 bytes of character, then the column length in that row would only be 10 bytes and not 30 bytes. In the case of char, it would still occupy 30 bytes because the remaining would be blank padded by Oracle.
Note: Currently varchar datatype is equivalent to varchar2 datatype.
Long datatype
This datatype is used to store variable character length. Maximum size is 2 GB. Long datatype has several characters similar to varchar2 datatype. Its length would be restricted based on the memory space available in the computer. The following restriction needs to be fulfilled when a long datatype attribute is cast on a column in a table.
♦ Only one column in a table can have long datatype. This should not contain unique 10 or primary key constraints.
♦The column cannot be indexed.
♦Procedures or stored procedures cannot accept long datatype as arguments.
| Note: Although the LONG data type was first introduced in earlier versions of Oracle, Oracle 21c continues to support it for backward compatibility. |
Number datatype
The Number datatype can store positive numbers, negative numbers, zeroes, fixed point numbers, and floating point numbers with a precision of 38.
♦column_name number { p = 38, s = 0 }
♦ column_name number(p) { fixed point}
♦ column_name number(p, s) {floating point}
Where p is the precision which refers to the total number of digits, it varies between 1 to 38, s is the scale which refers to number of digits to the right of the decimal point, which varies between -84 to 127.
Date datatype
Date datatype is used to store date and time in a table. Oracle database makes use of its own format to store date in a fixed length of 7 bytes each for century, month, day, year, hour, minute and second. Default date datatype is “dd-mon-yy”. To view system’s date and time we can use the SQL function called sysdate (). Valid date is from Jan 1, 4712 BC to Dec 31, 4712 AD.
Raw datatype
Raw datatype is used to store byte oriented data like binary data or byte strings and the maximum size of this datatype is 2000 bytes. While using this datatype the size should be mentioned because by default it does not specify any size. Only storage and retrieval of data are possible, manipulations of data cannot be done. Raw datatype can be indexed.
Long raw datatype
Long Raw datatype is used to store binary data of variable length, which can have a maximum size of 2 GB. This datatype cannot be indexed. Further all limitations faced by long datatype also holds good for long raw datatype.
In addition to the above mentioned data types Oracle supports:
♦ CLOB
♦ BLOB
♦ BFILE
LOB datatype
LOB is otherwise known as Large Object data types. This can store unstructured information such as sound clips, video files etc., up to 4 gigabytes in size. They allow efficient, random, piece-wise access to the data. The LOB types store values, which are known as locators. These locators store the location of the large objects. The location may be out-of-line(not within the table) or in an external file. LOB values can be operated upon through the use of the locators. The DBMS_LOB package can be used to manipulate LOBs.
LOBS can be either external or internal depending on their location with regard to the los database. Data stored in a LOB column is known as LOB value.
Internal LOBs are stored in the database tablespace. This provides efficient access and optimization of space. External LOBS are also referred to as BFILE. These are stored in operating system files outside the database tablespace. These files use reference semantics. They may be stored in CD-ROMs, PhotoCDs or hard disks etc. But the storage cannot extend from one device to another. The external LOBs do not participate in transactions.
Changes to the internal LOBS can be done using SQL DML or through the packages provided in PL/SQL called DBMS_LOB. Changes can also be done through a series of API calls from the Oracle Call Interface (OCI). Changes can be made to an entire internal LOB or piecewise to the beginning, middle or end of an internal LOB. Both internal and external LOBs can be accessed for read purpose.
PL/SQL8 also provides a set of intrinsic datatypes for the support of LOBS; although SQL cannot directly manipulate these datatypes, they are accessible from SQL through PL/SQLAR function calls.
CLOB
A column with its data type as CLOB stores character objects with single byte characters. It cannot contain character sets of varying widths. A table can have multiple columns with CLOB as its data type.
BLOB
A column with its data type as BLOB can store large binary objects such as graphics, video clips and sound files. A table can have multiple columns with BLOB as its data type.
BFILE
A BFILE column stores file pointers to LOBS managed by file systems external to the database. A BFILE column may contain filenames for photos stored on a CD-ROM, 80.J
For the sake of backward compatibility Oracle supports all other data types such as RAW and LONGRAW. The LOB data types have several advantages over the datatypes already present in previous versions of Oracle. The advantages are:00
♦ A table can have multiple LOB columns
♦ Multiple LOBS are allowed in a single row
♦ LOBS can be attributes of a user defined data type
♦ A table stores small locators for the LOBs in a column in place of the actual objects. In contrast a table stores the LONG column within the table itself.
♦ A LOB column can have storage characteristics different from those for the table. It is possible to separate the primary table data from those of the LOB columns to different physical locations.
♦ Accessing the LOB column returns the locator.
♦ The maximum size of a LOB column is 4 gigabytes
♦ Applications can manipulate and access pieces of the LOB. However, for LONG datatype the entire data must be accessed.
The LOBs can be incorporated into tables. When defining a LOB column in a table explicit definition of the tablespace and the storage characteristics can be specified for each internal LOB. There are no specific storage characteristics for external LOBS since they are not stored in the database. The advantage of specifying the storage characteristics for the LOB columns is that querying of the database becomes easier. Specifying the storage characteristics for the LOB columns can have performance improvement and decrease in contention for the table’s tablespace. LOB columns can be indexed. The index on the LOB column cannot be dropped and rebuilt.
The user can specify setting of internal LOBS to null or empty. An empty LOB stored in a table is a LOB of zero length and is assigned a locator. This locator can be used to populate the LOB later.
Note: A tablespace can be referred to as logical storage units which make up the database. It is normally the job of a DBA to create, add and drop a tablespace though a user can also perform the same. Defining a tablespace for the LOB is optional.
Design Considerations
There are some decisions that need to be made as a designer before the new large object features are used. If a column has data that needs to participate in database transactions then we can make use of LOB. If a column has data that need not necessarily participate in database transactions then we can make use of BFILE. There is no overhead involved in loading a BFILE and hence they appear more attractive than a LOB. But while using a BFILE the following considerations have to be taken into account
♦ Oracle makes no attempt to control what users of the operating system do with external files. A user might delete or copy another file over the external file. Doing so potentially violates the referential integrity of the data and is always a risk.
♦ When a BFILE is deleted from the database, only the database reference is actually deleted, and the file remains unaffected. When the BFILE is used periodic process need to be designed to clean up orphan BFILES whose references have all been deleted or which were created as part of a transaction which was rolled back. Of course, with data held inside the database, Oracle has more control over who accesses and updates the data, and manages the housekeeping tasks of cleaning up deleted data.
In general we can use the LOB instead of the BFILE. Unless there is a compelling reason to use the BFILE it is better to use the LOB.
LOBs are subject to read consistency. Read consistency means that the data visible to a query has a read consistency point, usually the time at which statement execution was initiated. If the data has been changed since the read consistency point, Oracle achieves consistency once again by reconstructing the original state from the rollback segments.
Example for Number, Character, Varchar, date Datatypes:
| SQL> create table emp(eid number,ename varchar2(40),gender char(1),hire_date date);
Table created. SQL> desc emp; |
Example for Long datatype:
| CREATE TABLE long_text ( id NUMBER, long_desc LONG ); |
Now we will insert the values into the following given table
| INSERT INTO long_text (id, long_desc) VALUES (1, ‘This datatype is used to store variable character length. Maximum size is 2 GB. Long datatype has several characters similar to varchar2 datatype. Its length would be restricted based on the memory space available in the computer. The following restriction needs to be fulfilled when a long datatype attribute is cast on a column in a table.’); |
Retriving Data from Long Datatype:
| SELECT long_desc FROM long_text WHERE id = 1;
LONG_DESC Note: In the above output, we retrived only 80 characters because of the default setting of long datatype as shown below: SQL> show long;
To set more size of the long: SQL> set long 999999; LONG_DESC SQL> |
Example for Raw Datatype:
| CREATE TABLE bin_data (id NUMBER, bin_file RAW(4000));
In this example, we create a table called “bin_data” with two columns: “id” and “bin_file”. The “bin_file” column is of type RAW and can store binary data up to a maximum size of 4000 bytes. Now we will insert the data into a following given table INSERT INTO bin_data (id, bin_file) select* from bin_data; |
Example for CLOB datatype:
| CREATE TABLE emp1 ( emp_id NUMBER, emp_name VARCHAR2(30), desc CLOB ); The “desc” ( means description ) column in this example has a CLOB data type.Now we will insert the values in a given tableINSERT INTO emp1 (emp_id, emp_name, description) VALUES (101, ‘Tom’, ‘This is a description for CLOB data type’); |