Skip Headers

Oracle9i Supplied PL/SQL Packages and Types Reference
Release 2 (9.2)

Part Number A96612-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

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.


Note:

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


This chapter discusses the following topics:

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:

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:

For example:

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

Note:

Extended ROWIDs are only used in Oracle8i and higher.


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

Go to previous page Go to next page
Oracle
Copyright © 2000, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback