Oracle - DataType

Card Puncher Data Processing

About

Each value manipulated by Oracle Database has a datatype.

Type

Built-In

Large Object: Oracle - LOB Datatype

Data Type Description
Oracle Database - Character DataType
VARCHAR2(size [BYTE | CHAR]) Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2. BYTE indicates that the column will have byte length semantics; CHAR indicates that the column will have character semantics.
NVARCHAR2(size) Variable-length character string having maximum length size characters. Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.
LONG Character data of variable length up to 2 gigabytes, or 231 -1 bytes. no more supported
CHAR(size [BYTE | CHAR]) Fixed-length character data of length size bytes. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte. BYTE and CHAR have the same semantics as for VARCHAR2.
NCHAR(size) Fixed-length character data of length size characters. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character.
CLOB A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes - 1) * (database block size).
NCLOB A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported, both using the database national character set. Maximum size is (4 gigabytes - 1) * (database block size). Stores national character set data.
Oracle Database - Datatype Datetime
DATE Valid date range from January 1, 4712 BC to December 31, 9999 AD.
TIMESTAMP (fractional_seconds_precision) Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6.
TIMESTAMP (fractional_seconds_precision) WITH TIME ZONE All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6.
TIMESTAMP (fractional_seconds_precision) WITH LOCAL TIME ZONE All values of TIMESTAMP WITH TIME ZONE, with the following exceptions:
* Data is normalized to the database time zone when it is stored in the database.
* When the data is retrieved, users see the data in the session time zone.
INTERVAL YEAR (year_precision) TO MONTH Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2.
INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision) Stores a period of time in days, hours, minutes, and seconds, where:
* day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.
* fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6.
Oracle Database - Numeric Datatype
NUMBER(p,s) for fixed-point or floating-point number and integer
BINARY_FLOAT for floating-point number
BINARY_DOUBLE for floating-point number
Others
RAW(size) Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.
LONG RAW Raw binary data of variable length up to 2 gigabytes.
ROWID Base 64 string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.
UROWID [(size)] Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes.
BLOB A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).
BFILE Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.

user-defined

see CREATE TYPE

Management

View

To view the data types contained in a database table, such as the employees table, you can use:

Modify

alter table modify column clause

Example:

alter schema.table modify column columnName [datatype] [default expr];

Documentation / Reference





Discover More
Owb Process Flow Parameter Scope And Direction
OWB - Process Flow Parameters and Variables

All mapping, activity in OWB is a program and then you can pass them a variable. For each parameter, Warehouse Builder defines a : Name, Direction, A direction of IN indicates that the parameter...
Card Puncher Data Processing
Oracle Database - Character DataType

You can use the following SQL data types to store alphanumeric data: VARCHAR2 and NVARCHAR2 data types store variable-length character literals. NCHAR and NVARCHAR2 data types store variable-length...
Card Puncher Data Processing
Oracle Database - Character Set

Oracle Database uses the database character set for: Data stored in SQL character datatypes (CHAR, VARCHAR2, CLOB, and LONG). Identifiers such as table names, column names, and PL/SQL variables. ...
Card Puncher Data Processing
Oracle Database - Data type Conversion

Data type Conversion. In an Oracle Net connection with Java, this task is given to the Two-Task Common (TTC) protocol. In SqlPlus: Implicit...
Card Puncher Data Processing
Oracle Database - Datatype difference between VARCHAR2 and CHAR

Varchar2 and char are two datatype used to store the same type of data: a string. But where are the difference ? The CHAR data type blank-pads and stores trailing blanks up to a fixed column length for...
Card Puncher Data Processing
Oracle Database - Floating-Point Number Datatype (BINARY_FLOAT, BINARY_DOUBLE)

Oracle provides two special datatype to store floating-point data: Datatype Storage Format BINARY_FLOAT single precision (32 bit) IEEE 754...
Card Puncher Data Processing
Oracle Database - How to load a CLOB field with an external table

A CLOB (or character large object) is a Oracle datatype that can contain single-byte or multibyte characters with a maximum size of (4 gigabytes - 1) (database block size), then more than the varchar2...
Card Puncher Data Processing
Oracle Database - Java Stored Procedure

Application developers familiar with procedural programming styles developed business application logic using languages like PL/SQL. The business logic they developed was deployed as stored program units...
Card Puncher Data Processing
Oracle Database - Long (Text) and Long Raw Datatype

LOB Oracle also recommends that you convert existing LONG columns to LOB columns. LOB columns are subject to far fewer restrictions than LONG columns. Further, LOB functionality is enhanced in every release,...
Card Puncher Data Processing
Oracle Database - NLS_DATE_FORMAT

The parameter NLS_DATE_FORMAT control the format of the date datatype. You can see here to see the different format...



Share this page:
Follow us:
Task Runner