In Linux / Cygwin “stat” exists as a command

$ stat /etc/hosts
Access: 2010-08-25 15:20:49.782522200 +0200
Modify: 2010-08-18 14:04:25.868114200 +0200
Change: 2010-08-18 14:04:26.072413100 +0200

Or use the one-liner perl below

### st_atime;         /* Time of last access */
$ perl -e 'use POSIX;[-f"/etc/hosts"]&&print ctime((stat(_))[8])'
Wed Aug 25 15:20:08 2010
### st_mtime;         /* Time of last data modification */
$ perl -e 'use POSIX;[-f"/etc/hosts"]&&print ctime((stat(_))[9])'
Wed Jun 10 11:36:40 2009
### st_ctime;         /* Time of last file status change */
$ perl -e 'use POSIX;[-f"/etc/hosts"]&&print ctime((stat(_))[10])' 
Wed Aug 25 01:00:07 2010

For instance the Sun Certified Network Administrator will be Oracle Certified Expert, Oracle Solaris 10 Network Administrator.

But to get the OCE Solaris title, you need to upgrade your certification [...] to receive an Oracle certification title (check more details on the Oracle Certification website here).

I never upgraded my Sun credentials, I am a Solaris 2.6 system admin and Solaris 7 network admin, but this equivalency with OCE surprised me.

There are even two OCM Java certifications : Oracle Certified Master, Java EE 5 Enterprise Architect, originally Sun Certified Enterprise Architect (SCEA) and Oracle Certified Master, Java SE6 Developer, originally Sun Certified Java Developer (SCJD)

Also, since a few months, there is an upgrade to the 9i OCM DBA certification : 11g OCM Upgrade. I hope I can do this one

So far so good.

Also when I run a report from the shell, I do set the sqlplus settings I like and I expect the script to receive the output with no ORA- error at the beginning.

But watch this !

$ echo "set feed off hea off newp none
conn scott/tiger
select * from dual;"|sqlplus /nolog
ERROR:
ORA-28002: the password will expire within 10 days

X

My password will expire in 10 days. And this screw up my day

Over my past two years as a developer, I can tell you it is pretty bad. What does TRUNC(SYSDATE) mean? It is today’s date.

But when does today starts and ends? And at the time of the writing, it is already tomorrow in Thailand.

We could argue about using CURRENT_DATE instead of sysdate, but it does not solve everything. Maybe you want your day to end at 10:30pm and initialize for the next day after some post processing. Maybe you want some components running in different timezones. Maybe you want to rollback and rollforward the date in your test systems…

Also the bad news, if your report hardcodes SYSDATE, kind of SELECT SUM(AMT) FROM T WHERE EXPIRY_DATE>TRUNC(SYSDATE), you will not be able to run it tomorrow in case it fails today.

No, I think that you would better store the date in a table and update it daily.

So you will have SELECT SUM(T.AMT) FROM T, TRADINGDAY WHERE T.EXPIRY_DATE>TRADINGDAY.TRADINGDAY and a daily job running UPDATE TRADEDAY SET TRADINGDAY=TRADINGDAY+1;.

You may want to consider the performance overhead of reading from a table instead of using the very performant SYSDATE function…

Actually, Oracle 10g is more than 5 years old and it is the only version available as Express Edition, 11g has not been released at the time of the writing, and no patch has been released, not even for the worst bugs or security breaches.

Most probably you will not want to run this for your sensitive data.

If you are serious about Oracle Development, you could download the Enterprise Edition for free, and get the Personal Edition on Oracle Shop for support and patches.

Last Friday 13th August, a news/rumor (not an official announcement) was posted on the opensolaris forum, apparently OpenSolaris has been cancelled in favor of an Oracle Solaris 11 Express. Not sure if it will come in the same format as the database XE, but this does not sound to please the community.

After the Oracle sues Google, it does not make Oracle very popular in the opensource community at the very moment

YMMV

Second surprise, not sure if it is a generic issue or related to the one above, a full checkout take ages .

svn and cvs command line interfaces almost have the same options, a bit like vi and vim -vi is my favorite- but the “tagging” massively differs. The is no cvs tag command in subversion, you use a different syntax.
cvs :

$ cd /home/lsc/CVS/proj1/source/pkg
$ cvs tag MYTAG helloworld.pkb helloworld.pks

$ cd /home/lsc/SVN/proj1/source/pkg
$ svn copy --parents helloworld.pkb http://myserver.domain.com/svn/REPOS1/proj1/tags/MYTAG/proj1/source/pkg -m "my first svn tag"
$ svn copy --parents helloworld.pks http://myserver.domain.com/svn/REPOS1/proj1/tags/MYTAG/proj1/source/pkg -m "my first svn tag"

I always loved the cvs rename command

$ cvs rename pkg1.pkb pkg2.pkb
cvs [rename aborted]: Remote server does not support rename

$ svn move pkg1.pkb pkg2.pkb
A         pkg2.pkb
D         pkg1.pkb

Update: One more annoyance, you cannot checkout a single file
subversion faq

You can use it to extract values from your xml files within your shell scripts

$ cat foo.xml
<emplist>
  <emp no="1">
    <ename>John</ename>
  </emp>
  <emp no="2">
    <ename>Jack</ename>
  </emp>
</emplist>
$ echo 'cat //emplist/emp[@no="1"]/ename/text()'|
  xmllint --shell foo.xml |
  sed -n 3p
John

I like this !

As written there and there, recursive with has more capabilities. It is also ANSI and implemented in DB2, MS SQL Server. CONNECT BY is an Oracle oddity. But does recursive with perfoms as well as connect by?

