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;
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:
comments powered by Disqus
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