Data Types
When creating a table, you must assign a data type to each column to specify what kind of data be stored in this field. Data types dedicate amount of storage space allocated and provide for a means of error checking. For example, if price field is declared as a number field, it will not accept string values and vice versa.
Some data types are NUMBER,
DATE, CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, NCLOB,
and LONG.
CHAR(n) declares fixed length character string data type. It's good to use when the data is one character for example "m" or "f" other wise it wastes memory. For example, if CHAR(20) is specified and 3 character data is provided, it will still use all the 20 bytes by adding trailing spaces.
VARCHAR2(n) declares variable length character string data type. It eliminates all the trailing spaces. For example, if VARCHAR2(20) is specified and 3 character data is provided, it will only use 3 bytes and no memory wasted. Accepts up to 4000 bytes long.
NUMBER(n) stores number data type both in fixed and floating points. It can store the range from 10^-130 to 10^126. NUMBER(p)means just digits, for example NUMBER(8)stores 8 digits. NUMBER(p,s) stores digits and decimals. For example, NUMBER(8,2) will store 8 digits and 2 decimals.
DATE stores a date value in century, year, month, day, hour, minute, and second format. Stores dates in the range of January 1, 4712BC to December 31, 4712 AD
LONG stores variable length character string up to 4 gigabytes long. Used when working with large amount of text values.
CLOB stores variable length character string up to 2 gigabytes long. Twice the size of long.
NCHAR NVARCHAR2, .... stores the data in unicode format instead of ASCII format.
Data Types Conversion Functions
Sometimes you want to convert a value from one date type to another date type. There are built-in functions available for this type of conversion. Following are some date type conversion examples:
Function | Description | Example |
TO_DATE | Converts character string to date using specific format mask | TO_DATE(‘05/12/2002’, ‘DD/DD/YYYY’); (birth_date,’mm/dd/yyyy’); |
TO_CHAR | Converts a number or date to character string | TO_CHAR(SYSDATE,’MM/DD/YYYY’); TO_CHAR(1); |
| TO_NUMBER | Converts a character to a number | TO_NUMBER(‘123’); |
|