9. Datatype Packages

Contents:
DBMS_ROWID: Working with the ROWID Pseudo-Column (Oracle8 only)
UTL_RAW: Manipulating Raw Data

This chapter introduces you to several packages that let you work effectively with particular types of Oracle data:

DBMS_ROWID: New in Oracle8, a llows you to work with the two different ROWID formats: extended (new to Oracle8) and restricted (traditional Oracle7 ROWIDs).
UTL_RAW: Offers a set of functions allowing you to perform concatenation, substring, bit-wise logical analysis, byte translation, and length operations on RAW data.
UTL_REF: New in Oracle8 Release 8.1; provides a PL/SQL interface to select and modify objects (instances of an object type) in an object table without having to specify or know about the underlying database table.

9.1 DBMS_ROWID: Working with the ROWID Pseudo-Column (Oracle8 only)

The DBMS_ROWID package lets you work with ROWIDs from within PL/SQL programs and SQL statements. You can use the programs in this package to both create and manipulate ROWIDs. You can determine the data block number, the object number, and other components of the ROWID without having to write code to translate the base-64 character external ROWID.

NOTE: With Oracle8, there are two types of ROWIDs: extended and restricted. Restricted ROWIDs are the ROWIDs available with Oracle Version 7 and earlier. Extended ROWIDs are used only in Oracle8.

9.1.1 Getting Started with DBMS_ROWID

The DBMS_ROWID package is created when the Oracle8 database is installed. The dbmsutil.sql script (found in the built-in packages source code directory, as described in Chapter 1, Introduction ), contains the source code for this package's specification. This script is called by catproc.sql , which is normally run immediately after database creation. The script creates the public synonym DBMS_ROWID for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of this package.

All of the programs in DBMS_ROWID run as invoker, meaning that the privileges of the programs are taken from the session running the DBMS_ROWID programs and not from the owner of that package.

9.1.1.1 DBMS_ROWID programs

Table 9.1 lists the programs defined for the DBMS_ROWID package. For a dicussion of some of the concepts underlying these program operations, see the next section, " Section 9.1.2, "ROWID Concepts" ."

Table 9.1: DBMS_ROWID Programs
Name	Description	Use in SQL
ROWID_BLOCK_NUMBER	Returns the database block number of the ROWID.	Yes
ROWID_CREATE	Creates a ROWID (either restricted or extended as you request) based on the individual ROWID component values you specify. Use this function for test purposes only.	Yes
ROWID_INFO	Returns information about the specified ROWID. This procedure essentially "parses" the ROWID.	Yes
ROWID_OBJECT	Returns the data object number for an extended ROWID. Returns 0 if the specified ROWID is restricted.	Yes
ROWID_RELATIVE_FNO	Returns the relative file number (relative to the tablespace) of the ROWID.	Yes
ROWID_ROW_NUMBER	Returns the row number of the ROWID.	Yes
ROWID_TO_ABSOLUTE_FNO	Returns the absolute file number (for a row in a given schema and table) from the ROWID.	Yes
ROWID_TO_EXTENDED	Converts a restricted ROWID to an extended ROWID.	Yes
ROWID_TO_RESTRICTED	Converts an extended ROWID to a restricted ROWID.	Yes
ROWID_TYPE	Returns 0 if the ROWID is restricted, 1 if the ROWID is extended.	Yes
ROWID_VERIFY	Returns 0 if the restricted ROWID provided can be converted to an extended format, and 1 otherwise.	Yes

9.1.1.2 DBMS_ROWID exceptions

Table 9.2 lists the named exceptions defined in the DBMS_ROWID package; they are associated with the error number listed beside the name.

Table 9.2: DBMS_ROWID Exceptions
Name	Number	Description
ROWID_INVALID	-1410	The value entered is larger than the maximum width defined for the column.
ROWID_BAD_BLOCK	-28516	The block number specified in the ROWID is invalid.

9.1.1.3 DBMS_ROWID nonprogram elements

