How to start Application Express

After installing Oracle Express, point your web browser to http://localhost:8080/apex
To change the default port:

sqlplus /nolog
connect sys/mypassword as sysdba
exec  dbms_xdb.sethttpport('9090');

Oracle How to pivot a table

create table winners
(year varchar(4) not null,
team varchar(30) not null,
constraint pk_winners primary key (year));

insert into winners (year,team) values ('1990','SAINTS');
insert into winners (year,team) values ('1991','SWANS');
insert into winners (year,team) values ('1992','DOGS');
insert into winners (year,team) values ('1993','MAGPIES');
insert into winners (year,team) values ('1994','TIGERS');


SQL> select * from winners;

YEAR TEAM
---- ------------------------------
1990 SAINTS
1991 SWANS
1992 DOGS
1993 MAGPIES
1994 TIGERS

5 rows selected.

SQL> select max(decode(year,'1990',team,'')) as "1990",
max(decode(year,'1991',team,'')) as "1991",
max(decode(year,'1992',team,'')) as "1992",
max(decode(year,'1993',team,'')) as "1993",
max(decode(year,'1994',team,'')) as "1994"
from winners

1990       1991       1992       1993       1994
---------- ---------- ---------- ---------- ----------
SAINTS     SWANS      DOGS       MAGPIES    TIGERS

1 row selected.

Oracle:How to get first and last rows in an aggregation

create table transactions (
id integer not null,
tdate date not null,
opening_bal number(8,2) not null,
closing_bal number(8,2) not null,
transaction number(8,2) not null,
constraint pk_transactions primary key (id,tdate));

insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'01-jan-10',0,10,10);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'02-jan-10',10,20,10);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'03-jan-10',20,25,5);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'04-jan-10',25,-15,-40);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'05-jan-10',-15,0,15);
insert into transactions (id,tdate,opening_bal,closing_bal,transaction) values (1,'06-jan-10',0,13,13);


SQL> select * from transactions;

        ID TDATE     OPENING_BAL CLOSING_BAL TRANSACTION
---------- --------- ----------- ----------- -----------
         1 01-JAN-10           0          10          10
         1 02-JAN-10          10          20          10
         1 03-JAN-10          20          25           5
         1 04-JAN-10          25         -15         -40
         1 05-JAN-10         -15           0          15
         1 06-JAN-10           0          13          13

6 rows selected.
SQL> select id, count(tdate) as tcount
  ,max(opening_bal) keep (dense_rank first order by tdate) as opening_bal
  ,max(closing_bal) keep (dense_rank last order by tdate) as closing_bal 
from transactions 
where tdate between '02-jan-10' and '04-jan-10' 
group by id;

        ID TCOUNT       OPENING_BAL CLOSING_BAL
---------- ------------ ----------- -----------
         1            3          10         -15

SQL>

Oracle How to generate random data (without PL/SQL)

This is a handy way of generating random data. The number of rows generated is defined by the "connect by level" (currently set to 5).

column s format A5
column r format 999999999999

select level,dbms_random.random r,dbms_random.value(1,10) v,dbms_random.string('','5') s,sysdate+level d
from dual
connect by level <= 5;

LEVEL     r               V           s      D        
---------- ------------- ---------- ----- ---------
         1     384454788 5.86831193 PBZVI 14/AUG/10
         2     568121861 8.77328309 YHVDP 15/AUG/10
         3    -918508229 6.24565917 CHKXW 16/AUG/10
         4    1409236401 4.15254419 HAZUS 17/AUG/10
         5   -1356731387 4.12237254 HDDDX 18/AUG/10

5 rows selected.

Oracle How to create a user

SQL> create user DEV identified by DEV;
User created.
SQL> grant create session to dev;
Grant succeeded.
SQL> grant resource to DEV;
Grant succeeded.
SQL> grant select any table to dev;
Grant succeeded.
SQL> grant select_catalog_role to dev;
Grant succeeded.
SQL>

