Oracle 10g: MONTHS_BETWEEN

/*
I had a problem in comparing 2 dates in one of my execution task.
It seems like I couldn't find DATEDIFF function in Oracle 10g reference.
so the most I could do is to use MONTHS_BETWEEN to calculate
the date difference between those 2 required dates.

Tricky part here is that..it only compares MONTHS.
If i have to compare for a range of 3 years (say for example),
then I have to times 12 -->[(3 * 12)]
*/

UPDATE wf_trnworkflow
SET ARCHIVEFLAG = 'NonActive'
WHERE (
MONTHS_BETWEEN(to_date(SYSDATE, 'DD-MON-YY HH:MI:SS'),to_date(CREATEDDATE, 'DD-MON-YY HH:MI:SS')) = (3 * 12)
OR
MONTHS_BETWEEN(to_date(SYSDATE, 'DD-MON-YY HH:MI:SS'),to_date(CREATEDDATE, 'DD-MON-YY HH:MI:SS')) > (3 * 12)
);

Oracle 10g: DBMS_SCHEDULER.DROP_JOB

/*
How to drop a scheduler job? Check this one out
*/

BEGIN
DBMS_SCHEDULER.DROP_JOB (job_name => 'test_job');
END;
/

Oracle 10g: View All Scheduler Jobs

---CODE----
select
job_name,
enabled
from
user_scheduler_jobs;

Oracle 10g: DBMS_Scheduler.create_job

/*
How to create a scheduler in Oracle Using PL/SQL.
job_name : A name that been given to a scheduler job
job_action: I have modified the job action so that
it will trigger a Stored Procedure.
Check out the nested code.
repeat_interval: can be changed to hourly, minutely
enabled : make sure to set it to true.
To stop scheduler, set it to false
*/


BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'test_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN GBL_ARCHIVE_PCKG.SP_UPDATETRNWORKFLOW; END;',
enabled => true,
start_date => systimestamp,
repeat_interval => 'freq=daily;byhour=23;byminute=0;bysecond=0');
END;
/

Oracle 10g: Create Trigger

CREATE OR REPLACE TRIGGER GBL_TEMP_JOINDATA_DTL

/*
*
*This trigger is used to automatically redirect one task to another.
*
*
*
* @trigger GBL_TEMP_JOINDATA_DTL
* @author Nur Fiza Suhati
* @version 1.0
*
*/


AFTER INSERT ON GBL_TEMP_JOINDATA_DTL
FOR EACH ROW
DECLARE
JOINID_ INTEGER;
BEGIN
IF inserting THEN

/*
:NEW --> this will actually pickup the new data for the table automatically.
*/
GBL_CIVILANT_PCKG.SP_ADDDTLJOINDATA(
JOINID_,
:NEW.JOINERTYPE,
:NEW.MEMBERID,
:NEW.MEMBERTYPE,
:NEW.OWNERID,
:NEW.OWNERTYPE,
:NEW.SHAREAMOUNT,
:NEW.BRANCHID,
:NEW.COMMITID,
:NEW.CREATEDBY
);
END IF;
END;
/

Oracle 10g: Create If-else within SQL Query

/*
How to create if-else when you have a comparison on a field in a certain query...? I Cut off all those if-else massive code and use 'case' in the sql statement...
*/

SELECT mstorganization.orgname, mstorganization.orgdocno,
mstorganization.orgshortform, mstorganization.website,
mstorganization.email,
CASE WHEN reforg.typedescription = '' THEN ' - '
ELSE reforg.typedescription
END AS "DOCTYPE", reforgtype.typedescription orgtypedesc,
mstorganization.ORGTYPE
FROM gbl_organization_mst mstorganization, gbl_refcode reforg,
gbl_refcode reforgtype
WHERE ( (mstorganization.orgid = ORGID_)
AND (mstorganization.branchid = BRANCHID_)
AND (mstorganization.orgdoctype = reforg.typeid(+))
AND (mstorganization.ORGTYPE = reforgtype.typeid(+))
);

Oracle 10g: Initcap

/*
How to create Initial capital letter for each selected field....
*/

SELECT org.orgid, org.orgname, refdoctype.typedescription doctype,org.orgdocno
FROM gbl_organization_mst org, gbl_refcode refdoctype
WHERE (
(Initcap(org.orgname) LIKE '%' || ORGNAME_ || '%')
AND (org.orgdoctype = refdoctype.typeid)
);

Oracle 10g: DBLINK

/*Create database link
testlink: dblink name
sa: schema
EPLOG: the other database's name
*/

CREATE DATABASE LINK testlink CONNECT TO sa IDENTIFIED BY sa USING 'EPLOG';

/*How to call
WF_LOGFILE: name of the table
testlink: name of the created dblink
*/
SELECT * FROM WF_LOGFILE@testlink;

Oracle 10g: Displaying All Connected Sessions

---Code---

ttitle "dbname Database|UNIX/Oracle Sessions";

set heading off;
select 'Sessions on database '||substr(name,1,8) from v$database;
set heading on;

select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
-- b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;

ttitle off;

spool off;

Oracle 10g: Database Data Source

Having trouble locating the database's Data Source?

Find it here:
C:\oracle\product\10.1.0\db_2\NETWORK\ADMIN\tnsnames.ora