1. Create a big emp

create table big_emp as
with t(empno,mgr) as
(select 1, null from dual
union all 
select empno+1,trunc(dbms_random.value(1+empno/10,empno))
from t 
where empno<100000)
select * from t

2. execution plan

CONNECT BY


select empno,mgr
from big_emp
connect by mgr = prior empno
start with mgr is null;

E M 
- -
1 -  
2 1 
4 2 
6 2 
8 6 

Operation                 Object    Rows Time Cost   Bytes
------------------------- ------- ------ ---- ---- -------
SELECT STATEMENT                       3    3  185      78
CONNECT BY WITH FILTERING
TABLE ACCESS FULL         BIG_EMP      1    1   61      10
HASH JOIN                              2    2  122      46
CONNECT BY PUMP
TABLE ACCESS FULL         BIG_EMP 100000    1   61 1000000

recursive WITH


with e(empno,mgr) as (
select empno, mgr 
from big_emp 
where mgr is null
union all
select f.empno,f.mgr 
from big_emp f, e 
where e.empno=f.mgr)
select empno,mgr
from e;

E M
- -
1 -  
2 1 
3 1 
4 2 
5 3 
...

Operation                 Object    Rows Time Cost   Bytes
------------------------- ------- ------ ---- ---- -------
SELECT STATEMENT                       3    3  183      78
VIEW                                   3    3  183      78
UNION ALL (RECURSIVE WITH) BREADTH FIRST
TABLE ACCESS FULL BIG_EMP              1    1   61      10
HASH JOIN                              2    2  122      46
RECURSIVE WITH PUMP
TABLE ACCESS FULL BIG_EMP         100000    1   61 1000000

In this particular simple case, it seems CONNECT BY have a 1% higher cost.

3. execution time

CONNECT BY


select sum(mgr)
from 
(
select empno,mgr
from big_emp
connect by mgr = prior empno
start with mgr is null
)

SUM(MGR) 
------------
2745293877 

1 rows returned in 0.73 seconds 

recursive with


with e(empno,mgr) as (
select empno, mgr 
from big_emp 
where mgr is null
union all
select f.empno,f.mgr 
from big_emp f, e 
where e.empno=f.mgr)
select sum(mgr)
from e;

SUM(MGR) 
------------
2745293877 

1 rows returned in 1.24 seconds 

Honestly I am not surprised that CONNECT BY is faster, CONNECT BY has been in the Oracle database forever and has been massively tuned in 8.1.7.4.

And then one day you find, ten years have got behind you.

I remembered a thread in developpez.net that reveals the dubious implementation of nocycle in 10g.

For the CONNECT BY ISLEAF, I have read the technique on amis.nl.

Ok, here is my graph

The 10g query

with o as
(
SELECT 'A' obj, 'B' link from dual union all
SELECT 'A', 'C' from dual union all
SELECT      'C', 'D' from dual union all
SELECT           'D', 'C' from dual union all
SELECT           'D', 'E' from dual union all
SELECT                'E', 'E' from dual)
select connect_by_root obj root,level,obj,link,
  sys_connect_by_path(obj||
'->'
||link,','),
  connect_by_iscycle,
  connect_by_isleaf
from o 
connect by nocycle obj=prior link
start with obj='A';

ROOT LEVEL O L PATH                 CYCLE  LEAF
---- ----- - - -------------------- ----- -----
A        1 A B ,A->B                    0     1
A        1 A C ,A->C                    0     0
A        2 C D ,A->C,C->D               1     0
A        3 D E ,A->C,C->D,D->E          1     1

Obviously in 10g the connect by nocycle does works well with that kind of graphs, D-C and E-E are missing and C-D and D-E are marked as cycling…

Let’s try the 11gR2 equivalency.

with o(obj,link) as
(
SELECT 'A', 'B' from dual union all
SELECT 'A', 'C' from dual union all
SELECT      'C', 'D' from dual union all
SELECT           'D', 'C' from dual union all
SELECT           'D', 'E' from dual union all
SELECT                'E', 'E' from dual),
t(root,lev,obj,link,path) as (
select obj,1,obj,link,cast(obj||'->'||link 
as varchar2(4000))
from o 
where obj='A'  -- START WITH
union all
select 
  t.root,t.lev+1,o.obj,o.link,
  t.path||', '||o.obj||
    '->'
    ||o.link
from t, o 
where t.link=o.obj
)
search depth first by obj set ord
cycle obj set cycle to 1 default 0
select root,lev,obj,link,path,cycle,
    case
    when (lev - lead(lev) over (order by ord)) < 0
    then 0
    else 1
    end is_leaf
 from t;

ROOT LEV  OBJ  LINK PATH                        CYCLE IS_LEAF
---- ---- ---- ---- --------------------------- ----- -------
A    1    A    B    A->B                            0       1
A    1    A    C    A->C                            0       0
A    2    C    D    A->C, C->D                      0       0
A    3    D    C    A->C, C->D, D->C                0       0
A    4    C    D    A->C, C->D, D->C, C->D          1       1
A    3    D    F    A->C, C->D, D->E                0       0
A    4    F    F    A->C, C->D, D->E, E->E          0       0
A    5    F    F    A->C, C->D, D->E, E->E, E->E    1       1

It looks good

If you exclude the rows with cycle=1, you get the six rows for the graph.

Do you want to continue ?

If you are expecting “y” or “n” but do not want to enforce the user to type y[Enter] but simply y, you can use the -n option in bash.

Within a ksh script:

yorn=$(bash -c 'read -p "Do you want to continue ? " -n 1 ans;echo "$ans"')

Within bash:

read -n 1 yorn