Oracle 10g: Guideline Create Update SP

CREATE OR REPLACE PACKAGE [Package Name] IS

TYPE [cursor name] is ref cursor;
PROCEDURE [sp_updateprocedure]
(
[input parameter 1] IN INTEGER,
[input parameter 2] IN INTEGER,
[input parameter 3] IN INTEGER,
[input parameter 4] IN INTEGER
);

END [Package Name];
/


CREATE OR REPLACE PACKAGE BODY [Package Name] IS
PROCEDURE [sp_updateprocedure]
(
[input parameter 1] IN INTEGER,
[input parameter 2] IN INTEGER,
[input parameter 3] IN INTEGER,
[input parameter 4] IN INTEGER
)
AS
BEGIN
UPDATE [table name]
SET
[table name].[fieldname 1] = [input parameter 1],
[table name].[fieldname 2] = [input parameter 2]
WHERE [table name].[fieldname 2] = [input parameter 3];
END;


END [Package Name];
/

Oracle 10g: Guideline Create Get SP

CREATE OR REPLACE PACKAGE [Package Name] IS

TYPE [cursor name] is ref cursor;
PROCEDURE [sp_getprocedure]
(
[input parameter 1] IN INTEGER,
[input parameter 2] IN VARCHAR2(1000),
[output cursor parameter] OUT [cursor name]
);
END [Package Name];
/


CREATE OR REPLACE PACKAGE BODY [Package Name] IS
PROCEDURE [sp_getprocedure]
(
[input parameter 1] IN INTEGER,
[input parameter 2] IN VARCHAR2(1000),
[output cursor parameter] OUT [cursor name]
)

AS
[var 1] INTEGER;
[var 2] VARCHAR2(100);

BEGIN

OPEN [output cursor parameter] FOR


SELECT [fieldname 1],[fieldname 2],
[fieldname 3],[fieldname 4],
[fieldname 5]
FROM [table name 1],[table name 2]
WHERE (
([table name 1].[fieldname 1] =
[input parameter 1]);
);
END;


END [Package Name];
/

Oracle 10g: Guideline Create Add SP

CREATE OR REPLACE PACKAGE [Package Name] IS
PROCEDURE [sp_addprocedure]
(
[output parameter] OUT [data type],
[input parameter 1] IN [table name].[fieldname]%TYPE,
[input parameter 2] IN [table name].[fieldname]%TYPE
);
END [Package Name];
/

CREATE OR REPLACE PACKAGE BODY [Package Name] IS
PROCEDURE [sp_addprocedure]
(
[output parameter] OUT [data type],
[input parameter 1] IN [table name].[fieldname]%TYPE,
[input parameter 2] IN [table name].[fieldname]%TYPE
)
AS
TABLEID INTEGER;
BEGIN
SELECT MAX([output parameter])
INTO TABLEID
from [table name]
where BRANCHID = [input parameter 5];

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

TABLEID := TABLEID + 1;

INSERT INTO [table name]
(
[fieldname 1],[fieldname 2]
)
values
(
TABLEID,[input parameter 1],[input parameter 2]

)RETURNING [fieldname 1] INTO [input parameter 1];
END;
END [Package Name];
/

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)
);