[Oracle] Pseudo-Columns

Published on 19 décembre 2013

The features provided by Oracle to DBA and devs are simply awesome,legion and sometimes pretty much unknown by the masses.

Okay so pseudo-columns are « variables », which values are assigned by Oracle. They are not stored on the disk but are accessible and queryable exactly like regular columns. I have already introduced one of them in a previous post (aka LEVEL in Learn hierarchical queries ).

The Usual Suspects


Get the current date and time of the base. The format depends on your NLS_DATE_LANGUAGE

  select sysdate from dual;



Number of each line resutling from your query. Depends on your query and not on the record that is retrieved. The example below will only return one row.

  select emp_id from emp_tab where rownum =1;



Number of each record,  unlike rownum this one is bound to each record. Again: only one row but this time the id is linked to the row.

  select emp_id from emp_tab where rowid =1;


level Used with the connect by clause. Hierarchical queries oragnize stuff in trees and this pseudo-column shows which level is each row on. (starts at 0)

  select ancestor,level from weasleys
  connect by prior ancestor = child
  starts with = 'XXX';


curval / nextval 

These two are used for sequences and are quite self explainatory  😉

  select my_sequence.curval from dual;



SCN for System Change Number, this number tracks data modifications. It’s really helpful when you want to know which records where last edited.

  select * from emp_table 
  order by ora_rowscn desc;


More examples

How to split a string in Oracle

To split a row we are going to use the level pseudo-column and its connect by clause…and a little regular expression.  😆

The level pseudo-column is used to « generate » rows. For example this query creates four rows

  select level from dual connect by level = 4;







 Then the regexp_substr works just like a regular susbstr : you can specify a starting position and the number of the occurence you want.

  substr : string to search , position to start, length
  regexp_substr: string to search, pattern, position to start, nth occurence</code></pre>

Everything put together looks something like this:

  select level as lv, regexp_substr ('smith,jones,doe,joe', '[^,]+', 1, level) name
  FROM dual
  CONNECT BY regexp_substr('smith,jones,doe,joe', '[^,]+', 1, level) is not null;


---    ------- 

 1     smith

 2     jones

 3     doe

 4     joe


How to know the last updated time of a row

Ora_rowscn will do the job, however this is what you get when using it raw:

  select emp_no,ora_rowscn from employee
   where rownum = 1
   order by ora_rowscn desc;


----------- ----------

 ALTO       2536313481

No worries though, you can easily convert it to a readable date with the scn_to_timestamp. But careful the retention time of scn number is not infinite so might get a ORA-08181 error. Anyways:

  select emp_no, scn_to_timestamp(ora_rowscn) as timestamp
  from employee_tab et
  where emp_no = '1234A'
  order by ora_rowscn desc;


----------- -------------------------------------------------

1234A       19/12/13 13:49:21,000000000
comments powered by Disqus