Taking this into account, the column data size for a particular numeric data value NUMBER (p), where p is the precision of a given value (scale has no effect), can be calculated using the following formula:

  1 byte			(exponent) 
  FLOOR(p/2)+1 bytes	(mantissa) 
+ 1 byte			(only for a negative number where
					the number of significant digits is
					less than 38) 
_______________________    
number of bytes of data 

Zero and positive and negative infinity (only generated on import from Version 5 Oracle databases) are stored using unique representations; zero and negative infinity each require one byte, while positive infinity requires two bytes.

DATE Datatype

Oracle uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.

For input and output of dates, the standard Oracle default date format is DD-MON-YY, as below:

'13-NOV-92' 

You can change this default date format for an instance with the parameter NLS_DATE_FORMAT. You can also change it during a user session with the ALTER SESSION statement. To enter dates that are not in standard Oracle date format, use the TO_DATE function with a format mask:

TO_DATE ('November 13, 1992', 'MONTH DD, YYYY') 

Note: If you use the standard date format DD-MON-YY, YY indicates the year in the 20th century (for example, 31-DEC-92 is December 31, 1992). If you want to indicate years in any century other than the 20th century, use a different format mask, as shown above.

Oracle stores time in 24-hour format -- HH:MI:SS. By default, the time in a date field is 12:00:00 A.M. (midnight) if no time portion is entered. In a time-only entry, the date portion defaults to the first day of the current month. To enter the time portion of a date, the TO_DATE function must be used with a format mask indicating the time portion, as in

INSERT INTO birthdays (bname, bday) VALUES 
('ANDY',TO_DATE('13-AUG-66 12:56 A.M.','DD-MON-YY HH:MI A.M.')); 

Using Julian Dates

The format mask "J" can be used with date functions (TO_DATE or TO_CHAR) to convert date data into Julian dates. For example, the following query returns all dates in Julian date format:

SELECT TO_CHAR (hiredate, 'J') FROM emp; 

You must use the TO_NUMBER function if you want to use Julian dates in calculations. You can use the TO_DATE function to enter Julian dates:

INSERT INTO emp (hiredate) VALUES (TO_DATE(2448921, 'J')); 

Date Arithmetic

You can enter missing dates into the database, but they are ignored in date arithmetic and treated as the next "real" date. For example, the next day after 04-10-1582 is 15-10-1582, and the day following 05-10-1582 is also 15-10-1582.

Note: This discussion of date arithmetic may not apply to all countries' date standards (such as those in Asia).

LONG Datatype

Uses of LONG Data

Restrictions on LONG and LONG RAW Data

• Only one LONG column is allowed per table.

• LONG columns cannot be indexed.

• LONG columns cannot appear in integrity constraints.

• LONG columns cannot be used in WHERE, GROUP BY, ORDER BY, CONNECT BY clauses, or with the DISTINCT operator in SELECT statements.

• LONG columns cannot be referenced by SQL functions (such as SUBSTR or INSTR).

• LONG columns cannot be used in the SELECT list of a subquery or queries combined by set operators (UNION, UNION ALL, INTERSECT, or MINUS).

• LONG columns cannot be used in expressions.

• LONG columns cannot be referenced when creating a table with a query (CREATE TABLE . . . AS SELECT . . .) or when inserting into a table (or view) with a query (INSERT INTO . . . SELECT . . .).

• A variable or argument of a PL/SQL program unit cannot be declared using the LONG datatype.

• Variables in database triggers cannot be declared using the LONG or LONG RAW datatypes.

• References to :NEW and :OLD in database triggers cannot be used with LONG or LONG RAW columns.

RAW and LONG RAW Datatypes

RAW is a variable-length datatype like the VARCHAR2 character datatype, except that SQL*Net (which connects user sessions to the instance) and the Import and Export utilities do not perform character conversion when transmitting RAW or LONG RAW data. In contrast, SQL*Net and Import/Export automatically convert CHAR, VARCHAR2, and LONG data between the database character set to the user session character set (set by the NLS_LANGUAGE parameter of the ALTER SESSION command), if the two character sets are different.

When Oracle automatically converts RAW or LONG RAW data to and from CHAR data, the binary data is represented in hexadecimal form with one hexadecimal character representing every four bits of RAW data. For example, one byte of RAW data with bits 11001011 is displayed and entered as 'CB'.

LONG RAW data cannot be indexed, but RAW data can be indexed.

ROWIDs and the ROWID Datatype

Each table in an Oracle database internally has a pseudocolumn named ROWID. This pseudocolumn is not evident when listing the structure of a table by executing a SELECT * FROM . . . statement, or a DESCRIBE . . . statement using SQL*Plus. However, each row's address can be retrieved with a SQL query using the reserved word ROWID as a column name:

SELECT ROWID, ename FROM emp; 

ROWIDs use a binary representation of the physical address for each row selected. When queried using SQL*Plus or Server Manager, the binary representation is converted to a VARCHAR2/hexadecimal representation. The above query might return the following row information:

ROWID              ENAME 
------------------ ---------- 
00000DD5.0000.0001 SMITH 
00000DD5.0001.0001 ALLEN 
00000DD5.0002.0001 WARD 

As shown above, a ROWID's VARCHAR2/hexadecimal representation is in a three-piece format: block.row.file.

• The data block that contains the row (block DD5 in the example). Block numbers are relative to their datafile, not tablespace. Therefore, two rows with identical block numbers could reside in two different datafiles of the same tablespace.

• The row in the block that contains the row (rows 0, 1, 2 in the example). Row numbers of a given block always start with 0.

• The datafile that contains the row (file 1 in the example). The first datafile of every database is always 1, and file numbers are unique within a database.

