how to create Mailling Label Report in Oracle Report Developer 10g || Or...

how to Export Data from SQL Develope & Oracle Toad to MS EXCEL 2007 || ...

how to create table and insert data from ms access to oracle database 11...

how to create DSNs in ms access 2007 to connect oracle database 11g || O...

FormatTrigger

function F_QTYFormatTrigger return boolean is
begin

  -- Automatically Generated from Report Builder.
  if (((:COLOR_CODE = 'WHITE') and
       (:COLOR_CODE = 'BLUE')) and
       (:COLOR_CODE = 'PINK'))
  then
    srw.set_text_color('magenta');
  end if;

  return (TRUE);
end;

How to delete(remove) facebook group step by step || DUAL SOLUTIONS || O...

Character join

character join 


function remkFormula return Char is
begin
if :abs_day <> 0 then
  return(:abs_day ||' day absent = ' ||:abs_day*2 || ' days deduction');

else return(''); end if;

end;

How to EXPORT DDL Scripts from Oracle Database using Toad Developer||DUAL SOLUTIONS||OracleSAP 37

How to Create an Entity Relationship Diagram (ERD) from oracle database using Toad Developer || DUAL SOLUTIONS/OracleSAP 36

update add and cut

update add and cut 

SQL> select * from IT_item_BRAND;

BRAND_CODE BRAND_NM
---------- --------------------------------------------------
1          TENDA
2          POWER VOULT
3          TOSHIBA
4          3G TELETALK
5          ABLEREX
6          ACCER
7          APACER
8          APOLLO
9          ASUS
10         BANGLA LION
11         BLAZER

BRAND_CODE BRAND_NM
---------- --------------------------------------------------
12         BRB
13         BRB/ PARADISE
14         CALLER ID
15         CANON
16         CARRIER
17         CISCO
18         CITYCEL E-ONE
19         CLONE PC
20         COLOR PRINTER
21         D-LINK
22         DELAX

BRAND_CODE BRAND_NM
---------- --------------------------------------------------
23         DELL
24         DINTEK
25         EPSON
26         FLORA
27         GE GT6
28         GP
29         GRAMEEN PHONE
30         HITACHI
31         HP
32         INTEL
33         IT VISION

BRAND_CODE BRAND_NM
---------- --------------------------------------------------
34         KASPERSKY
35         KEA PRINNTING
36         LEVELONE
37         LEVELONE ( GES-1650)
38         LEVELONE (GES-2450)
39         LG
40         LINKSYS
41         MICKROTIK
42         MICROLAB
43         NATIONAL
44         NEC

BRAND_CODE BRAND_NM
---------- --------------------------------------------------
45         NOKIA
46         NOVA
47         OVO
48         PANASONIC
49         PANASONIC-500
50         PARADISE
51         PENDUIT
52         POWER PACK
53         PROLINK
54         QUBEE
55         SAMSUNG

BRAND_CODE BRAND_NM
---------- --------------------------------------------------
56         SMART/NOVA
57         SPLITE
58         SYMENTIC
59         TISA
60         TRANSCAND
61         TRANSCEND
62         VARBATIM
63         VERBATIM
64         VOP TECT
65         VOPTECH
66         WESTERN DIGITAL (WD)

BRAND_CODE BRAND_NM
---------- --------------------------------------------------
67         YDD
68         ZOOM ULTRA
69         BEST CHALLENGER
70         EURO
71         ZEBRA
72         POWER RITE
73         ASTA
74         KSTAR
75         SUMMIT
76         CLONE PC
77         SHARETECH

BRAND_CODE BRAND_NM
---------- --------------------------------------------------
78         A4TECH
79         FUJITSU
80         H TECH

80 rows selected.

SQL> commit;

Commit complete.

SQL> update IT_item_BRAND set BRAND_CODE='00'||brand_code ;

80 rows updated.

SQL> select * from IT_item_BRAND;

BRAND_CODE BRAND_NM
---------- --------------------------------------------------
001        TENDA
002        POWER VOULT
003        TOSHIBA
004        3G TELETALK
005        ABLEREX
006        ACCER
007        APACER
008        APOLLO
009        ASUS
0010       BANGLA LION
0011       BLAZER

BRAND_CODE BRAND_NM
---------- --------------------------------------------------
0012       BRB
0013       BRB/ PARADISE
0014       CALLER ID
0015       CANON
0016       CARRIER
0017       CISCO
0018       CITYCEL E-ONE
0019       CLONE PC
0020       COLOR PRINTER
0021       D-LINK
0022       DELAX

BRAND_CODE BRAND_NM
---------- --------------------------------------------------
0023       DELL
0024       DINTEK
0025       EPSON
0026       FLORA
0027       GE GT6
0028       GP
0029       GRAMEEN PHONE
0030       HITACHI
0031       HP
0032       INTEL
0033       IT VISION

