This Post does not give a lesson on data modeling and creating a proper database schema. In order to write proper SQL statements, familiarity with database objects (tables, views, constraints) are essential.
One general rule to follow when you are writing SQL statements is that data types cannot be mixed. Conversion utilities are available to convert from one type to another. These conversion functions are covered later in this Post.
Numeric
The NUMBER data type is used to store zero, negative, positive, fixed, and floating point numbers with up to 38 digits of precision. Numbers range between 1.0x10 -130 and 1.0x10 126.
Numbers can be defined in one of three ways:
NUMBER(p,s)
where p is the precision up to 38 digits and s is the scale (number of digits to the right of the decimal point). The scale can range between -84 to 127.
NUMBER (p)
This is a fixed-point number with a scale of zero and a precision of p.
NUMBER
This is a floating-point number with a precision of 38.
The following table shows how Oracle stores different scales and precisions:
Actual Data Defined as Stored as
123456.789 NUMBER(6,2) 123456.79
123456.789 NUMBER(6) 123457
123456.789 NUMBER(6,-2) 123400
123456.789
NUMBER
123456.789
Character
There are four character types available:
1. The CHAR data type is used where fixed-length fields are necessary. Any length up to 255 characters can be specified. The default length is 1. When data is entered, any space left over will be filled with blanks. All alpha-numeric characters are allowed.
2. The VARCHAR2 is used for variable-length fields. A length component must be supplied when you use this data type. The maximum length is 2000 characters. All alpha-numeric characters are allowed.
3. The LONG data type is used to store large amounts of variable-length text. Any length up to 2 GB can be specified. Be aware that there are some restrictions to using this data type, such as:
Only one column per table can be defined as LONG.
A LONG column cannot be indexed.
A LONG column cannot be passed as an argument to a procedure.
A function cannot be used to return a LONG column.
A LONG column cannot be used in where, order by, group by, or connect by clauses.
4. The VARCHAR data type is synonymous with VARCHAR2. Oracle Corporation is reserving this for future use. Do not use this data type.
Binary
Two data types, RAW and LONGRAW, are available for storing binary type data such as digitized sound and images. These data types take on similar characteristics as the VARCHAR2 and LONG data types already mentioned.
Use the RAW data type to store binary data up to 2000 characters and use the LONGRAW data type to store binary data up to 2 GB.
Oracle only stores and retrieves binary data; no string manipulations are allowed. Data is retrieved as hexadecimal character values.
Others
Every row in the database has an address. You can retrieve this address by using the ROWID function. The format of the ROWID is as follows:
BLOCK.ROW.FILE
BLOCK is the data block of the data FILE containing the ROW. The data is in hexadecimal format and has the data type ROWID.
MLSLABEL is a data type used to store the binary format of a label used on a secure operating system.