1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Pseudocolumns in Oracle

Discussion in 'SQL PL/SQL' started by rajavu, Oct 16, 2008.

  1. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Pseudo columns are associated with table data , but nothing to do with table data and retrieved from database. But it looks like retrieved from the table data directly .

    Some of the Most commonly used Pseudo columns in Oracle are

    1. SYSDATE
    2. SYSTIMESTAMP
    3. ROWID
    4. ROWNUM
    5. USER
    6. UID
    7. LEVEL
    8. CURRVAL
    9. NEXTVAL


    1. SYSDATE

    It shows the Current date from the local or remore database . We can use the CURRENT_DATE also with for the same purpose.

    Code (Text):


    SELECT SYSDATE FROM DUAL
     
    2. SYSTIMESTAMP

    Systimestamp function returns the current system date and time (including fractional seconds and time zone) on your database.

    Code (Text):

    SELECT SYSTIMESTAMP  FROM DUAL

     
    3. ROWID

    Rowid is a pseudo column that uniquely identifies a row within a table, but not within a database. It is possible for two rows of two different tables stored in the same cluster to have the same rowid

    Code (Text):

    SELECT ROWID FROM EMP

     
    4. ROWNUM

    Rownum numbers the records in a result set. The first record that meets the where criteria in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.

    Code (Text):

    SELECT ROWNUM  FROM EMP WHERE ROWNUM <=10

     
    5. USER

    User is a pseudo column that returns the name of the user currently connected to the session.

    Code (Text):

    SELECT USER FROM DUAL

     
    6. UID

    Uid is a pseudo column that returns the id number of a user currently connected to the session.

    Code (Text):

    SELECT UID FROM DUAL

     
    7. LEVEL

    LEVEL pseudo-column is an indication of how deep in the tree one is. It is used in hierarchical queries along with CONNECT by clause.

    Code (Text):

    SELECT  level, empno, ename, mgr
    FROM     emp
    CONNECT BY PRIOR empno = mgr
    START WITH mgr IS NULL

     
    8. NEXTVAL

    NEXTVAL is used to invoke a sequence. If nextval is invoked on a sequence, it makes sure that a unique number is generated.

    Code (Text):

    SELECT <SEQUENCE>.NEXTVAL FROM DUAL

     
    9. CURRVAL

    CURRVAL can only be used if a session has already called nextval on a trigger. currval will then return the same number that was generated with nextval.

    Code (Text):

    SELECT <SEQUENCE>.CURRVAL FROM DUAL

     

    There are other psuedocolums also available in Oracle like versions_xid, versions_operation, versions_startscn, versions_starttime, versions_endscn, versions_endtime, ora_rowscn,object_value etc. But they are not widely used as above pseudo columns
     
  2. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Several spelling mistakes there.
    Code (Text):
    SQL> SELECT SYSTIMESTAM  FROM DUAL
    2 ;
    SELECT SYSTIMESTAM FROM DUAL
    *
    ERROR at line 1:
    ORA-00904: "SYSTIMESTAM": invalid identifier

    Subject is Psedocolumns it will be Pseudocolumns. There are other Pseudocolumns. I will try to make example here if I free.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Thanx Arju for pointing out the mistakes . I corrected the pointed mistakes . Expecting your support as always .
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Very Good list, I can add a few :)

    Pseudo Columns with flashback

    With flashback queries, the following Pseudo Columns are available:
    • versions_xid
    • versions_operation
    • versions_startscn
    • versions_starttime
    • versions_endscn
    • versions_endtime


    ora_rowscn

    If a table is created with norowdependencies, the ora_rowscn will be the same for all rows within the same db block.

    If, however, a table is created with rowdependencies, then the ora_rowscn is different for each row according to the SCN at its commit time:

    object_value

    The OBJECT_VALUE pseudocolumn returns system-generated names for the columns of an object table, XMLType table, object view, or XMLType view.