Oracle: How to convert to local time

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select
cast(from_tz(cast (to_date('2011-01-01 16:00:00') as timestamp),'GMT') at local as date) as LOCAL_EVENT_DATE
from dual;
alter session set succeeded.
LOCAL_EVENT_DATE
-------------------------
2011-01-02 02:00:00
1 rows selected

How to check for valid numbers


-- select records from a table where a varchar field contains only valid numbers
-- Method 1
select emp_id,hours_worked
from emp
where trim(translate(hours_worked,'+-.0123456789',' ')) is null

translate(expr_string, from_string, to_string) converts searches through expr_string and replaces every character in from_string to the corresponding character in to_string. If no corresponding character is found (i.e. the nth character in from_string has no corresponding character in to_string), then the character is removed.
Examples

• translate('12345', '14', '69') => '69345'
• translate('12345', '41', '96') => '69345'
• translate('12345', '12345', ' ') => ' '

Note that to_string must have at least one character (a space in the last example) otherwise all characters are removed because to_string is effectively NULL.
Note also that this will not work for values with the pattern 9999-9999
This function can be used to test if a value is numeric by trimming as shown in the SQL snippet above.
-- Method 2
select emp_id,hours_worked
from emp
where regexp_like(hours_worked,'^\d+$')

Oracle Dates

Internal Date Format
Oracle dates are stored as a structure made up of 7 fields each of which is 1 bytes long.
byte 1: century + 100
byte 2: year + 100
byte 3: month
byte 4: day of month
byte 5: hour + 1
byte 6: minute + 1
byte 7: second + 1

create table t (d date);

insert into t values (to_date('2001-05-30 08:05:30','yyyy-mm-dd hh24:mi:ss'));

select dump(d) from t;

DUMP(D)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,101,5,30,1,1,1

Default Date Format
The default date format is used whenever a date value has to be displayed (e.g. the result of a select). It is set using the nls_date_format initialization parameter. By default it is 'dd-MON-yy'. You can change this for the current session using the 'ALTER SESSION' command.

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Adding days
Adding days is very simple - just use normal arithmetic operators (+ and -).

alter session set nls_date_format='yyyy-mm-dd';
var d varchar2(10);
exec :d := '2010-01-31';
select :d, to_date(:d)+1 from dual;

:D                               TO_DATE(:D
-------------------------------- ----------
2010-01-31                       2010-02-01

Adding months
Adding months is just as easy but you have to be aware of a little quirk. When adding a month to an end of month date, the result is also the end of month. Note that you can use negative numbers for the second argument.
Moving from a month with 30 days to a month with 31 days could "lose" a day.

exec :d := '2001-04-30';

select :d,add_months(:d,1) from dual;

:D                               ADD_MONTHS
-------------------------------- ----------
2001-04-30                       2001-05-31

This is what could happen when moving from a non-leap year to a leap year.

exec :d := '2003-02-28';

select :d,add_months(:d,12) from dual;

:D                               ADD_MONTHS
-------------------------------- ----------
2003-02-28                       2004-02-29

Oracle Analytic Function

SELECT
SALES_OFFICER_CODE
, SALES_OFFICER_NAME
, SOURCE_START_DATE
, EFFECTIVE_DATE
, END_DATE
, SOURCE_END_DATE
, ROW_NUMBER() OVER (PARTITION BY SALES_OFFICER_CODE ORDER BY SALES_OFFICER_CODE,SOURCE_START_DATE desc) as rn
, FIRST_VALUE(SOURCE_START_DATE) OVER (PARTITION BY SALES_OFFICER_CODE ORDER BY SALES_OFFICER_CODE,SOURCE_START_DATE desc) as fv
, NVL(LAG(SOURCE_START_DATE) OVER (PARTITION BY SALES_OFFICER_CODE ORDER BY SALES_OFFICER_CODE,SOURCE_START_DATE desc),to_date('1900/01/01','yyyy-mm-dd')) as lag
FROM SDMSYSDB.TSDMD_SALES_OFFICER_ROLE