You cannot set the value of the pseudocolumn ROWID in INSERT or UPDATE statements. Oracle uses the ROWIDs in the pseudocolumn ROWID internally for various operations as described in the following section. Though you can reference ROWIDs in the pseudocolumn ROWID like other table columns (used in SELECT lists and WHERE clauses), ROWIDs are not stored in the database, nor are they database data.

ROWIDs and Non-Oracle Databases Oracle database applications can be executed against non-Oracle database servers using SQL*Connect or the Oracle Open Gateway. In such cases, the binary format of ROWIDs varies according to the characteristics of the non-Oracle system. Furthermore, no standard translation to VARCHAR2/hexadecimal format is available. Programs can still use the ROWID datatype; however, they must use a non-standard translation to hexadecimal format of length up to 256 bytes. Refer to the relevant manual for OCIs or Precompilers for further details on handling ROWIDs with non-Oracle systems.

How ROWIDs Are Used

End-users and application developers can also use ROWIDs for several important uses:

• ROWIDs are the fastest means of accessing particular rows.

• ROWIDs can be used to see how a table is organized.

• ROWIDs are unique identifiers for rows in a given table.

You can also create tables with columns defined using the ROWID datatype. For example, you can define an exception table with a column of datatype ROWID to store the ROWIDs of rows in the database that violate integrity constraints. Columns defined using the ROWID datatype behave like other table columns; values can be updated, and so on. Each value in a column defined as datatype ROWID requires six bytes to store pertinent column data.

Examples of Using ROWIDs

Use the function SUBSTR to break the data in ROWID into its three components (file, block, and row). For example:

SELECT ROWID, SUBSTR(ROWID,15,4) "FILE", 
	   SUBSTR(ROWID,1,8) "BLOCK", 
	   SUBSTR(ROWID,10,4) "ROW" 
	   FROM products; 

 ROWID               FILE  BLOCK     ROW 
------------------  ----  --------  ---- 
00000DD5.0000.0001  0001  00000DD5  0000 
00000DD5.0001.0001  0001  00000DD5  0001 
00000DD5.0002.0001  0001  00000DD5  0002 

ROWIDs can be useful for revealing information about the physical storage of a table's data. For example, if you are interested in the physical location of a table's rows (such as for table striping), the following query tells how many datafiles contain rows of a given table:

SELECT COUNT(DISTINCT(SUBSTR(ROWID,15,4))) "FILES" FROM tablename;  
  FILES 
-------- 
    2 

For more information on how to use ROWIDs, refer to the Oracle7 Server SQL Reference, the PL/SQL User's Guide and Reference, Oracle7 Server Tuning, and other books that document Oracle tools and utilities.

The MLSLABEL Datatype

The MLSLABEL datatype is used to store the binary format of an operating system label. The maximum width of a column declared as MLSLABEL is 255 bytes.

MLSLABEL data is stored as a variable-length tag (two to five bytes, in which the first byte indicates length) that maps to a binary label in the data dictionary. The reason that MLSLABEL data is stored as a representative tag instead of the binary label itself is that binary operating system labels can be very long. Given that a label is stored with every row in the database (in the ROWLABEL column), storing many large labels can consume a lot of space. Storing a representative tag instead of a label is more space efficient.

Any labels that are valid on your operating system can be inserted into an MLSLABEL column. When you insert a label into an MLSLABEL column, Trusted Oracle implicitly converts the data into the binary format of the label.

The following sections further describe this datatype and the ROWLABEL pseudocolumn. If you are using Trusted Oracle, also see the Trusted Oracle7 Server Administrator's Guide for more information.

The ALL_LABELS Data Dictionary View

Note that this view does not necessarily contain all labels that are valid in the database, since any valid operating system label, in any valid format, is a valid label within Trusted Oracle. Also note that this view may contain labels that are invalid within the database (if those labels were once used in the database, but are no longer valid).

The ROWLABEL Column

In OS MAC mode, given that a table can contain rows at one label only, the values in this column are always uniform within a table (and within a single database).

In DBMS MAC mode, the values in this column can range within a single table from DBHIGH to DBLOW (within any constraints defined for that table).

Summary of Oracle Datatype Information

Data Type	Description	Column Length (bytes)
CHAR (size)	Fixed-length character data of length size.	Fixed for every row in the table (with trailing blanks); maximum size is 255 bytes per row, default size is one byte per row. Consider the character set that is used before setting size. (Are you using a one-byte or multi-byte character set?)
VARCHAR2 (size)	Variable-length character data. A maximum size must be specified.	Variable for each row, up to 2000 bytes per row. Consider the character set that is used before setting size. (Are you using a one-byte or multi-byte character set?)
NUMBER (p, s)	Variable-length numeric data. Maximum precision p and/or scale s is 38.	Variable for each row. The maximum space required for a given column is 21 bytes per row.
DATE	Fixed-length date and time data, ranging from January 1, 4712 B.C. to December 31, 4712 A. D. Default format: DD-MON-YY.	Fixed at seven bytes for each row in the table.
LONG	Variable-length character data.	Variable for each row in the table, up to 2^31 - 1 bytes, or two gigabytes, per row.
RAW (size)	Variable-length raw binary data. A maximum size must be specified.	Variable for each row in the table, up to 255 bytes per row.
LONG RAW	Variable-length raw binary data.	Variable for each row in the table, up to 2^31 - 1 bytes, or two gigabytes, per row.
ROWID	Binary data representing row addresses.	Fixed at six bytes for each row in the table.
MLSLABEL	Variable-length binary data representing operating system labels.	Variable for each row in the table, ranging from two to five bytes per row.

Table 6 - 2. Summary of Oracle Datatype Information