In this fourth part of a seven-part series on using and administering Oracle Database XE, you'll learn about ROWID datatypes, and then we'll start discussing how to create and maintain tables. This article is excerpted from chapter 30 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
ROWID Datatypes
ROWIDs are datatypes that store either physical or logical addresses of rows in an Oracle Database XE table. ROWIDs store physical addresses, and UROWIDs store both logical and physical addresses. For the vast majority of applications, using or viewing ROWIDs is not required to achieve the best performance. However, for specialized applications, you can retrieve a table row using a ROWID value with only one I/O operation. A ROWID is guaranteed to be unique across all tables in the database.
Every table in the database has a pseudo-column namedROWID; it is not part of the table’s structure nor does it take up any space. It merely shows you the physical address of the row’s block in the database, as in this query:
select rowid, customer_name from customer_comment;
Even though you can create additional physical columns in a table of type ROWID, there is no validation to ensure that the value you place in the column is the address of a valid row in the database.
ANSI-Supported Datatypes
To improve compatibility with database applications that use ANSI SQL datatypes, Oracle supports ANSI datatypes such as CHARACTER, NATIONAL CHARACTER, andDECIMALand stores them internally as compatible Oracle datatypes. Table 30-6 shows the ANSI SQL datatype and the equivalent Oracle datatype.
Table 30-6. ANSI to Oracle Datatype Equivalents
ANSI SQL Datatype
Oracle Datatype
CHARACTER(n),CHAR(n)
CHAR(n)
CHARACTER VARYING(n),CHAR VARYING(n)
VARCHAR2(n),NATIONAL CHARACTER(n), NATIONAL CHAR(n),NCHAR(n),NCHAR(n)
NATIONAL CHARACTER VARYING(n), NATIONAL CHAR VARYING(n),NCHAR VARYING (n)
