Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL ROWID Keyword      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: ROWID

Definition:
In Oracle PL/SQL, the ROWID can be used as a pseudocolumn and also as a Data Type.

1. As a pseudocolumn

An Oracle server assigns each row in each table with a unique ROWID to identify the row in the table. The ROWID is the address of the row which contains the data object number, the data block of the row, the row position and data file. Using the ROWID method is generally considered to be the fastest way to search for a given row in the database.

It's important to note that ROWID values are not necessarily unique within a database. It is entirely possible for two rows of two different tables stored in the same cluster to have the same ROWID.

The ROWID is intended to be immutable (that is, unchangeable) and the user should not try to change it once it is assigned.

Note that the the ROWID may change if the row is physically moved on disk, such as:
  • Doing an export or import of the table
  • Doing ALTER TABLE XXXX MOVE
  • Doing ALTER TABLE XXXX SHRINK SPACE
  • Doing FLASHBACK TABLE XXXX
  • When splitting a partition
  • When updating a value so that it moves to a new partition
  • When combining two partitions

Example Usage:

The SQL query below displays the ROWID for the EMPLOYEE table records

SQL> SELECT ROWID,  ENAME, DEPTNO, SAL FROM EMPLOYEE;

ROWID ENAME DEPTNO SAL
------------------ ------ ---------- ----------
AAAIVuAABAAAMhCAAA JOHN 100 2300
AAAIVuAABAAAMhCAAB MILLER 110 3900
AAAIVuAABAAAMhCAAC KATE 120 4500



2. As a Data Type

As a Data Type, the ROWID is a valid data type of a column and is available in both SQL and Pl/SQL. It stores the ROWID pseudocolumn value of a row in the database.

Example Syntax:

[COLUMN] ROWID


Example Usage:

The below SQL statements create a table T_ROW with RID as ROWID column. Note that it is updated with ROWID of its own row.

CREATE TABLE T_ROW
(ID NUMBER,
RID ROWID);

Table created.

INSERT INTO T_ROW(ID) VALUES(1)

1 row created.

UPDATE T_ROW T
SET RID = T.ROWID

1 row updated.

SQL> SELECT * FROM T_ROW;

ID RID
------------------ ----------
AAAIYxAABAAAMiSAAA 1



Related Links:

Related Code Snippets:
  • CHARTOROWID - CHARTOROWID converts a value from CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to RO...
  • ROWIDTOCHAR - ROWIDTOCHAR converts a rowid value to VARCHAR2 datatype. The result of this conversi...
  • ROWIDTONCHAR - ROWIDTONCHAR converts a rowid value to NVARCHAR2 datatype. The result of this conv...
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 255 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?