BRAND_CODE BRAND_NM
---------- --------------------------------------------------
0034       KASPERSKY
0035       KEA PRINNTING
0036       LEVELONE
0037       LEVELONE ( GES-1650)
0038       LEVELONE (GES-2450)
0039       LG
0040       LINKSYS
0041       MICKROTIK
0042       MICROLAB
0043       NATIONAL
0044       NEC

BRAND_CODE BRAND_NM
---------- --------------------------------------------------
0045       NOKIA
0046       NOVA
0047       OVO
0048       PANASONIC
0049       PANASONIC-500
0050       PARADISE
0051       PENDUIT
0052       POWER PACK
0053       PROLINK
0054       QUBEE
0055       SAMSUNG

BRAND_CODE BRAND_NM
---------- --------------------------------------------------
0056       SMART/NOVA
0057       SPLITE
0058       SYMENTIC
0059       TISA
0060       TRANSCAND
0061       TRANSCEND
0062       VARBATIM
0063       VERBATIM
0064       VOP TECT
0065       VOPTECH
0066       WESTERN DIGITAL (WD)

BRAND_CODE BRAND_NM
---------- --------------------------------------------------
0067       YDD
0068       ZOOM ULTRA
0069       BEST CHALLENGER
0070       EURO
0071       ZEBRA
0072       POWER RITE
0073       ASTA
0074       KSTAR
0075       SUMMIT
0076       CLONE PC
0077       SHARETECH

BRAND_CODE BRAND_NM
---------- --------------------------------------------------
0078       A4TECH
0079       FUJITSU
0080       H TECH

80 rows selected.

SQL> commit;

Commit complete.

 update IT_item_BRAND set BRAND_CODE=SUBSTR(brand_code,2,3) ;


SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL; Substring --------- CDEF SELECT SUBSTR('ABCDEFG',-5,4) "Substring" FROM DUAL; Substring --------- CDEF
Assume a double-byte database character set:
SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes" FROM DUAL; Substring with bytes -------------------- CD

SQL>  select lpad(nvl(to_number(max(BRAND_CODE)+1),1),4,('0')) from  IT_ITEM_BRAND;

LPAD
----
0010

SQL>  select max(brand_code) from IT_ITEM_BRAND;

