Oracle Data Type

The data type (datatype) is an attribute in the column or stored procedure.

Oracle supports three basic data types:

• Character Data Type
• Digital Data Type
• Other data types

Character Data Type

CHAR: the char data type stores fixed-length character values. A char data type can contain 1 to 2000 characters. If the length of a char is not explicitly stated, its default length is set to 1. If a char type variable is assigned a value whose length is smaller than the specified length, Oracle will automatically fill it with spaces.

Varchar2: stores variable-length strings. Although the length of a varchar2 data variable must also be specified, this length refers to the maximum length assigned to the variable rather than the actual value length. No space is required. It can be set to a maximum of 4000 characters.

Because the varchar2 data type only stores the characters assigned to this column (without spaces), varchar2 requires less storage space than the char data type.

Varchar2 is recommended for Oracle

The nchar, nvarchar2, and nvarchar2 data types store character data of Fixed Length and variable length, but they use character sets different from other database types. When creating a database, you must specify the character set used to encode the data in the database. You can also specify a secondary character set [National Language Set (NLS)]. Nchar and nvarchar2 columns use secondary character sets.

In Oracle9i, the length of the nchar and nvarchar2 columns can be expressed in characters rather than bytes.

Long: The long data type can store 2 GB of character data, which is inherited from earlier versions. To store large data volumes, we recommend that you use the clob and nclob Data Types in Oracle. There are many restrictions on using the long type in tables and SQL statements.

This data type should be basically useless.

Clob and nclob: clob and nclob data types can store up to 4 GB of character data. The nclob data type can store NLS data.

Digital Data Type

Oracle uses standard, variable-length internal formats to store numbers. The internal format precision can be up to 38 BITs, that is, the number data type.

The number data type can have two delimiters, for example:

Column number (precision, scale)

Precision indicates the valid digit in the number. If precision is not specified, Oracle uses 38 as the precision.

Scale indicates the number of digits to the right of the decimal point. scale is set to 0 by default. If scale is set to a negative number, Oracle will select this number to the specified number to the left of the decimal point.

Date data type

Oracle standard Date Format: DD-MON-YY hh: MI: SS

You can modify the format of the Instance insertion date by modifying the instance parameter nls_date_format. During a session, you can use the alter session SQL command to modify the date, or use the parameters in the to_date expression of the SQL statement to update a specific value.

 

Raw, long raw, and long raw data types are mainly used to explain the database. When the two types are specified, Oracle stores data in bits. Raw data is generally used to store objects in a specific format, such as bitmap. The raw data type can occupy 2 kb space, while the long raw data type can occupy 2 GB space.

Rowid is a special column type. It is called a pseudo column ). Rowid pseudo columns can be accessed in SQL select statements as normal columns. Each row in the Oracle database has a pseudo column. Rowid indicates the address of the row. The rowid pseudo column is defined by the rowid data type.

Rowid is related to the specific location of the disk drive. Therefore, rowid is the fastest way to obtain rows. However, the rowid of the row changes with the uninstallation and heavy load of the database. Therefore, we recommend that you do not use the value of the rowid pseudo column in the transaction. For example, once the current application has used the record, there is no reason to save the rowid of the row. You cannot use any SQL statement to set the value of the standard rowid pseudo column.

A column or variable can be defined as the rowid data type, but Oracle cannot guarantee that the value of this column or variable is a valid rowid.

Lob

Lob (large object) data type, can save 4 GB of information. Lob has the following types:

. Clob can only store character data

. Nclob: stores the character set data of the local language.

. Blob, which stores data in binary format.

You can specify whether to save a lob data in the Oracle database or point to an external file containing the secondary data.

Lob can participate in transactions. To manage data in lob, you must use the dbms_lob PL/SQL built-in software package or OCI interface.

To convert the long data type to lob, Oracle9i contains many functions that support both lob and long, and a new alter table statement, it allows automatic conversion of long data type to lob.

Bfile

Bfile data types are used as pointers to files stored outside the Oracle database.

Xmltype

As part of XML support, Oracle9i contains a new data type xmltype. Columns defined as xmltype will be stored in an XML document in the character lob column. There are many built-in functions that allow you to extract a single node from your notebook, and you can also create an index for any node in the xmltype document.

User-Defined data

After oracle8, users can define their own complex data types, which are composed of Oracle basic data types.

Anytype, anydata, and anydataset

Oracle includes three new data types, which are used to define data structures other than existing data types. Each data type must be defined by a program unit to let Oracle9i know how to handle specific implementations of these types.

Type conversion

Oracle automatically converts some data types to other data types. The conversion depends on the SQL statement that includes this value.

Data conversion can also be performed through the Oracle type conversion function display.

Connection and Comparison

On most platforms, the connection operators in Oracle SQL are represented by two vertical bars (|. Concatenates two character values. Oracle's automatic type conversion function allows two numeric values to be connected.

Null

Null is an important feature of relational databases. In fact, null does not represent any value. It indicates no value. If you want to create a column in the table and the column must have a value, you should specify it as not null, which indicates that the column cannot contain null values.

Any data type can be assigned a null value. The Null Value introduces the three-state logic of SQL operations. If one side of the comparison is null, three statuses will appear: True, false, and none of them.

Because the null value is not equal to 0 or any other value, you can only use the relational operator is null to test whether a data value is null.

The null value is especially suitable for the following situations: when a column has not been assigned a value. If the null value is not selected, all columns of the row must be assigned a value. This actually removes the possibility that a column does not require a value, and the value assigned to it is easy to misunderstand. In this case, the end user may be misled and the cumulative operation error result may be returned.