57
DBMS_ROWID

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.

Note: DBMS_ROWID is not to be used with universal ROWIDs (UROWIDs).

This chapter discusses the following topics:

• Usage Notes

• Requirements

• ROWID Types

• Exceptions

• Summary of DBMS_ROWID Subprograms

Usage Notes

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';

Troubleshooting Use of the RESTRICT_REFERENCES Pragma

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

PL/SQL Example

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);
  ...

Requirements

This package runs with the privileges of calling user, rather than the package owner ('sys').

ROWID Types

RESTRICTED	Restricted ROWID
EXTENDED	Extended ROWID

For example:

rowid_type_restricted constant integer := 0;
rowid_type_extended   constant integer := 1;

Note: Extended ROWIDs are only used in Oracle8i and above.

ROWID Verification Results

VALID	Valid ROWID
INVALID	Invalid ROWID

For example:

rowid_is_valid   constant integer := 0;
rowid_is_invalid constant integer := 1;

Object Types

UNDEFINED

Object Number not defined (for restricted ROWIDs)

For example:

rowid_object_undefined constant integer := 0;

ROWID Conversion Types

INTERNAL	Convert to/from column of ROWID type
EXTERNAL	Convert to/from string format

For example:

rowid_convert_internal constant integer := 0;
rowid_convert_external constant integer := 1;

Exceptions

ROWID_INVALID	Invalid rowid format
ROWID_BAD_BLOCK	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);

Summary of DBMS_ROWID Subprograms

Table 57-1 DBMS_ROWID Subprograms

Subprogram	Description
"ROWID_CREATE Function"	Creates a ROWID, for testing only.
"ROWID_INFO Procedure"	Returns the type and components of a ROWID.
"ROWID_TYPE Function"	Returns the ROWID type: 0 is restricted, 1 is extended.
"ROWID_OBJECT Function"	Returns the object number of the extended ROWID.
"ROWID_RELATIVE_FNO Function"	Returns the file number of a ROWID.
"ROWID_BLOCK_NUMBER Function"	Returns the block number of a ROWID.
"ROWID_ROW_NUMBER Function"	Returns the row number.
"ROWID_TO_ABSOLUTE_FNO Function"	Returns the absolute file number associated with the ROWID for a row in a specific table.
"ROWID_TO_EXTENDED Function"	Converts a ROWID from restricted format to extended.
"ROWID_TO_RESTRICTED Function"	Converts an extended ROWID to restricted format.
"ROWID_VERIFY Function"	Checks if a ROWID can be correctly extended by the ROWID_TO_EXTENDED function.