MAX(BRAND_
----------
009

SQL>  select max(to_number(brand_code)) from IT_ITEM_BRAND;

MAX(TO_NUMBER(BRAND_CODE))
--------------------------
                        80

SQL> select lpad(nvl(to_number(max(to_number(BRAND_CODE))+1),1),4,('0')) from  IT_ITEM_BRAND;

LPAD
----
0081

SQL>

Formula column report

1---formula column  report
-------------------------
function CF_mastercodeFormula return Char is
v varchar2(150);
begin
    select  :itemname||'('|| BR_ITEM_MAPPING.MASTERCODE ||')' into v
    from br_item_mapping
  where itemcode=:itemcode;
  return(v);
exception
    when others then
    return (:itemname);
end;

--------------
2----button level change
-------------------------------
--must be control block no
--item key board/item navigator no


if :SYSTEM.MODE='NORMAL' then
    CLEAR_FORM(NO_VALIDATE);
     set_item_property('CONTROL.QRY_BTN',label,'Query');
     --do_key('enter_query');
     enter_query;
else
    go_block('BR_CUSTOMER');
        --do_key('execute_query');
    execute_query;
    set_item_property('CONTROL.QRY_BTN',label,'Search');
end if;


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

3----item level when-mouse-double-click

declare
     ed_id editor;
     ed_name varchar2(40);
     val varchar2(32000);
     ed_ok boolean:=true;
begin
     ed_name:='ED_ADDR';
     ed_id:=find_editor(ed_name);
     val:=:BR_CUSTOMER.ADDRESS;
     show_editor(ed_id,val,10,14,val,ed_ok);
     :BR_CUSTOMER.ADDRESS:=val;
end;

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

4---enable/disable
---------------------

set_item_property ('pre_btn', visible, property_true);
set_item_property ('nxt_btn', visible, property_true);
set_item_property('pre_btn',enabled,property_true);
set_item_property('nxt_btn',enabled,property_true);

5----------------------
---post-change tirgger

if :lv_cd is not null then
    select lv_nm into :lv_nm from level_info where lv_cd=:lv_cd;
    select LPAD(NVL(max(exam_cd),0)+1,3,0) into :grd_cd from exam_info;
else
    message('Select exam level ');
    raise form_trigger_failure;
end if;


6-----------------------
------program unit or key next item trigger

------DATA ALREADY INSERTED

PROCEDURE DIVS IS
dnm char(40);
BEGIN
begin
  select cost_desc into dnm
  from
  cost
  where cost_sl=:emp_div.cost_cd;
exception
    when no_data_found
    then
    dnm :=null;
END;
if dnm is not null
    then :emp_div.cost_nm :=dnm;
      message('DATA ALREADY INSERTED');
        message('DATA ALREADY INSERTED');
    raise form_trigger_failure;
else
    :emp_div.cost_nm :=dnm;
    next_field;
end if;
end;



7---------
-------WHEN-NEW-BLOCK-INSTANCE  THANA HRM

execute_query;
last_record;
next_record;


8--------
---------Invalid Examination Code


declare
    bb char(40);
begin
    select exam_name into bb
    from exam_info
    where exam_cd=:grd_cd and lv_cd = :lv_cd;
if bb is not null
    then :grd:=bb;
    next_field;
else
        raise form_trigger_failure;
    :grd_cd :=null;
    :grd :=null;
    message('Invalid Examination Code');
    end if;
end;



9--------
-------form_success

PROCEDURE grd_code IS
BEGIN
 if form_success then
         copy('5','system.message_level');
         COMMIT_FORM; next_record;
         message('Data commited !');
         message('Data commited !');
 else
     message('Problem in Data ! Check your Data !');
     message('Problem in Data ! Check your Data !');
end if;
END;

10------------

----------INVALID COST CENTRE CODE

PROCEDURE cost_code IS
    bb char(45);
 begin
null;
    select cost_desc into bb
    from cost
    where cost_sl=:emp_desg.cost_cd;
if bb is not null
    then :emp_desg.COST_NM :=bb;
    next_field;
else
        raise form_trigger_failure;
    :emp_desg.cost_cd :=null;
:emp_desg.cost_nm :=null;
    message('INVALID COST CENTRE CODE');
    end if;
end;


11-------

when-validate-item
-------------------

begin
if :emp_mast.div_cd is not null then
        message('Are you sure Employee Division is Correct...??');
        message('Are you sure Employee Division is Correct...??');
    else
    null;
    raise form_trigger_failure;
end if;
end;

--message


12---------------
---INVALID EMPLOYEE ID

PROCEDURE EMP_CODE2 IS
BEGIN
begin
select a.emp_nm,a.desg_cd, c.desg_nm,a.dept_cd,f.dept_nm,a.sec_cd,g.sec_nm,a.cost_sl,h.cost_desc
into
:emp_desg.NM,:emp_desg.deg,:emp_desg.deptnm1,:emp_desg.dept_cd,:emp_desg.dept,
:emp_desg.sec_cd,:emp_desg.sec,:emp_desg.cost_cd,:emp_desg.cost_nm
from emp_mast a,emp_desg c,emp_dept f,emp_sec g,cost h
where a.emp_id=:emp_id and
        a.desg_cd=c.desg_cd and
      a.dept_cd=f.dept_cd(+)  and
      a.dept_cd=g.dept_cd(+) and
      a.sec_cd=g.sec_cd(+)  and
      a.cost_sl=h.cost_sl;
      --pbas;
next_field;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        message('INVALID EMPLOYEE ID !  ');
raise form_trigger_failure;
END;
 end;  


----13 DATA ALRADY POSTED TRY WITH NEW ONE
---------------------------------------------

PROCEDURE ptp_code IS
BEGIN
  select a.dept_cd,c.dept_nm,a.sec_cd,d.sec_nm,
         a.cost_sl,e.cost_desc,a.pt_dt,a.flg_tp
into :emp_desg.dept_cd,:emp_desg.dept,
:emp_desg.sec_cd,:emp_desg.sec,:emp_desg.cost_cd,:emp_desg.cost_nm,
:emp_desg.birth_dt,:emp_desg.tp
from emp_posting a,emp_dept c,emp_sec d,cost e
where a.com_cd = :com_cd and a.emp_id=:emp_desg.emp_id and
      a.cir_no=:emp_desg.circ_no and
      a.cir_dt=:emp_desg.doj and
      a.dept_cd=c.dept_cd and
      a.dept_cd=d.dept_cd and
      a.sec_cd=d.sec_cd and
      a.cost_sl=e.cost_sl;
 message('DATA ALRADY POSTED TRY WITH NEW ONE');
 raise form_trigger_failure;
exception
    when no_data_found
    then
    next_field;
END;



----------------
error set in oracle forms 10g
------------------------------
---ON-ERROR trigger form level


DECLARE
   lv_errcod  NUMBER       := ERROR_CODE;
   lv_errtyp  VARCHAR2(3)  := ERROR_TYPE;
   lv_errtxt  VARCHAR2(80) := ERROR_TEXT;
BEGIN
   IF (lv_errcod = 40508) THEN
       message('UNABLE TO INSERT RECORD: DUPLICATE CODE ENTRY');
       message('UNABLE TO INSERT RECORD: DUPLICATE CODE ENTRY');
       RAISE Form_Trigger_Failure;
    ELSIF (lv_errcod = 40510) THEN
        message('UNABLE TO DELETE RECORD: DATA EXISTS IN SUB GROUP');
       message('UNABLE TO DELETE RECORD: DATA EXISTS IN SUB GROUP');
       RAISE Form_Trigger_Failure;
    ELSIF (lv_errcod = 40509) THEN
       message('UNABLE TO UPDATE RECORD: DATA EXISTS IN SUB GROUP / DUPLICATE RECORD');
       message('UNABLE TO UPDATE RECORD: DATA EXISTS IN SUB GROUP / DUPLICATE RECORD');
       RAISE Form_Trigger_Failure;
    ELSE
   Message(lv_errtyp||'-'||to_char(lv_errcod)||': '||lv_errtxt);
   Message(lv_errtyp||'-'||to_char(lv_errcod)||': '||lv_errtxt);
   RAISE Form_Trigger_Failure;
    END IF;
END;



/

---------------------------------------
values set

---WHEN-VALIDATE-ITEM

if :slcode is not null then
    select nvl(max(mlink),0)+1 into :mlink from sl_code;        -----mlink number
else
    null;
end if;


--------
HRM(star)
----
when-validate_item

declare
    v number;
begin
    select count(distinct at_dt) into v from at_proc_mast where at_Dt =:dtd;
  
    if v>0 then
        message('Data Already Process...');
        RAISE FORM_TRIGGER_FAILURE;
    else
        null;
    end if;
  
end;



----set pre insert/ auto numner lpad 0001
--------------------------------------------

begin
    select lpad(nvl(to_number(max(mv_no)+1),1),4,('0'))
into :mov_info.mv_no
    from tour_info
    where at_dt=:mov_info.at_dt;
exception
    when no_data_found then
    null;
end;




end dt is grater then st_dt/at_dt then null
----------------------------------------------


begin
if :mov_info.edt<:mov_info .at_dt="" br="" then="">    raise form_trigger_failure;
else
    null;
    end if;
end;



----------------------------------------------
----validate date

declare
    v number;
begin
    select count(distinct at_dt) into v from at_proc_mast where at_Dt =:dtd;
  
    if v>0 then
        message('Data Already Process...');
        raise form_trigger_failure;
    else
        null;
    end if;
end;


--------


declare
    v number;
begin

    SELECT count(cust_id) INTO V FROM br_customer WHERE cust_id=:BR_CUSTOMER.cust_id;
    if v>0 then
            MESSAGE('Cusomer Id Already Exist !!');
          MESSAGE('Cusomer Id Already Exist !!');  
        raise form_trigger_failure;
    else
    next_field;
    end if;
end;

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


pre_insert
-------------

field varchar2
SELECT NVL(MAX(TO_NUMBER(recpt_no)),0)+1 into :br_receipt_master.recpt_no from br_receipt_master;


id restriction
---------------
declare
    v varchar2(6);
begin
    select emp_id into v from hrm.emp_mast where emp_id='001032';
    if :auth_by=v then
select emp_nm into :auth_nm from hrm.emp_mast where emp_id = :auth_by;
    else
        :auth_nm:='';
        :auth_by:='';
    end if;
    end;
next_field;


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

set field data form an others field
----------------------------------

go_block('emp_attn_data');
FIRST_RECORD;
-- WHILE TRUE LOOP
WHILE :System.Last_Record <> 'TRUE' LOOP
    if :emp_attn_data.flag is not null then
        null;
    elsif  :emp_attn_data.fst_in<>'000000' then
        null;
    else
        :emp_attn_data.fst_in:=:con.in_time;
        :emp_attn_data.fst_out:=:con.out_time;
    end if;
NEXT_RECORD;
IF :System.Last_Record = 'TRUE' THEN
    if :emp_attn_data.flag is not null then
        null;
    elsif  :emp_attn_data.fst_in<>'000000' then
        null;
    else
    :emp_attn_data.fst_in:=:con.in_time;
    :emp_attn_data.fst_out:=:con.out_time;
    end if;
EXIT;
END IF;
END LOOP;
first_record;



/

when-new-form_instarnce
-----------------------
:clock:=to_char(sysdate,'HH12:MI:SS');


-----------valid & invalid -----------

declare
    v number;
begin
    select dept_cd into v  from emp_dept where dept_cd=:dept_cd;
  
if v>0 then
  select dept_nm into :dept_nm from emp_dept where dept_cd=:dept_cd;

else
    message('Invalid Department id !!');
end if;
exception
    when others then
    raise form_trigger_failure;
end;

----------------
mouse duble click
-----------------
declare
    v_choosen BOOLEAN;
begin
    v_choosen :=show_lov('EMP');
    if v_choosen  then
        message('Value Choosen');
    else
        message('Value Not Choosen');
    end if;
end;


FIELD Y/N   Save or clear
------------------------

begin
    if :visitors.y_n='Y' then
        commit_form;clear_form;
    elsif :visitors.y_n='N' then
        clear_form(no_commit);
    else
        null;
    end if;
    end;



-----------------------------------
data already exist
------------------------------

declare
    v number(1);
begin
    select count(*) into v from emp_id_change where emp_id=:EMP_DESG.emp_id;

if v is not null then
    message('Employee ID already Transfered !!');
    message('Employee ID already Transfered !!');
else
go_item('emp_desg.NEMP_ID');
end if;

end ;

Take the Challenge!

Take the Challenge!

http://www.oracle.com/technetwork/issue-archive/2012/12-jan/o12plsql-1408561.html

Each PL/SQL 101 article offers a quiz to test your knowledge of the information provided in the article. The quiz questions are shown below and also at PL/SQL Challenge (plsqlchallenge.com), a Website that offers online quizzes for the PL/SQL language. You can read and take the quiz here in Oracle Magazine and then check your answers in the next issue. If, however, you take the quiz at PL/SQL Challenge, you will be entered into a raffle to win an e-book from O’Reilly Media (oreilly.com).

Question 1

Oracle Database provides a function for returning the date of the last day of the month. It does not, however, provide a function for returning the date of the first day. Which of the following can be used to do this?

1. CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
      RETURN DATE
   IS
   BEGIN
      RETURN TRUNC (date_in);
   END;
   /


2. CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
      RETURN DATE
   IS
   BEGIN
      RETURN TRUNC (date_in, 'MM');
   END;
   /


3. CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
      RETURN DATE
   IS
   BEGIN
      RETURN TRUNC (date_in, 'MONTH');
   END;
   /


4. CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
      RETURN DATE
   IS
   BEGIN
      RETURN TO_DATE (TO_CHAR (date_in, 'YYYY-MM')
   || '-01', 'YYYY-MM-DD');
   END;
   /

Question 2
Given this declaration section:

DECLARE
   c_format   CONSTANT VARCHAR2 (22)
      := 'YYYY-MM-DD HH24:MI:SS' ;
   l_new_year          DATE
      := TO_DATE (
            '2012-01-02 00:00:01'
          ,  c_format);
which of the following blocks offers an exception section so that after that block is executed, the date and time 2012-01-01 00:00:01 will be displayed on the screen?

1.  BEGIN
      DBMS_OUTPUT.put_line (
         TO_CHAR (
            l_new_year - 24
          ,  c_format));
   END;
2. BEGIN
      DBMS_OUTPUT.put_line (
         TO_CHAR (l_new_year - 1
                ,  c_format));
   END;
3. BEGIN
      DBMS_OUTPUT.put_line (
         TO_CHAR (
              l_new_year
            - 24 * 60 * 60
          ,  c_format));
   END;
4. BEGIN
      DBMS_OUTPUT.put_line (
         TO_CHAR (
              TRUNC (l_new_year)
            - 1
            + 1 / (24 * 60 * 60)
          ,  c_format));
   END;

Human Age calculation from oracle SQL

Human Age calculation from oracle SQL


SELECT (SUBSTR(TO_CHAR(SYSDATE, 'YYYYMMDD'),1,4) - SUBSTR(TO_CHAR('19830521','FM00000000'),1,4)) - (CASE WHEN SUBSTR(TO_CHAR(SYSDATE, 'YYYYMMDD'),5,2) < SUBSTR(TO_CHAR('19830521','FM00000000'),5,2) THEN 1 WHEN SUBSTR(TO_CHAR(SYSDATE, 'YYYYMMDD'),5,2) = SUBSTR(TO_CHAR('19830521','FM00000000'),5,2) AND SUBSTR(TO_CHAR(SYSDATE, 'YYYYMMDD'),7,2) < SUBSTR(TO_CHAR('19830521','FM00000000'),7,2) THEN 1
  ELSE 0  END) AGE FROM DUAL;

       AGE
----------
        34

SQL> select round(MONTHS_BETWEEN(sysdate,to_date('10-JAN-1989','DD-MON-YYYY')))||' Months' from dual;

ROUND(MONTHS_BETWEEN(SYSDATE,TO_DATE('10-JAN-19
-----------------------------------------------
345 Months

SQL> select round(MONTHS_BETWEEN(sysdate,to_date('10-JAN-1989','DD-MON-YYYY'))/12,1)||' Yrs' from dual;

ROUND(MONTHS_BETWEEN(SYSDATE,TO_DATE('10-JAN
--------------------------------------------
28.8 Yrs


============================================================
DATE is the datatype that we are all familiar with when we think about representing date and time values. It has the ability to store the month, day, year, century, hours, minutes, and seconds. It is typically good for representing data for when something has happened or should happen in the future. The problem with the DATE datatype is its' granularity when trying to determine a time interval between two events when the events happen within a second of each other. This issue is solved with the TIMESTAMP datatype.
In order to represent the date stored in a more readable format, the TO_CHAR function has traditionally been wrapped around the date:

SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "hiredate"
  FROM emp;

hiredate
-------------------
17.12.1980:00:00:00
20.02.1981:00:00:00

You can add and subtract number constants as well as other dates from dates. Oracle interprets number constants in arithmetic date expressions as numbers of days. For example:

• SYSDATE + 1 is tomorrow
• SYSDATE - 7 is one week ago
• SYSDATE + (10/1440) is ten minutes from now.

Subtracting the HIREDATE column of the EMP table from SYSDATE returns the number of days since each employee was hired.

SELECT '03.12.2004:10:34:24' "Now",
       TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
       TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')
       - hiredate "Hired since [Days]"
  FROM emp;
Now                 Hiredate            Hired since [Days]
------------------- ------------------- ------------------
03.12.2004:10:34:24 17.12.1980:00:00:00         8752.44056

You cannot multiply or divide DATE values. Oracle provides functions for many common date operations. For example, the ADD_MONTHS function lets you add or subtract months from a date. The MONTHS_BETWEEN function returns the number of months between two dates.

Subtraction between Dates

The trouble people get into when using the DATE datatype is doing arithmetic on the column in order to figure out the number of years, weeks, days, hours, and seconds between two dates. What needs to be realized when doing the calculation is that when you do subtraction between dates, you get a number that represents the number of days. You should then multiply that number by the number of seconds in a day (86400) before you continue with calculations to determine the interval with which you are concerned.

DEFINE Today = TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')

SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
       TO_CHAR(&Today,'DD.MM.YYYY:HH24:MI:SS') "Today",
       trunc(86400*(&Today-hiredate))-60*(trunc((86400*(&&Today-hiredate))/60)) "Sec",
       trunc((86400*(&Today-hiredate))/60)-60*(trunc(((86400*(&&Today-hiredate))/60)/60)) "Min",
       trunc(((86400*(&Today-hiredate))/60)/60)-24*(trunc((((86400*(&&Today-hiredate))/60)/60)/24)) "Hrs",
       trunc((((86400*(&Today-hiredate))/60)/60)/24) "Days"
FROM emp;

Hiredate            Today               Sec Min Hrs  Days
------------------- ------------------- --- --- --- -----
17.12.1980:00:00:00 03.12.2004:10:34:24  24  34  10  8752

Check out the above query for a possible solution on how to extract the individual time intervals for a subtraction of two dates. The fractions could be reduced but we wanted to show all the numbers to emphasize the calculation.

If you want a solution which breaks the days in years and month you can use the following query. We will use a leap year date, 01/01/2000 for example, for temporary purposes. This date will provide accurate calculation for most cases.

DEFINE DateDay = 8752.44056

SELECT
    TO_NUMBER(SUBSTR(A,1,4)) - 2000 years,
    TO_NUMBER(SUBSTR(A,6,2)) - 01 months,
    TO_NUMBER(SUBSTR(A,9,2)) - 01 days,
    SUBSTR(A,12,2) hours,
    SUBSTR(A,15,2) minutes,
    SUBSTR(A,18,2) seconds
FROM (SELECT TO_CHAR(TO_DATE('20000101','YYYYMMDD')
      + &DateDay,'YYYY MM DD HH24:MI:SS') A
      FROM DUAL);
     YEARS     MONTHS       DAYS HO MI SE
---------- ---------- ---------- -- -- --
        23         11         17 10 34 24

The new TIMESTAMP datatype

One of the main problems with the DATE datatype was its' inability to be granular enough to determine which event might have happened first in relation to another event. Oracle has expanded on the DATE datatype and has given us the TIMESTAMP datatype which stores all the information that the DATE datatype stores, but also includes fractional seconds.

Convert DATE datatype to TIMESTAMP datatype

If you want to convert a DATE datatype to a TIMESTAMP datatype format, just use the CAST function. As you can see, there is a fractional seconds part of '.000000' on the end of this conversion. This is only because when converting from the DATE datatype that does not have the fractional seconds it defaults to zeros and the display is defaulted to the default timestamp format (NLS_TIMESTAMP_FORMAT). If you are moving a DATE datatype column from one table to a TIMESTAMP datatype column of another table, all you need to do is a INSERT SELECT FROM and Oracle will do the conversion for you.

CREATE TABLE date_table (
  date1      DATE,
  time1      TIMESTAMP,
  time2      TIMESTAMP
);

INSERT INTO date_table (date1, time1, time2)
  VALUES (SYSDATE,
          TO_TIMESTAMP ('17.12.1980:00:00:00','DD.MM.YYYY:HH24:MI:SS'),
          TO_TIMESTAMP ('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')
  );
COMMIT;

SELECT CAST(date1 AS TIMESTAMP) "Date" FROM date_table;

Date
---------------------------------------------------------------------------
03-DEC-04 11.36.45.000000 AM

The TO_TIMESTAMP function

The TO_TIMESTAMP function converts a string to a timestamp. The syntax for the to_timestamp function is:

TO_TIMESTAMP ( string , [ format_mask ]  [ 'nlsparam' ]  )

string is the string that will be converted to a timestamp.
format_mask is optional.  This is the format that will be used to convert string to a timestamp.

 The following is a list of options for the format_mask parameter  These parameters can be used in many combinations.

Parameter	Explanation
YYYY	4-digit year
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
DD	Day of month (1-31).
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).

 Formatting of the TIMESTAMP datatype

Formatting of the new TIMESTAMP datatype is the same as formatting the DATE datatype. Beware while the TO_CHAR function works with both datatypes, the TRUNC function will not work with a datatype of TIMESTAMP. This is a clear indication that the use of TIMESTAMP datatype should explicitly be used for date and times where a difference in time is of utmost importance, such that Oracle won't even let you compare like values. If you wanted to show the fractional seconds within a TIMESTAMP datatype, look at the 'FF3' to only showing 3 place holders for the fractional seconds.
Formatting of the TIMESTAMP datatype:

SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date"
  FROM date_table;
Date
-------------------
12/17/1980 00:00:00

Formatting of the TIMESTAMP datatype with fractional seconds:

SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date"
  FROM date_table;

Date
-----------------------------
12/17/1980 00:00:00:000

Subtraction of two TIMESTAMP datatypes

Calculating the time difference between two TIMESTAMP datatypes is much easier than the old DATE datatype. Look at what happens when you just do the same substraction as in the above queries:

SELECT SUBSTR(time1,1,30) "Time1",
       SUBSTR(time2,1,30) "Time2",
       SUBSTR((time2-time1),1,30) "Time1 - Time2"
 FROM date_table;
Time1                          Time2                          Time1 - Time2
------------------------------ ------------------------------ ---------------------------
17-DEC-80 12.00.00.000000 AM   03-DEC-04 10.34.24.000000 AM   +000008752 10:34:24.000000

As you can see, the results are much easier to recognize, 8752 days, 10 hours, 34 minutes, and 24 seconds. This means no more worries about how many seconds in a day and all those cumbersome calculations. And therefore the calculations for getting the weeks, days, hours, minutes, and seconds becomes a matter of picking out the number by using the SUBSTR function as can be seen next:

SELECT SUBSTR(time1,1,30) "Time1",
       SUBSTR(time2,1,30) "Time2",
       SUBSTR((time2-time1), INSTR((time2-time1),' ')+7,2) "SS",
       SUBSTR((time2-time1), INSTR((time2-time1),' ')+4,2) "MI",
       SUBSTR((time2-time1), INSTR((time2-time1),' ')+1,2) "HH",
       TRUNC(TO_NUMBER(SUBSTR((time2-time1),1, INSTR(time2-time1,' ')))) "Days"
  FROM date_table;

Time1                          Time2                          SS MI HH       Days
------------------------------ ------------------------------ -- -- -- ----------
17-DEC-80 12.00.00.000000 AM   03-DEC-04 10.34.24.000000 AM   24 34 10       8752

NEXT_DAY and LAST_DAY functions

The NEXT_DAY and LAST_DAY functions can be used to calculate for example «the last Saturday in any given month». You can simply get the last day in the month, subtract 7 days from that, and then use NEXT_DAY to find the next Saturday after that one.

NEXT_DAY (date, char)

NEXT_DAY returns the date of the first weekday named by char that is later than date. The return type is always DATE, regardless of the datatype of date. The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument date.

Example

Return the date of the next Monday after now:

SELECT TO_CHAR(NEXT_DAY(sysdate,'MON'),'DD.MM.YYYY') "Next Monday from now"
  FROM DUAL;
Next Monday
-----------
 06.12.2004

LAST_DAY(date)

LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE, regardless of the datatype of date.

Example

The following statement determines how many days are left in the current month:

SELECT SYSDATE,
   LAST_DAY(SYSDATE) "Last",
   LAST_DAY(SYSDATE) - SYSDATE "Days Left"
   FROM DUAL;

SYSDATE     Last     Days Left
--------- --------- ----------
03-DEC-04 31-DEC-04         28

Get the last date of a month:

SELECT LAST_DAY (TO_DATE ('02','MM')) FROM dual;
 LAST_DAY
---------
29-FEB-04

Return the last Saturday of each month for a given year

You can simply get the last day in the month, subtract 7 days from that, and then use NEXT_DAY to find the next Saturday after that one.

DEFINE my_month = 12;

SELECT TO_CHAR (
  NEXT_DAY (
     LAST_DAY (TO_DATE (&my_month,'MM' )) - 7,
     TO_CHAR (TO_DATE ('29-01-1927', 'DD-MM-YYYY' ),'DAY')
  ),'DD.MM.YYYY') "Last Saturday in December 2004"
FROM dual;

Last Saturday in December 2004
------------------------------
25.12.2004

Return the last Saturdays for the current year.

SELECT TO_CHAR (
  NEXT_DAY (
    LAST_DAY (
        ADD_MONTHS (TRUNC(SYSDATE,'Y'),ROWNUM-1))-7,
        TO_CHAR (TO_DATE('29-01-1927', 'DD-MM-YYYY'),'DAY')
  ), 'DD.MM.YYYY') "Last Saturdays in 2004"
 FROM ALL_OBJECTS
WHERE ROWNUM <= 12;
Last Saturdays in 2004
----------------------
31.01.2004
28.02.2004
27.03.2004
24.04.2004
29.05.2004
26.06.2004
31.07.2004
28.08.2004
25.09.2004
30.10.2004
27.11.2004
25.12.2004

The "29-01-1927" is just a random date that we knew was a Saturday—any Saturday would do. This is done instead of using "SAT" in the query for international reasons, because in languages other than English, "SAT" isn't Saturday. This query should work in any language out there.

=========================================================================
You can use intrinsic Oracle functions to determine any date in the future:
 

Date / Time Math	Time Description
WHERE (date) > sysdate - 7/24;	Past 7 hours
WHERE (date) > sysdate - 7;	Past 7 days
WHERE (date) > sysdate - 7/1440;	Past 7 minutes
7/24 13/44	7 hours 13 hours
1/24/60/60 7/24/60/60	One second Seven seconds
1/24/60 5/24/60	One minute Five minutes
1/24 5/24	One hour Five hours
TRUNC(SYSDATE+1/24,'HH')	Every one hour starting with the next hour

For full examples of using Oracle data functions for scheduling, see Dr. Hall's book "Oracle Job Scheduling":

--  Schedule a snapshot to be run on this instance every hour

 

variable jobno number;

variable instno number;

begin

 

  select instance_number into :instno from v$instance;

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

-- Submit job to begin at 0600 and run every hour

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

dbms_job.submit(

   :jobno, 'statspack.snap;',

   trunc(sysdate)+6/24,

   'trunc(SYSDATE+1/24,''HH'')',

   TRUE,

   :instno);

 

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

-- Submit job to begin at 0900 and run 12 hours later

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

dbms_job.submit(

   :jobno,

   'statspack.snap;',

   trunc(sysdate+1)+9/24,

   'trunc(SYSDATE+12/24,''HH'')',

   TRUE,

   :instno);

 

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

-- Submit job to begin at 0600 and run every 10 minutes

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

dbms_job.submit(

   :jobno,

   'statspack.snap;',

   trunc(sysdate+1/144,'MI'),

   'trunc(sysdate+1/144,''MI'')',

   TRUE,

   :instno);

 

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

-- Submit job to begin at 0600 and run every hour, Monday - Friday

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

dbms_job.submit(
:jobno,
'statspack.snap;?,
trunc(sysdate+1)+6/24,
'trunc(
  least(
   next_day(SYSDATE - 1,'?MONDAY'?),
   next_day(SYSDATE - 1,''tUESDAY'?),
   next_day(SYSDATE - 1,'?WEDNESDAY'?),
   next_day(SYSDATE - 1,''tHURSDAY'?),
   next_day(SYSDATE - 1,'?FRIDAY'?)
)
+1/24,'?HH'?)',
TRUE,
:instno);

commit;
end;
/

---

Author Jeff Hunter also has examples of Oracle date math scheduling:
Run Statspack Snapshot Every 5 Minutes Starting at the Next 5 Minute Interval

variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno,
   'statspack.snap;',
   trunc(sysdate,'HH24')+
     ((floor(to_number(to_char(sysdate,'MI'))/5)+1)*5)/(24*60),
   'trunc(sysdate,''HH24'')+
     ((floor(to_number(to_char(sysdate,''MI''))/5)+1)*5)/(24*60)',
   TRUE, :instno);
COMMIT;
END;
/


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

Run Statspack Snapshot Every 15 Minutes Starting at the Next 15 Minute Interval

variable jobno number;
variable instno number;

BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/15)+1)*15)/(24*60), 'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/15)+1)*15)/(24*60)', TRUE, :instno);
COMMIT;
END;
/

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

Run Statspack Snapshot Every 30 Minutes Starting at the Next 30 Minute Interval

variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/30)+1)*30)/(24*60), 'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/30)+1)*30)/(24*60)', TRUE, :instno);
COMMIT;
END;
/


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

Run Statspack Snapshot Every 1 Hour

variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', TRUNC(sysdate+1/24,'HH'), 'TRUNC(SYSDATE+1/24,''HH'')', TRUE, :instno);
COMMIT;
END;
/

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

DBMS_JOB / Every 15 Minutes from Monday to Friday, Between 6 a.m. and 6 p.m.

SQL> ALTER SESSION SET nls_date_format = '(DY) MON DD, YYYY HH24:MI';
Session altered.

SQL> SELECT
sysdate
, CASE
WHEN ( TO_CHAR(SYSDATE, 'HH24') BETWEEN 6 AND 17
AND
TO_CHAR(SYSDATE, 'DY') NOT IN ('SAT','SUN')
)
THEN TRUNC(sysdate) +
(TRUNC(TO_CHAR(sysdate,'sssss')/900)+1)*15/24/60
WHEN (TO_CHAR(sysdate, 'DY') NOT IN ('FRI','SAT','SUN'))
THEN TRUNC(sysdate)+1+6/24
ELSE next_day(trunc(sysdate), 'Mon') + 6/24
END interval_date
FROM dual;