[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

sysdate

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

  select sysdate from dual;

 

rownum

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;

 

rowid

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;

 

ora_scn

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;

 LEVEL

----------

 1

 2

 3

 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;

 LV    NAME

---    ------- 

 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;

EMP_NO      ORA_ROWSCN

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

 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;

EMP_NO      TIMESTAMP

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

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