62
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.

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.

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

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;

ROWID Verification Results

Result	Description
VALID	Valid ROWID
INVALID	Invalid ROWID

For example:

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

Object Types

Result	Description
UNDEFINED	Object Number not defined (for restricted ROWIDs)

For example:

rowid_object_undefined constant integer := 0;

ROWID Conversion Types

Result	Description
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

Exception	Description
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);