Internally, every database table has aROWID pseudocolumn, which stores binary values calledrowids. Each rowid represents the storage address of a row. Aphysical rowid identifies a row in an ordinary table. Alogical rowid identifies a row in an index-organized table. TheROWID datatype can store only physical rowids. However, theUROWID (universal rowid) datatype can store physical, logical, or foreign
(non-Oracle) rowids.
Suggestion: Use theROWID datatype only for backward compatibility with old applications. For new applications, use theUROWID datatype.
When you select or fetch a rowid into aROWID variable, you can use the built-in functionROWIDTOCHAR, which converts the binary value into an 18-byte character string. Conversely, the functionCHARTOROWID converts aROWID character string into a rowid. If the conversion fails because the character string does not represent a valid rowid, PL/SQL raises the predefined exceptionSYS_INVALID_ROWID. This also applies to implicit conversions.
To convert betweenUROWID variables and character strings, use regular assignment statements without any function call. The values are implicitly converted between UROWID and character types.
Physical Rowids Physical rowids provide fast access to particular rows. As long as the row exists, its physical rowid does not change. Efficient and stable, physical rowids are useful for selecting a set of rows, operating on the whole set, and then updating a subset. For example, you can compare aUROWID variable with theROWID
pseudocolumn in theWHERE clause of anUPDATE orDELETE statement to identify the latest row fetched from a cursor. See"Fetching Across Commits" on page 6-34.
A physical rowid can have either of two formats. The 10-byteextended rowid format supports tablespace-relative block addresses and can identify rows in partitioned and non-partitioned tables. The 6-byterestricted rowid format is provided for backward compatibility.
Extended rowids use a base-64 encoding of the physical address for each row selected. For example, in SQL*Plus (which implicitly converts rowids into character strings), the query
Overview of Predefined PL/SQL Datatypes
might return the following row: ROWID ENAME --- --- AAAAqcAABAAADFNAAH SCOTT
The format,OOOOOOFFFBBBBBBRRR, has four parts:
■ OOOOOO: The data object number (AAAAqc in the example above) identifies the database segment. Schema objects in the same segment, such as a cluster of tables, have the same data object number.
■ FFF: The file number (AABin the example) identifies the data file that contains the row. File numbers are unique within a database.
■ BBBBBB: The block number (AAADFNin the example) identifies the data block that contains the row. Because block numbers are relative to their data file, not their tablespace, two rows in the same tablespace but in different data files can have the same block number.
■ RRR: The row number (AAH in the example) identifies the row in the block.
Logical Rowids Logical rowids provide the fastest access to particular rows. Oracle uses them to construct secondary indexes on index-organized tables. Having no permanent physical address, a logical rowid can move across data blocks when new rows are inserted. However, if the physical location of a row changes, its logical rowid remains valid.
A logical rowid can include aguess, which identifies the block location of a row at the time the guess is made. Instead of doing a full key search, Oracle uses the guess to search the block directly. However, as new rows are inserted, guesses can become stale and slow down access to rows. To obtain fresh guesses, you can rebuild the secondary index.
You can use theROWID pseudocolumn to select logical rowids (which are opaque values) from an index-organized table. Also, you can insert logical rowids into a column of typeUROWID, which has a maximum size of 4000 bytes.
TheANALYZE statement helps you track the staleness of guesses. This is useful for applications that store rowids with guesses in aUROWID column, then use the rowids to fetch rows.
Note: To manipulate rowids, you can use the supplied packageDBMS_ROWID. For more information, seePL/SQL Packages and Types Reference.
VARCHAR2
You use theVARCHAR2 datatype to store variable-length character data. How the data is represented internally depends on the database character set. TheVARCHAR2 datatype takes a required parameter that specifies a maximum size up to 32767 bytes. The syntax follows:
VARCHAR2(maximum_size [CHAR | BYTE])
You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.
SmallVARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For aVARCHAR2 that is2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For aVARCHAR2 variable that is shorter than 2000 bytes,
Overview of Predefined PL/SQL Datatypes
PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to aVARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes.
If you specify the maximum size in bytes rather than characters, aVARCHAR2(n) variable might be too small to holdn multibyte characters. To avoid this possibility, use the notationVARCHAR2(n CHAR)so that the variable can holdn characters in the database character set, even if some of those characters contain multiple bytes. When you specify the length in characters, the upper limit is still 32767 bytes. So for
double-byte and multibyte character sets, you can only specify 1/2 or 1/3 as many characters as with a single-byte character set.
Although PL/SQL character variables can be relatively long, you cannot insert VARCHAR2 values longer than 4000 bytes into aVARCHAR2 database column. You can insert anyVARCHAR2(n) value into aLONG database column because the maximum width of aLONG column is 2**31 bytes. However, you cannot retrieve a value longer than 32767 bytes from aLONG column into aVARCHAR2(n) variable. When you do not use theCHAR orBYTE qualifiers, the default is determined by the setting of theNLS_LENGTH_SEMANTICS initialization parameter. When a PL/SQL procedure is compiled, the setting of this parameter is recorded, so that the same setting is used when the procedure is recompiled after being invalidated.
VARCHAR2 Subtypes TheVARCHAR2 subtypes below have the same range of values as their base type. For example,VARCHAR is just another name forVARCHAR2.
STRING VARCHAR
You can use these subtypes for compatibility with ANSI/ISO and IBM types.
Note: Currently,VARCHAR is synonymous withVARCHAR2. However, in future releases of PL/SQL, to accommodate emerging SQL standards,VARCHAR might become a separate datatype with different comparison semantics. It is a good idea to useVARCHAR2 rather thanVARCHAR.