Table of Contents
This chapter briefly introduces the DBMS_ROWID package, and describes how to use the procedures and functions of the package.
DBMS_ROWID returns information about the ROWID obtained from executing a SQL query or PSM, or creates a ROWID. ROWID includes information related to segments, blocks, absolute files and row numbers.
This section describes the procedure provided by the DBMS_ROWID package.
Exports all information about the ROWID. Since this is a procedure, it cannot be used in a SQL statement.
Details about the ROWID_INFO procedure are as follows:
Prototype
PROCEDURE ROWID_INFO
(
rowid_in IN ROWID,
segment_number OUT NUMBER,
absolute_fno OUT NUMBER,
block_number OUT NUMBER,
row_number OUT NUMBER
);
Parameter
| Parameter | Description |
|---|---|
| rowid_in | ROWID. |
| segment_number | Segment number. |
| absolute_fno | Absolute file number. |
| block_number | Block number. |
| row_number | Row number. |
Example
DBMS_ROWID.ROWID_INFO(rowid_1, sgmt_no, fno, blk_no, row_no);
DBMS_OUTPUT.PUT_LINE('segment number : ' || sgmt_no);
DBMS_OUTPUT.PUT_LINE('absolute file number : ' || fno);
DBMS_OUTPUT.PUT_LINE('block number : ' || blk_no);
DBMS_OUTPUT.PUT_LINE('row number : ' || row_no);
This section describes the functions provided by the DBMS_ROWID package, in alphabetical order.
Creates the ROWID with the entered information. This function is useful for checking whether a ROWID has been created correctly. The user can try to create a ROWID with random information but this is not meaningful.
Details about the ROWID_CREATE procedure are as follows:
Prototype
FUNCTION ROWID_CREATE
(
segment_number IN NUMBER,
absolute_fno IN NUMBER,
block_number IN NUMBER,
row_number IN NUMBER
)
RETURN ROWID;
Parameter
| Parameter | Description |
|---|---|
| segment_number | Segment number. |
| absolute_fno | Absolute file number. |
| block_number | Block number within the file. |
| row_number | Row number within the block. |
Example
DBMS_ROWID.ROWID_INFO(rowid_1, sgmt_no, fno, blk_no, row_no); my_rowid := DBMS_ROWID.ROWID_CREATE(sgmt_no, fno, blk_no, row_no); DBMS_OUTPUT.PUT_LINE(rowid_1 || ' ' || my_rowid);
Extracts and returns the segment number from the given ROWID.
Details about the ROWID_SEGMENT function are as follows:
Prototype
FUNCTION ROWID_SEGMENT
(
row_id IN ROWID
)
RETURN NUMBER;
Parameter
| Parameter | Description |
|---|---|
| row_id | ROWID. |
Example
SELECT dbms_rowid.rowid_segment(rowid) FROM table;
Extracts and returns the block number in the file for the given ROWID.
Details about the ROWID_BLOCK_NUMBER function are as follows:
Prototype
FUNCTION ROWID_BLOCK_NUMBER
(
row_id IN ROWID
)
RETURN NUMBER;
Parameter
| Parameter | Description |
|---|---|
| row_id | ROWID. |
Example
SELECT dbms_rowid.rowid_block_number(rowid) FROM table;
Extracts and returns the row number in the block for the given ROWID.
Details about the ROWID_ROW_NUMBER function are as follows:
Prototype
FUNCTION ROWID_ROW_NUMBER
(
row_id IN ROWID
)
RETURN NUMBER;
Parameter
| Parameter | Description |
|---|---|
| row_id | ROWID. |
Example
SELECT dbms_rowid.rowid_row_number(rowid) FROM table;
Extracts and returns the absolute file number in the segment for the given ROWID.
Details about the ROWID_ABSOLUTE_FNO function are as follows:
Prototype
FUNCTION ROWID_ABSOLUTE_FNO
(
row_id IN ROWID
)
RETURN NUMBER;
Parameter
| Parameter | Description |
|---|---|
| row_id | ROWID. |
Example
SELECT dbms_rowid.rowid_absolute_fno(rowid) FROM table;
Extracts the absolute file number for the given ROWID and converts it to a relative file number of the tablespace.
Details about the ROWID_TO_RELATIVE_FNO function are as follows:
Prototype
FUNCTION ROWID_TO_RELATIVE_FNO
(
row_id IN ROWID
) RETURN NUMBER;
Parameter
| Parameter | Description |
|---|---|
| row_id | ROWID. |
Example
SELECT dbms_rowid.rowid_to_relative_fno(rowid) FROM table;