Oracle Snippets

Useful(?) bits of SQL, PLSQL.

Convert date/time from one timezone to another

select
    to_date('15-Aug-2008 10:00:00','dd-Mon-yyyy hh24:mi:ss') as EST,
    NEW_TIME(to_date('15-Aug-2008 10:00:00','dd-Mon-yyyy hh24:mi:ss'),'EST','GMT') as GMT
from
    dual;

Time difference in seconds between two oracle DATE values.
D1 should be the most recent date.

select
    ( to_date( '&d1','dd-Mon-yyyy hh24:mi:ss' ) - to_date( '&d2','dd-Mon-yyyy hh24:mi:ss' ) )*24*60*60
from
    dual

Check number of bits (bitness) of Oracle executable:

select length(addr)*4 || '-bits' as word_length 
from v$process 
where rownum=1;

WORD_LENGTH
---------------------------------------------
32-bits

On Unix you can also:

$ file $ORACLE_HOME/bin/oracle
/oracle/product/10.2.0/db_1/bin/oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1
(SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped

That example from Oracle Enterprise Linux.