Wednesday, December 6, 2017
Tuesday, December 5, 2017
Monday, December 4, 2017
Saturday, December 2, 2017
Sunday, November 26, 2017
Saturday, November 25, 2017
Tuesday, November 21, 2017
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.
Action:
- Wait a moment and try to connect a second time.
- Check which services are currently known by the listener by executing: lsnrctl services
- Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.
- If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.
- Check for an event in the listener.log file.
Reference: Oracle Documentation
Also some times ORA-12514 is related to the listener configuration, there are times when database is not registered with listener. lsnrctl status command can let you know if listener is serving the database service or not, otherwise you may need to configure the listerer again using Static or Dynamic Registration.
Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.
Action:
- Wait a moment and try to connect a second time.
- Check which services are currently known by the listener by executing: lsnrctl services
- Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.
- If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.
- Check for an event in the listener.log file.
Reference: Oracle Documentation
C:\Users\nimish.garg>sqlplus soctt/tiger@orcl
SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 12 14:15:01 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Resolving ORA-12514:
"ORA-12514: TNS:listener does not currently know of service requested in connect descriptor" is self explanatory. ORA-12514 is most likely related to tnsnames.ora where connect descriptor is specified incorrectly. So here our first step is to check tnsnames.ora for "orcl" connect descriptor to find what service name was actually requested.
Now we know that we are requesting "orcl.mydomain.co.in" service on "ngarg.mydomain.co.in" machine at 1521 port. Now we need to check what are the services listener at "ngarg.mydomain.co.in" knows
As we can see here listener at "ngarg.mydomain.co.in" knows about "myorcl.mydomain.co.in" service and we are looking for "orcl.mydomain.co.in", which is the reason we are facing ORA-12514. So we just need to correct our client's tnsnames.ora as
Now lets try to connect again.
Success !!!
"ORA-12514: TNS:listener does not currently know of service requested in connect descriptor" is self explanatory. ORA-12514 is most likely related to tnsnames.ora where connect descriptor is specified incorrectly. So here our first step is to check tnsnames.ora for "orcl" connect descriptor to find what service name was actually requested.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ngarg.mydomain.co.in)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.mydomain.co.in)
)
)
Now we know that we are requesting "orcl.mydomain.co.in" service on "ngarg.mydomain.co.in" machine at 1521 port. Now we need to check what are the services listener at "ngarg.mydomain.co.in" knows
-- on "ngarg.mydomain.co.in"
C:\>lsnrctl status
LSNRCTL for 32-bit Windows: Version 11.2.0.3.0 - Production on 12-APR-2014 14:23:16
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ngarg.mydomain.co.in)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.3.0 - Production
Start Date 07-APR-2014 20:05:09
Uptime 4 days 18 hr. 18 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File E:\oracle\app\nimish.garg\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
Listener Log File E:\oracle\app\nimish.garg\diag\tnslsnr\ngarg\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ngarg.mydomain.co.in)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 2 handler(s) for this service...
Service "myorcl.mydomain.co.in" has 1 instance(s).
Instance "myorcl", status READY, has 1 handler(s) for this service...
The command completed successfully
As we can see here listener at "ngarg.mydomain.co.in" knows about "myorcl.mydomain.co.in" service and we are looking for "orcl.mydomain.co.in", which is the reason we are facing ORA-12514. So we just need to correct our client's tnsnames.ora as
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ngarg.mydomain.co.in)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = myorcl.mydomain.co.in)
)
)
Now lets try to connect again.
C:\Users\nimish.garg>sqlplus scott/tiger@orcl
SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 12 14:28:45 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production
SQL>
Success !!!
Also some times ORA-12514 is related to the listener configuration, there are times when database is not registered with listener. lsnrctl status command can let you know if listener is serving the database service or not, otherwise you may need to configure the listerer again using Static or Dynamic Registration.
Sunday, November 19, 2017
Saturday, November 18, 2017
Friday, November 17, 2017
Thursday, November 16, 2017
Monday, November 13, 2017
Sunday, November 12, 2017
Thursday, November 9, 2017
Sunday, November 5, 2017
Saturday, November 4, 2017
Tuesday, October 31, 2017
Monday, October 30, 2017
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;
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;
Sunday, October 29, 2017
Wednesday, October 25, 2017
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;
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;
Saturday, October 21, 2017
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>
Friday, October 20, 2017
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 ;
-------------------------
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 ;
Saturday, October 14, 2017
Take the Challenge!
Take the Challenge!
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?- CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
RETURN DATE
IS
BEGIN
RETURN TRUNC (date_in);
END;
/ - CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
RETURN DATE
IS
BEGIN
RETURN TRUNC (date_in, 'MM');
END;
/ - CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)
RETURN DATE
IS
BEGIN
RETURN TRUNC (date_in, 'MONTH');
END;
/ - 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;
/
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?
- BEGIN
DBMS_OUTPUT.put_line (
TO_CHAR (
l_new_year - 24
, c_format));
END; - BEGIN
DBMS_OUTPUT.put_line (
TO_CHAR (l_new_year - 1
, c_format));
END; - BEGIN
DBMS_OUTPUT.put_line (
TO_CHAR (
l_new_year
- 24 * 60 * 60
, c_format));
END; - 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:
=========================================================================
You can use intrinsic Oracle functions to determine any date in the future:
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;
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;
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:-------------------
17.12.1980:00:00:00
20.02.1981:00:00:00
- SYSDATE + 1 is tomorrow
- SYSDATE - 7 is one week ago
- SYSDATE + (10/1440) is ten minutes from now.
SELECT '03.12.2004:10:34:24' "Now",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.
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
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 8752Check 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.
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"Formatting of the TIMESTAMP datatype with fractional seconds:
FROM date_table;
Date
-------------------
12/17/1980 00:00:00SELECT 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",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:
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.000000SELECT 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,Get the last date of a month:
LAST_DAY(SYSDATE) "Last",
LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL;
SYSDATE Last Days Left
--------- --------- ----------
03-DEC-04 31-DEC-04 28SELECT 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.2004Return the last Saturdays for the current year.SELECT TO_CHAR (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.
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
=========================================================================
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 |
-- 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;
Subscribe to:
Posts (Atom)
-
# Report Column (only column): 1. Column Formatting > HTML Expression <span style="display:block; width: 200px"> #...
-
Installing Oracle Forms and Reports 12c on Windows 10 64 Bit. Hardware used for this installation is · Intel i3-2370M CPU · ... -
when open forms builder then errors FRM-91129: fatal error: no value specified for required environment variable FORMS_BUILDER_CLASSPATH a...
-
---------------------------- | Keyboard Shortcut | ---------------------------- · Create: Breadcrumb Region Ctrl+/, C, B · ...