Table 9.3 lists the named constants defined by the DBMS_ROWID package for use with its programs.

Table 9.3: DBMS_ROWID Constants
Name/Type	Description
ROWID_TYPE_RESTRICTED	A ROWID type: integer constant assigned the value of 0.
ROWID_TYPE_EXTENDED	A ROWID type: integer constant assigned the value of 1.
ROWID_IS_VALID	A ROWID verification result: integer constant assigned the value of 0.
ROWID_IS_INVALID	A ROWID verification result: integer constant assigned the value of 1.
ROWID_OBJECT_UNDEFINED	An object type indicating that the object number is not defined (for restricted ROWIDs): integer constant assigned the value of 0.
ROWID_CONVERT_INTERNAL	A ROWID conversion type: integer constant assigned the value of 0.
ROWID_CONVERT_EXTERNAL	A ROWID conversion type: integer constant assigned the value of 1.

9.1.2 ROWID Concepts

This section offers a quick overview of the Oracle ROWID. You can get much more extensive information on ROWIDs from the Oracle documentation.

In the Oracle RDBMS, ROWID is a pseudocolumn that is a part of every table you create. The ROWID is an internally generated and maintained binary value that identifies a row of data in your table. It is called a pseudocolumn because a SQL statement includes it in places where you would normally use a column. However, it is not a column that you create for the table. Instead, the RDBMS generates the ROWID for each row as it is inserted into the database. The information in the ROWID provides the exact physical location of the row in the database. You cannot change the value of a ROWID.

You can use the ROWID datatype to store ROWIDs from the database in your PL/SQL program. You can SELECT or FETCH the ROWID for a row into a ROWID variable. To manipulate ROWIDs in Oracle8, you will want to use the DBMS_ROWID package described in this chapter. In Oracle7, you will use the ROWIDTOCHAR function to convert the ROWID to a fixed-length string and then perform operations against that string.

In Oracle7, the format of the fixed-length ROWID is,

BBBBBBB.RRRR.FFFFF

where components of this format have the following meanings:

BBBBBBB: The block in the database file.
RRRR: The row in the block (where the first row is zero, not one).
FFFFF: The database file.

All these numbers are hexadecimal; the database file is a number that you would then use to look up the actual name of the database file through the data dictionary.

In Oracle8, ROWIDs have been "extended" to support partitioned tables and indexes. The new, extended ROWIDs include a data object number, identifying the d atabase segment. Any schema object found in the same segment, such as a cluster of tables, will have the same object number. In Oracle8, then, a ROWID contains the following information:

The data object number
The data file (where the first file is 1)
The data block within the data file
The row in the data block (where the first row is 0)

Usually (and always in Oracle7), a ROWID will uniquely identify a row of data. Within Oracle8, however, rows in different tables stored in the same cluster can have the same ROWID value.

9.1.3 The DBMS_ROWID Interface

The following sections describe the procedures and functions available through DBMS_ROWID.

9.1.3.1 The DBMS_ROWID. ROWID_BLOCK_NUMBER function

The ROWID_BLOCK_NUMBER function returns the block number of a ROWID. Its header is,

FUNCTION DBMS_ROWID.ROWID_BLOCK_NUMBER (row_id IN ROWID) RETURN NUMBER;

where the ROWID parameter is the ROWID from which the value is extracted.

9.1.3.1.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_BLOCK_NUMBER:

PRAGMA RESTRICT_REFERENCES (ROWID_BLOCK_NUMBER, WNDS, RNDS, WNPS, RNPS);

9.1.3.2 The DBMS_ROWID. CREATE_ROWID function

The CREATE_ROWID function creates and returns a ROWID (either restricted or extended, as you request) based on the individual ROWID component values you specify. Use this function for test purposes only. Here is its header:

FUNCTION DBMS_ROWID.ROWID_CREATE     (rowid_type IN NUMBER     ,object_number IN NUMBER     ,relative_fno IN NUMBER     ,block_number IN NUMBER     ,row_number IN NUMBER) RETURN ROWID;

