Oracle9i Supplied PL/SQL Packages and Types Reference Release 2 (9.2) Part Number A96612-01 |
|
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:
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';
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:
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); ...
This package runs with the privileges of calling user, rather than the package owner ('sys').
The types are as follows:
For example:
rowid_type_restricted constant integer := 0; rowid_type_extended constant integer := 1;
Result | Description |
---|---|
VALID |
Valid |
INVALID |
Invalid |
For example:
rowid_is_valid constant integer := 0; rowid_is_invalid constant integer := 1;
Result | Description |
---|---|
UNDEFINED |
Object Number not defined (for restricted |
For example:
rowid_object_undefined constant integer := 0;
Result | Description |
---|---|
INTERNAL |
Convert to/from column of |
EXTERNAL |
Convert to/from string format |
For example:
rowid_convert_internal constant integer := 0; rowid_convert_external constant integer := 1;
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);
|
Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. |
|