#

| Chapter 9 |

# 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, allows 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 database 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;

8.3 DBMS_LOB Interface |  | 9.2 UTL_RAW: Manipulating Raw Data

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.