Parameters are summarized in the following table.

Parameter	Description
rowid_type	The type of ROWID to be created. Specify either of the named constants ROWID_TYPE_RESTRICTED or ROWID_TYPE_EXTENDED.
object_number	The data object number for the ROWID. For a restricted ROWID (Oracle7), use the ROWID_OBJECT_UNDEFINED constant.
relative_fno	The relative file number for the ROWID.
block_number	The block number for the ROWID.
row_number	The row number for the ROWID.

9.1.3.2.1 Restrictions

The DBMS_ROWID package supplies the following pragma for CREATE_ROWID:

PRAGMA RESTRICT_REFERENCES (CREATE_ROWID, WNDS, RNDS, WNPS, RNPS);

9.1.3.2.2 Example

Here is an example of a call to the ROWID_CREATE procedure:

   my_rowid ROWID; BEGIN     my_rowid := DBMS_ROWID.ROWID_CREATE       (DBMS_ROWID.ROWID_TYPE_EXTENDED, 100, 15, 103, 345); END; /

9.1.3.3 The DBMS_ROWID. ROWID_INFO procedure

The ROWID_INFO procedure parses out and returns the individual components of the specified ROWID. Here is its header:

PROCEDURE DBMS_ROWID.ROWID_INFO     (rowid_in IN ROWID     ,rowid_type OUT NUMBER     ,object_number OUT NUMBER     ,relative_fno OUT NUMBER     ,block_number OUT NUMBER     ,row_number OUT NUMBER);

Parameters are summarized in the following table.

Parameter	Description
rowid_in	The ROWID value to be parsed into components.
rowid_type	The type of ROWID. The value returned will be either of the named constants ROWID_TYPE_RESTRICTED or ROWID_TYPE_EXTENDED.
object_number	The data object number for the ROWID. For a restricted ROWID (Oracle7), the ROWID_OBJECT_UNDEFINED constant is returned.
relative_fno	The relative file number for the ROWID.
block_number	The block number for the ROWID in the file.
row_number	The row number for the ROWID.

9.1.3.3.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_INFO:

PRAGMA RESTRICT_REFERENCES (ROWID_INFO, WNDS, RNDS, WNPS, RNPS);

9.1.3.4 The DBMS_ROWID. ROWID_OBJECT function

The ROWID_OBJECT function returns the object number of a ROWID. The ROWID_OBJECT_UNDEFINED constant is returned for restricted ROWIDs. Its header is,

FUNCTION DBMS_ROWID.ROWID_OBJECT (row_id IN ROWID) RETURN NUMBER;

where the row_id parameter is the ROWID from which the value is extracted.

9.1.3.4.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_OBJECT:

PRAGMA RESTRICT_REFERENCES (ROWID_OBJECT, WNDS, RNDS, WNPS, RNPS);

9.1.3.4.2 Example

You will want to obtain a ROWID's object number only if the ROWID type is extended. You would write code like this to perform that check:

IF DBMS_ROWID.ROWID_TYPE (v_rowid) = DBMS_ROWID.ROWID_TYPE_EXTENDED  THEN    v_objnum := DBMS_ROWID.ROWID_OBJECT (v_rowid); END IF;

9.1.3.5 The DBMS_ROWID. ROWID_RELATIVE_FNO function

The ROWID_RELATIVE_FNO function returns the relative file number of a ROWID. Its header is,

FUNCTION DBMS_ROWID.ROWID_RELATIVE_FNO     (row_id IN ROWID) RETURN NUMBER;

where the row_id parameter is the ROWID from which the value is extracted.

9.1.3.5.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_RELATIVE_FNO:

PRAGMA RESTRICT_REFERENCES (ROWID_RELATIVE_FNO, WNDS, RNDS, WNPS, RNPS);

9.1.3.6 The DBMS_ROWID. ROWID_ROW_NUMBER function

