Wednesday, October 15, 2014


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 functions are available to convert from one type to another.


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:
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.
This is a fixed-point number with a scale of zero and a precision of p.
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
Instead of storing date and time information in a character or numeric format, IBM created a separate data type. For each DATE data type, the following information is stored:
Century, Year, Month, Day, Hour, Minute, Seconds

You can easily retrieve the current date and time by using the function SYSDATE.
Date arithmetic is possible using number constants or other dates. Only addition and subtraction are supported. For example, SYSDATE + 7 will return one week from today.
Every database system has a default date format that is usually set to DD-MON-YY, where DD is the day of the month (the first day of the month is 01), MON is the abbreviated month name, and YY is a two-digit year designation.


There are four character types available:
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.
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.
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 or group by clauses.
The VARCHAR data type is synonymous with VARCHAR2. Oracle Corporation is reserving this for future use.


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.

No comments: