The DBMS_ROWID package lets you create ROWIDs and obtain information about ROWIDs from PL/SQL programs and SQL statements. You can find the data block number, the object number, and other ROWID components without writing code to interpret the base-64 character external ROWID. DBMS_ROWID is intended for upgrading from Oracle database version 7 to Oracle database version 8.X.
Note:
DBMS_ROWID is not to be used with universal ROWIDs (UROWIDs).This chapter contains the following topics:
Security Model
Types
Exceptions
Operational Notes
Examples
This package runs with the privileges of calling user, rather than the package owner SYS.
The types are as follows:
RESTRICTED—restricted ROWID
EXTENDED—extended ROWID
For example:
rowid_type_restricted constant integer := 0; rowid_type_extended constant integer := 1;
Note:
ExtendedROWIDs are only used in Oracle database version 8.Xi and higher.For example:
rowid_is_valid constant integer := 0; rowid_is_invalid constant integer := 1;
| Result | Description |
|---|---|
|
|
Object Number not defined (for restricted |
For example:
rowid_object_undefined constant integer := 0;
| Result | Description |
|---|---|
|
|
Convert to/from column of |
|
|
Convert to/from string format |
For example:
rowid_convert_internal constant integer := 0; rowid_convert_external constant integer := 1;
| Exception | Description |
|---|---|
|
|
Invalid rowid format |
|
|
Block is beyond end of file |
For example:
ROWID_INVALID exception; pragma exception_init(ROWID_INVALID, -1410); ROWID_BAD_BLOCK exception; pragma exception_init(ROWID_BAD_BLOCK, -28516);
Some of the functions in this package take a single parameter, such as a ROWID. This can be a character or a PL/SLQ ROWID, either restricted or extended, as required.
You can call the DBMS_ROWID functions and procedures from PL/SQL code, and you can also use the functions in SQL statements.
Note:
ROWID_INFO is a procedure. It can only be used in PL/SQL code.You can use functions from the DBMS_ROWID package just like built-in SQL functions; in other words, you can use them wherever you can use an expression. In this example, the ROWID_BLOCK_NUMBER function is used to return just the block number of a single row in the EMP table:
SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) FROM emp WHERE ename = 'KING';
If Oracle returns the error "ORA:452, 0, 'Subprogram '%s' violates its associated pragma' for pragma restrict_references, it could mean the violation is due to:
A problem with the current procedure or function
Calling a procedure or function without a pragma or due to calling one with a less restrictive pragma
Calling a package procedure or function that touches the initialization code in a package or that sets the default values
This example returns the ROWID for a row in the EMP table, extracts the data object number from the ROWID, using the ROWID_OBJECT function in the DBMS_ROWID package, then displays the object number:
DECLARE
object_no INTEGER;
row_id ROWID;
...
BEGIN
SELECT ROWID INTO row_id FROM emp
WHERE empno = 7499;
object_no := DBMS_ROWID.ROWID_OBJECT(row_id);
DBMS_OUTPUT.PUT_LINE('The obj. # is '|| object_no);
...
Table 90-5 DBMS_ROWID Package Subprograms
| Subprogram | Description |
|---|---|
|
Returns the block number of a |
|
|
Creates a |
|
|
Returns the type and components of a |
|
|
Returns the object number of the extended |
|
|
Returns the file number of a |
|
|
Returns the row number |
|
|
Returns the absolute file number associated with the |
|
|
Converts a |
|
|
Converts an extended |
|
|
Returns the |
|
|
Checks if a |
This function returns the database block number for the input ROWID.
DBMS_ROWID.ROWID_BLOCK_NUMBER ( row_id IN ROWID, ts_type_in IN VARCHAR2 DEFAULT 'SMALLFILE') RETURN NUMBER;
pragma RESTRICT_REFERENCES(rowid_block_number,WNDS,RNDS,WNPS,RNPS);
Table 90-6 ROWID_BLOCK_NUMBER Function Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
The type of the tablespace (bigfile/smallfile) to which the row belongs. |
The example SQL statement selects the block number from a ROWID and inserts it into another table:
INSERT INTO T2 (SELECT dbms_rowid.rowid_block_number(ROWID, 'BIGFILE') FROM some_table WHERE key_value = 42);
This function lets you create a ROWID, given the component parts as parameters.
This is useful for testing ROWID operations, because only the Oracle Server can create a valid ROWID that points to data in a database.
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;
pragma RESTRICT_REFERENCES(rowid_create,WNDS,RNDS,WNPS,RNPS);
Table 90-7 ROWID_CREATE Function Parameters
| Parameter | Description |
|---|---|
|
|
Type (restricted or extended). Set the If you specify |
|
|
Data object number ( |
|
|
Relative file number. |
|
|
Block number in this file. |
|
|
Returns row number in this block. |
Create a dummy extended ROWID:
my_rowid := DBMS_ROWID.ROWID_CREATE(1, 9999, 12, 1000, 13);
Find out what the rowid_object function returns:
obj_number := DBMS_ROWID.ROWID_OBJECT(my_rowid);
The variable obj_number now contains 9999.
This procedure returns information about a ROWID, including its type (restricted or extended), and the components of the ROWID. This is a procedure, and it cannot be used in a SQL statement.
DBMS_ROWID.ROWID_INFO ( rowid_in IN ROWID, ts_type_in IN VARCHAR2 DEFAULT 'SMALLFILE', rowid_type OUT NUMBER, object_number OUT NUMBER, relative_fno OUT NUMBER, block_number OUT NUMBER, row_number OUT NUMBER);
pragma RESTRICT_REFERENCES(rowid_info,WNDS,RNDS,WNPS,RNPS);
Table 90-8 ROWID_INFO Procedure Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
The type of the tablespace (bigfile/smallfile) to which the row belongs. |
|
|
Returns type (restricted/extended). |
|
|
Returns data object number ( |
|
|
Returns relative file number. |
|
|
Returns block number in this file. |
|
|
Returns row number in this block. |
See Also:
"ROWID_TYPE Function"This example reads back the values for the ROWID that you created in the ROWID_CREATE:
DBMS_ROWID.ROWID_INFO(my_rowid, 'BIGFILE', rid_type, obj_num, file_num, block_num, row_num);
DBMS_OUTPUT.PUT_LINE('The type is ' || rid_type);
DBMS_OUTPUT.PUT_LINE('Data object number is ' || obj_num);
-- and so on...
This function returns the data object number for an extended ROWID. The function returns zero if the input ROWID is a restricted ROWID.
DBMS_ROWID.ROWID_OBJECT ( rowid_id IN ROWID) RETURN NUMBER;
pragma RESTRICT_REFERENCES(rowid_object,WNDS,RNDS,WNPS,RNPS);
Note:
TheROWID_OBJECT_UNDEFINED constant is returned for restricted ROWIDs.SELECT dbms_rowid.rowid_object(ROWID) FROM emp WHERE empno = 7499;
This function returns the relative file number of the ROWID specified as the IN parameter. (The file number is relative to the tablespace.)
DBMS_ROWID.ROWID_RELATIVE_FNO ( rowid_id IN ROWID, ts_type_in IN VARCHAR2 DEFAULT 'SMALLFILE') RETURN NUMBER;
pragma RESTRICT_REFERENCES(rowid_relative_fno,WNDS,RNDS,WNPS,RNPS);
Table 90-10 ROWID_RELATIVE_FNO Function Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
The type of the tablespace (bigfile/smallfile) to which the row belongs. |
The example PL/SQL code fragment returns the relative file number:
DECLARE
file_number INTEGER;
rowid_val ROWID;
BEGIN
SELECT ROWID INTO rowid_val
FROM dept
WHERE loc = 'Boston';
file_number :=
dbms_rowid.rowid_relative_fno(rowid_val, 'SMALLFILE');
...
This function extracts the row number from the ROWID IN parameter.
DBMS_ROWID.ROWID_ROW_NUMBER ( row_id IN ROWID) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(rowid_row_number,WNDS,RNDS,WNPS,RNPS);
Table 90-11 ROWID_ROW_NUMBER Function Parameters
| Parameter | Description |
|---|---|
|
|
|
Select a row number:
SELECT dbms_rowid.rowid_row_number(ROWID) FROM emp WHERE ename = 'ALLEN';
This function extracts the absolute file number from a ROWID, where the file number is absolute for a row in a given schema and table. The schema name and the name of the schema object (such as a table name) are provided as IN parameters for this function.
DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO ( row_id IN ROWID, schema_name IN VARCHAR2, object_name IN VARCHAR2) RETURN NUMBER;
pragma RESTRICT_REFERENCES(rowid_to_absolute_fno,WNDS,WNPS,RNPS);
Table 90-12 ROWID_TO_ABSOLUTE_FNO Function Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
Name of the schema which contains the table. |
|
|
Table name. |
DECLARE
abs_fno INTEGER;
rowid_val CHAR(18);
object_name VARCHAR2(20) := 'EMP';
BEGIN
SELECT ROWID INTO rowid_val
FROM emp
WHERE empno = 9999;
abs_fno := dbms_rowid.rowid_to_absolute_fno(
rowid_val, 'SCOTT', object_name);
Note:
For partitioned objects, the name must be a table name, not a partition or a sub/partition name.This function translates a restricted ROWID that addresses a row in a schema and table that you specify to the extended ROWID format. Later, it may be removed from this package into a different place.
DBMS_ROWID.ROWID_TO_EXTENDED ( old_rowid IN ROWID, schema_name IN VARCHAR2, object_name IN VARCHAR2, conversion_type IN INTEGER) RETURN ROWID;
pragma RESTRICT_REFERENCES(rowid_to_extended,WNDS,WNPS,RNPS);
Table 90-13 ROWID_TO_EXTENDED Function Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
Name of the schema which contains the table (optional). |
|
|
Table name (optional). |
|
|
The following constants are defined: rowid_convert_internal (:=0) rowid_convert_external (:=1) |
ROWID_TO_EXTENDED returns the ROWID in the extended character format. If the input ROWID is NULL, then the function returns NULL. If a zero-valued ROWID is supplied (00000000.0000.0000), then a zero-valued restricted ROWID is returned.
Assume that there is a table called RIDS in the schema SCOTT, and that the table contains a column ROWID_COL that holds ROWIDs (restricted), and a column TABLE_COL that point to other tables in the SCOTT schema. You can convert the ROWIDs to extended format with the statement:
UPDATE SCOTT.RIDS
SET rowid_col =
dbms_rowid.rowid_to_extended (
rowid_col, 'SCOTT", TABLE_COL, 0);
If the schema and object names are provided as IN parameters, then this function verifies SELECT authority on the table named, and converts the restricted ROWID provided to an extended ROWID, using the data object number of the table. That ROWID_TO_EXTENDED returns a value, however, 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.
If the schema and object name are not provided (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 can cause problems if the file has been dropped, and its number has been reused prior to the migration. If the fetched page belongs to a valid table, then the data object number of this table is used in converting to an extended ROWID value. This is very inefficient, and Oracle recommends doing this only as a last resort, when the target table is not known. The user must still know the correct table name at the time of using the converted value.
If an extended ROWID value is supplied, the data object number in the input extended ROWID is verified against the data object number computed from the table name parameter. If the two numbers do not match, the INVALID_ROWID exception is raised. If they do match, the input ROWID is returned.
See Also:
The ROWID_VERIFY Function has a method to determine if a givenROWID can be converted to the extended format.This function converts an extended ROWID into restricted ROWID format.
DBMS_ROWID.ROWID_TO_RESTRICTED ( old_rowid IN ROWID, conversion_type IN INTEGER) RETURN ROWID;
pragma RESTRICT_REFERENCES(rowid_to_restricted,WNDS,RNDS,WNPS,RNPS);
Table 90-14 ROWID_TO_RESTRICTED Function Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
The following constants are defined: rowid_convert_internal (:=0) rowid_convert_external (:=1) |
This function returns 0 if the ROWID is a restricted ROWID, and 1 if it is extended.
DBMS_ROWID.ROWID_TYPE ( rowid_id IN ROWID) RETURN NUMBER;
pragma RESTRICT_REFERENCES(rowid_type,WNDS,RNDS,WNPS,RNPS);
IF DBMS_ROWID.ROWID_TYPE(my_rowid) = 1 THEN my_obj_num := DBMS_ROWID.ROWID_OBJECT(my_rowid);
This function verifies the ROWID. It returns 0 if the input restricted ROWID can be converted to extended format, given the input schema name and table name, and it returns 1 if the conversion is not possible.
Note:
You can use this function in aWHERE clause of a SQL statement, as shown in the example.DBMS_ROWID.ROWID_VERIFY ( rowid_in IN ROWID, schema_name IN VARCHAR2, object_name IN VARCHAR2, conversion_type IN INTEGER RETURN NUMBER;
pragma RESTRICT_REFERENCES(rowid_verify,WNDS,WNPS,RNPS);
Table 90-16 ROWID_VERIFY Function Parameters
| Parameter | Description |
|---|---|
|
|
|
|
|
Name of the schema which contains the table. |
|
|
Table name. |
|
|
The following constants are defined: rowid_convert_internal (:=0) rowid_convert_external (:=1) |
Considering the schema in the example for the ROWID_TO_EXTENDED function, you can use the following statement to find bad ROWIDs prior to conversion. This enables you to fix them beforehand.
SELECT ROWID, rowid_col FROM SCOTT.RIDS WHERE dbms_rowid.rowid_verify(rowid_col, NULL, NULL, 0) =1;