Tuesday 26 June 2012

Pseudo column's In Oracle

Definition:
Pseudocolumns are not actual columns in a table, but they behave like columns. They can be thought of as special-purpose data elements that can be used in SQL statements just as if they were part of the table, but are not actually contained in a table. Essentially, a pseudo-column is an Oracle assigned value used in the same context as an Oracle Database column, but not stored on disk. 
You can select values from a pseudocolumn but you cannot INSERT INTO, UPDATE, or DELETE from a pseudo-column. Pseudocolumns are allowed in SQL statements, but not in procedural statements. SQL and PL/SQL recognize the following SQL pseudocolumns, which return specific data items (this is a partial list):
ROWID: For each row in the database, the ROWID pseudocolumn returns the address of the row. Oracle Database rowid values contain information necessary to locate a row:
  • The data object number of the object
  • The data block in the data file in which the row resides
  • The position of the row in the data block (first row is 0)
  • The data file in which the row resides (first file is 1). The file number is relative to the tablespace.
Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.
NOTE:
 You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later.











































No comments:

Post a Comment