The ROWID_ROW_NUMBER function returns the row number of a ROWID. Its header is,

FUNCTION DBMS_ROWID.ROWID_ROW_NUMBER (row_id IN ROWID) RETURN NUMBER;

where the row_id parameter is the ROWID from which the value is extracted.

9.1.3.6.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_ROW_NUMBER:

PRAGMA RESTRICT_REFERENCES (ROWID_ROW_NUMBER, WNDS, RNDS, WNPS, RNPS);

9.1.3.7 The DBMS_ROWID. ROWID_TO_ABSOLUTE_FNO function

The ROWID_TO_ABSOLUTE_FNO function returns the absolute file number of a ROWID. Here is its header:

FUNCTION DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO     (row_id IN ROWID     ,schema_name IN VARCHAR2     ,object_name IN VARCHAR2) RETURN NUMBER;

Parameters are summarized in the following table.

Parameter	Description
row_id	The ROWID from which the value is extracted.
schema_name	The name of the schema contains the table.
object_name	The table name.

9.1.3.7.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_RELATIVE_FNO:

PRAGMA RESTRICT_REFERENCES (ROWID_RELATIVE_FNO, WNDS, WNPS, RNPS);

9.1.3.8 The DBMS_ROWID. ROWID_TO_EXTENDED function

The ROWID_TO_EXTENDED function converts a restricted ROWID, addressing a specific row in a table, to an extended ROWID. Here is its header:

FUNCTION DBMS_ROWID.ROWID_TO_EXTENDED     (old_rowid IN ROWID     ,schema_name IN VARCHAR2     ,object_name IN VARCHAR2     ,conversion_type IN INTEGER) RETURN ROWID;

Parameters are summarized in the following table.

Parameter	Description
old_rowid	The ROWID to be converted.
schema_name	The name of the schema that contains the table.
object_name	The table name.
conversion_type	The type of conversion. Pass either the ROWID_CONVERT_INTERNAL constant (if old_ROWID was stored in a column of type ROWID) or ROWID_CONVERT_EXTERNAL (if old_ROWID was stored as a character string).

9.1.3.8.1 Restrictions

Note the following restrictions on calling ROWID_TO_EXTENDED:

The ROWID_TO_EXTENDED function returns a ROWID in the extended character format. If you provide a NULL ROWID, the function will return NULL. If a zero-valued ROWID is supplied (00000000.0000.0000), a zero-valued restricted ROWID is returned.
The DBMS_ROWID package supplies the following pragma for ROWID_TO_EXTENDED:
```
PRAGMA RESTRICT_REFERENCES (ROWID_TO_EXTENDED, WNDS, WNPS, RNPS);
```
If the schema and object names are provided as IN parameters, this function first verifies that you have SELECT privilege on the table named. It then converts the restricted ROWID provided to an extended ROWID, using the data object number of the specified table. Even if ROWID_TO_EXTENDED returns a value, however, that does not guarantee that the converted ROWID actually references a valid row in the table, either at the time that the function is called, or when the extended ROWID is actually used. It is only performing a conversion.
If the schema and object name are not provided (i.e., are passed as NULL), then this function attempts to fetch the page specified by the restricted ROWID provided. It treats the file number stored in this ROWID as the absolute file number. This may cause problems if the file has been dropped, and its number has been reused prior to the data migration. If the fetched page belongs to a valid table, the data object number of this table is used in converting to an extended ROWID value.

This approach is very inefficient. Oracle recommends doing this only as a last resort, when the target table is not known. Note that the user must still be aware of the correct table name when using the converted ROWID.
If an extended ROWID value is supplied, that ROWID's data object is verified against the data object number calculated from the table name argument. If the two numbers do not match, DBMS_ROWID raises the INVALID_ROWID exception. If there is a match, then the input ROWID is returned.

9.1.3.8.2 Example

