Thursday, November 8, 2012


pseudo-column is an Oracle assigned value (pseudo-field) used in the same context as an Oracle Database column, but not stored on disk. SQL and PL/SQL recognizes the following SQL pseudocolumns, which return specific data items: SYSDATE, SYSTIMESTAMP, ROWIDROWNUMUIDUSER, LEVEL, CURRVAL, NEXTVAL, ORA_ROWSCN, etc.

1) Pseudo columns behave like a table column, but it is not actually stored in a table.

2) Upon pseudo columns only select statements can be implemented. Insert, delete,update cannot be implemented.

Currval, (can be used in set clause of an update, values clause of an insert)

Nextval, (can be used in set clause of an update, values clause of an insert)

Level, Rowid, Rownum.

Download here PDF on Pseudo column and functions in SQL

SQL> SELECT sysdate, systimestamp FROM dual;
--------- ----------------------------------------
13-DEC-07 13-DEC-07 AM +02:00

SQL> select ename, ORA_ROWSCN, SCN_TO_TIMESTAMP(ORA_ROWSCN) from emp where empno=7369;
---------- ---------- ----------------------------------------------------------------
SMITH         2113048 20/12/2008 16:59:51.000


  1. Bonjour,

    Jeez oh man,while I applaud for your writing , it’s just so damn straight to the point SQL & PL/SQL.

    I have a requirement as below

    I have a table with 3 columns

    Col1 Col2 Col3 lowrange highrange
    BRID A 1 1 3
    BRID A 3 1 3
    BRID C 1 1 5
    BRID C 2 1 5
    BRID C 3 1 5
    BRID C 5 1 5
    BRID B 1 1 7
    BRID B 2 1 7
    BRID B 3 1 7
    BRID B 5 1 7
    BRID B 7 1 7

    We need to insert the missing number in col3 between lowrange and highrange.
    I have managed to insert the missing numbers.

    However, if we need to insert exactly between the missed place.

    I would not be able to use order by as Col2 is not maintained in any order.

    The output should be as below

    Col1 Col2 Col3
    BRID A 1
    BRID A 2
    BRID A 3
    BRID C 1
    BRID C 2
    BRID C 3
    BRID C 4
    BRID C 5
    BRID B 1
    BRID B 2
    BRID B 3
    BRID B 4
    BRID B 5
    BRID B 6
    BRID B 7

    Kindly let me now any logic to achieve the same.

    THANK YOU!! This saved my butt today, I’m immensely grateful.

    Many Thanks,

  2. Selamat Petang,

    The challenge however, is we don’t yet know how it will be used making to hard to assess their value proposition and consequently value.

    how to apply refcursor in package with multiple insted of multple cursors in oracle?
    Give one example.
    Thank you very much and will look for more postings from you.