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

Oracle 10g: Concatenation Operator (||)

Careless mistake that causes severe injury...

MSSQL: +

EXAMPLE:
SELECT * FROM GBL_CIVILANT_MST WHERE ICNO LIKE '%' + SEARCHITEM_ + '%'

ORACLE:

EXAMPLE:
SELECT * FROM GBL_CIVILANT_MST WHERE ICNO LIKE '%' SEARCHITEM_ '%'

Oracle 10g: Concatenate(Concat)

SYNTAX:
CONCAT(char1, char2)
PURPOSE:
Returns char1 concatenated with char2. This function is equivalent to the concatenation operator ().
EXAMPLE:
This example uses nesting to concatenate three character strings:

SELECT CONCAT( CONCAT(ename, ' is a '), job) "Job" FROM emp WHERE empno = 7900

Job
-------------------------
JAMES is a CLERK

Oracle 10g: Backup & Restore (dump file)

BACKUP

RESTORE

---COMMAND---

Goto Start > Run > Type cmd :

BACKUP:
Command
c:\>exp user/pwd@database file= full=yes
Example
c:\>exp SA/sa@EPERHILI file=C:\ePerhilitanDBBAK\ePerhili13022007.dmp full=yes

RESTORE:
Command
c:\>imp user/pwd@database file= full=yes
Example
c:\>imp SA/sa@EPERHILI file=C:\ePerhili13022007.dmp full=yes

BACKUP(CERTAIN TABLES INCLUDED DATA):
Command
c:\>exp user/pwd@database file= tables=
Example
c:\>exp SA/sa@EPERHILI file=C:\WFLOW.dmp tables=(tblProcess,tblTask,tblModule)

RESTORE(CERTAIN TABLES INCLUDED DATA):
Command
c:\>imp user/pwd@database file= full=yes
Example
c:\>imp SA/sa@EPERHILI file=C:\WFLOW.dmp full=yes

Oracle 10g: Executing Stored Procedure(Get)

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
     v_cursor GetRefCodePckg.refcode_ref_cursor;
     v_typeid GBL_REFCODE.TYPEID%TYPE;
     v_parentid GBL_REFCODE.PARENTID%TYPE;
     v_typedescription GBL_REFCODE.TYPEDESCRIPTION%TYPE;
     v_typedetails GBL_REFCODE.TYPEDETAILS%TYPE;
     v_typestatus GBL_REFCODE.TYPESTATUS%TYPE;

BEGIN
     GetRefCodePckg.SP_GETREFCODE (P_ID => 1,
     DESC_REF => v_cursor);
     LOOP
     FETCH v_cursor
          INTO v_typeid, v_parentid, v_typedescription, v_typedetails, v_typestatus;
          EXIT WHEN v_cursor%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(v_typeid ' ' v_parentid ' ' v_typedescription ' ' v_typedetails ' ' v_typestatus);
     END LOOP;
     CLOSE v_cursor;
END;
/

Oracle 10g: Execute Stored Procedure(Add)

SET SERVEROUTPUT ON
DECLARE
ROW NUMBER;
BEGIN
TRNWORKFLOW_PCKG.SP_ADDTRNWORKFLOW
(55,1,1,1,1,SYSDATE,NULL,1,NULL,NULL,ROW);
DBMS_OUTPUT.PUT(ROW);
END;

Oracle 10g: Create New Package

CREATE OR REPLACE PACKAGE DB_PCKG IS
/*
*
* This package used for internal database procedures (logfile).
* Inclusive of all modules.
*
*
*
* @package DB_PCKG
* @author Nur Fiza Suhati
* @version 1.0
*
*/

PROCEDURE SP_ADDLOGFILE
(
LOGDATE_ IN INTEGER,
LOGACTION_ IN INTEGER,
TASKID_ IN INTEGER,
USERID_ IN INTEGER,
TABLENAME_ IN VARCHAR2,
BRANCHID_ IN INTEGER
);
END DB_PCKG;
/

CREATE OR REPLACE PACKAGE BODY DB_PCKG IS

PROCEDURE SP_ADDLOGFILE
(
LOGDATE_ IN INTEGER,
LOGACTION_ IN INTEGER,
TASKID_ IN INTEGER,
USERID_ IN INTEGER,
TABLENAME_ IN VARCHAR2,
BRANCHID_ IN INTEGER
)
AS
TABLEID INTEGER;
BEGIN
/*
Create auto running number
*/
SELECT MAX(LOGID) INTO TABLEID from wf_logfile where BRANCHID = BRANCHID_;

IF TABLEID IS NULL THEN
TABLEID := 0;
END IF;
TABLEID := TABLEID + 1;

/*
Insert statement
*/
INSERT INTO WF_LOGFILE(LOGID,LOGDATE,LOGACTION,TASKID,USERID,TABLENAME,BRANCHID)
VALUES(TABLEID,'',1,1,1,'WF_LOGFILE',1);
END;