Suppose that I have a table in my APP schema called ROWID_conversion. This table contains two columns: ROWID_value and table_name. The ROWID_value column contains the restricted-format ROWIDs for rows in the table specifed by the table_name column. I can then convert all of my restricted ROWID values to extended ones with the following UPDATE statement:

UPDATE app.rowid_conversion    SET rowid_value =         DBMS_ROWID.ROWID_TO_EXTENDED            (rowid_value,             'APP',             table_name,            DBMS_ROWID.ROWID_CONVERT_INTERNAL)



;.

9.1.3.9 The DBMS_ROWID. ROWID_TO_RESTRICTED function

The ROWID_TO_RESTRICTED function converts an extended ROWID to a restricted ROWID. Here is its header:

FUNCTION DBMS_ROWID.ROWID_TO_RESTRICTED     (old_rowid IN ROWID     ,conversion_type IN INTEGER) RETURN ROWID;

Parameters are summarized in the following table.

Parameter	Description
old_rowid	The ROWID to be converted.
conversion_type	The format of the returned ROWID. Pass either the ROWID_CONVERT_INTERNAL constant (if the returned ROWID is to be stored in a column of type ROWID) or the ROWID_CONVERT_EXTERNAL constant (if the returned ROWID is to be stored as a character string).

9.1.3.9.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_TO_RESTRICTED:

PRAGMA RESTRICT_REFERENCES     (ROWID_TO_RESTRICTED, WNDS, RNDS, WNPS, RNPS);

9.1.3.10 The DBMS_ROWID. ROWID_TYPE function

The ROWID_TYPE function returns the type of a ROWID via one of the following package constants: ROWID_TYPE_RESTRICTED or ROWID_TYPE_EXTENDED. Its header is,

FUNCTION DBMS_ROWID.ROWID_TYPE (row_id IN ROWID)    RETURN NUMBER;

where the row_id parameter is the ROWID from which the value is extracted.

9.1.3.10.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_TYPE:

PRAGMA RESTRICT_REFERENCES (ROWID_TYPE, WNDS, RNDS, WNPS, RNPS);

9.1.3.10.2 Example

In the following query, I determine the ROWID types in the emp table:

SELECT DISTINCT (DBMS_ROWID.ROWID_TYPE(ROWID))   FROM emp;

This returns the value of 1, that is: DBMS_ROWID.ROWID_TYPE_EXTENDED. The emp table was created under Oracle8 and therefore uses the Oracle8 type of ROWID.

9.1.3.11 The DBMS_ROWID. ROWID_VERIFY function

The ROWID_VERIFY function verifies a ROWID. It returns either the ROWID_VALID or ROWID_INVALID constants. Here is its header:

FUNCTION DBMS_ROWID.ROWID_VERIFY     (rowid_in IN ROWID     ,schema_name IN VARCHAR2     ,object_name IN VARCHAR2     ,conversion_type IN INTEGER) RETURN NUMBER;

Parameters are summarized in the following table.

Parameter	Description
rowid_in	The ROWID to be verified.
schema_name	The name of the schema containing the table.
object_name	The name of the table.
conversion_type	The type of conversion to be used for the verification. You should pass either the ROWID_CONVERT_INTERNAL constant (if ROWID_in is stored in a column of type ROWID) or the ROWID_CONVERT_EXTERNAL constant (if ROWID_in is stored as a character string).

9.1.3.11.1 Restrictions

The DBMS_ROWID package supplies the following pragma for ROWID_VERIFY:

PRAGMA RESTRICT_REFERENCES (ROWID_VERIFY, WNDS, WNPS, RNPS);

9.1.3.11.2 Example

Interestingly, you can call this numeric function as a kind of Boolean function within SQL. Suppose that I want to find all the invalid ROWIDs prior to converting them from restricted to extended. I could write the following query (using the same tables used in the example for the ROWID_TO_EXTENDED function):

SELECT ROWID, rowid_value    FROM app.rowid_conversion   WHERE DBMS_ROWID.ROWID_VERIFY (rowid_value, NULL, NULL, 0) = 1;