Oracle 10g: Show All Tables Command

...at times when we need to list out all the tables in our database..this command saves us a lot of time...

select owner, table_name, tablespace_name
from dba_tables
group by owner,table_name, tablespace_name

Oracle 10g: Change from datatype number to boolean

I was supposed to change a field's datatype from integer to boolean. It took me quite a while to disentangle the problem...so I figure out that table alteration (scripting) in oracle 10g is quite confusing compared to SQL 2000. I kept on receiving this type of error:

*** ORA-01451: column to be modified to NULL cannot be modified to NULL ***

There are multiple solutions being suggested through out the net....but I chose to keep it stupidly simple to solve it....

  

1. Column to be modified must be empty to change datatype...What I did was:

UPDATE [table_name]

SET [column_name] = NULL

UPDATE GBL_DOCUMENT_MST
SET ISPAYMENT = NULL

  

2. Now....I can alter the table successfully...

Alter table GBL_DOCUMENT_MST
modify
(
ISPAYMENT CHAR(1) CHECK (ISPAYMENT IN ( 'T', 'F' ))
);

  

3. Time to Execute!...

Oracle 10g: IN Operator

I always forgot on how to use the IN Operator....very useful yet so easy to be forgotten...


I had a task where I had to update the child's table(
gbl_mouprogress_dtl) STATUS-field automatically ONCE the master table (
gbl_mou_mst) STATUS-field being updated using the OWNERTYPE-field, OWNERID-field.

UPDATE GBL_MOUPROGRESS_DTL
SET STATUS = STATUS_,
LASTMODIFIEDBY = LASTMODIFIEDBY_,
LASTMODIFIEDDATE = SYSDATE
WHERE MOUPROGRESSID IN (
SELECT gbl_mou_mst.mouid
FROM gbl_mou_mst gbl_mou_mst
WHERE ( (gbl_mou_mst.status = WHERESTATUS_)
AND (gbl_mou_mst.branchid = BRANCHID_)
AND (gbl_mou_mst.ownertype = OWNERTYPE_)
AND (gbl_mou_mst.ownerid = OWNERID_)
)
);

MS Project 2007: Templates From Microsoft Project

No idea on how to start a project? Templates should be able to help you stop 'wandering'...:)

Make sure you have the Templates menu on the right sidebar...choose On Computer...

A dialog box will pop out....choose any template you reckon..e.g: Software Development...

.....the Output....

MS Project 2007: Create New Project

Double Click Image to Enlarge...

find Project > Project Information from the menu...

set your Start Date...then click 'OK'..


A new project has been created...

What if... we want to create a new project from the existing project ?

Click on New >

• Blank Project
• Totally new project
• Create new project from your current project file
• Copy file from any project and save as as new one

MS Project 2007: Today I learn MS Project...

Project Management? powwow...never been listed along my career path. I rather be a specialist of 'something' than becoming a project manager. But! We never knew the future...

Few weeks ago, we attended a 2-day-course learning Microsoft Project 2007. I shall be providing the notes that I took for everyone's reference. Sharing is caring... :)

Oracle 10g: Create Database

Thinking on how to create a new database? Follow these steps:

..choose

..then..

..then..

..then..

..finally..

Oracle 10g: Installation

I found my own reference for Oracle 10g installation that I made previously. Hope this will help everyone to install at your own. But first you have to download the software from http://www.oracle.com/ . Its Free! After finished downloading it, make sure you find a folder called install and find a file called SETUP.EXE inside the install folder. Double click the setup file. Choose Install Product and this will come out. [Double Click Image to Enlarge].

We can choose any type that we want. For starter, we select the Standard Edition.

Uncheck the ‘Create Starter Database’. It is better to create new database separately.

...then...

...then...

...then...

...and finally...Click Exit...

Oracle 10g: String Aggregation Technique 3

Another trick of doing data compression. Last time I did this was in the open source environment. Combining data using javascript and for loop to get the compressed data result. This technique is a little bit different, since I am mapping the DocumentNo with its Documenttype. Later then...the data will loop again to be binded to its Civilantid and produce only one row of data, instead of multiple rows.

How?

select civilantid,max(sys_connect_by_path(combinedoc, ' ')) documentno
from ( select civilantid,civdoc, documentno, (documenttype ', ' documentno) combinedoc,
row_number() over(partition by civilantid order by civdoc) rn
from gbl_civdoc_dtl where civilantid = 17)
start with rn = 1
connect by prior rn = rn-1 and prior civilantid = civilantid
group by civilantid
order by civilantid

To manipulate the given data, I did use the Split function in PHP...emm that was like 2 years ago..:)

Oracle 10g: String Aggregation Technique 2

Example if we have rows of data like the above picture and we want to compress it like the bottom picture. One way of doing it..by using string aggregation.

How?

select ownerid,max(sys_connect_by_path(phoneno1, ',')) phoneno1,max(sys_connect_by_path(faxno, ', ')) faxno
from ( select ownerid,faxno, phoneno1,
row_number() over(partition by ownerid order by ownerid) rn
from gbl_address_dtl where ownertype= 6008 and ownerid = 30)
start with rn = 1
connect by prior rn = rn-1 and prior ownerid = ownerid
group by ownerid
order by ownerid

Oracle 10g: Updating Child's Table's Status

In order to minimize code and data manipulation at the application server, I have suggested to the team that this will be controlled at the database server by using stored procedures and triggers to update the child's table status AUTOMATICALLY if the parent's table status is changed. Hopefully it will lighten up the network transacts...

Example:
Parent = GBL_ORGANIZATION_MST
Child = GBL_ADDRESS_DTL

1. Create Stored Proc: Focus on the bolded Item. I have other tables using this sp as well. I need to control the flow of the data here.

PROCEDURE SP_UPDATESTATUSDTLADDRESS(
ADDRESSID_ IN GBL_ADDRESS_DTL.ADDRESSID%TYPE,
OWNERTYPE_ IN GBL_ADDRESS_DTL.OWNERTYPE%TYPE,
OWNERID_ IN GBL_ADDRESS_DTL.OWNERID%TYPE,
BRANCHID_ IN GBL_ADDRESS_DTL.BRANCHID%TYPE,
STATUS_ IN GBL_ADDRESS_DTL.STATUS%TYPE,
WHERESTATUS_ IN CHAR,
LASTMODIFIEDBY_ IN GBL_ADDRESS_DTL.LASTMODIFIEDBY%TYPE
)
AS
BEGIN

IF ADDRESSID_ <> 0 THEN
UPDATE GBL_ADDRESS_DTL
SET STATUS = STATUS_,
LASTMODIFIEDBY = LASTMODIFIEDBY_,
LASTMODIFIEDDATE = SYSDATE
WHERE ADDRESSID=ADDRESSID_ and STATUS = WHERESTATUS_ and BRANCHID=BRANCHID_;

ELSIF ADDRESSID_ = 0 THEN
UPDATE GBL_ADDRESS_DTL
SET STATUS = STATUS_,
LASTMODIFIEDBY = LASTMODIFIEDBY_,
LASTMODIFIEDDATE = SYSDATE
WHERE OWNERTYPE=OWNERTYPE_ and OWNERID=OWNERID_ AND STATUS = WHERESTATUS_ and BRANCHID=BRANCHID_;
END IF;

END;


2. Create Trigger: Multiple triggers can be created in each table. Value of 6001 is actually the type of data that we have in our table.

CREATE OR REPLACE TRIGGER UPDATESTATUS_MSTORGANIZATION AFTER update OF STATUS ON GBL_ORGANIZATION_MST for each row
begin

IF :new.Status = 'A' then
GBL_Address_PCKG.SP_UPDATESTATUSDTLADDRESS( 0,6001,:new.ORGID,:new.BRANCHID,'A','D',:new.LASTMODIFIEDBY);
elsif :new.Status = 'D' then
GBL_Address_PCKG.SP_UPDATESTATUSDTLADDRESS( 0,6001,:new.ORGID,:new.BRANCHID,'D','A',:new.LASTMODIFIEDBY);
end if;
end;

3. Execute both of the script..and wallaahhh...its done!..

Thanks to En Hidayat for the guide..:)

Financial Tips: Home Downpayment

Put at least 20% down on a home. Making a down payment of less than 20% will usually result in a private mortgage insurance (PMI) fee being added. This is usually 0.5%, meaning it could cost you about $1,000 a year on a $200,000 principal.

Oracle 10g: String Aggregation Technique 1

say for example I want to make a string aggregation for this table...what should

1. Create a function:

CREATE OR REPLACE FUNCTION concatenate_list (p_cursor IN SYS_REFCURSOR)
RETURN VARCHAR2
IS
l_return VARCHAR2(32767);
l_temp VARCHAR2(32767);
BEGIN
LOOP
FETCH p_cursor
INTO l_temp;
EXIT WHEN p_cursor%NOTFOUND;
l_return := l_return ',' l_temp;
END LOOP;
RETURN LTRIM(l_return, ',');
END;
/

2. Call the created function in any needed statement:

COLUMN sppcommon,spplocal FORMAT A50

SELECT dtlspp.speciesid,
concatenate_list(CURSOR(SELECT dtlspp.description
FROM gbl_species_dtl dtlspp
where dtlspp.speciesid=17)) spplocal
FROM gbl_species_dtl dtlspp
where dtlspp.speciesid=17
GROUP BY dtlspp.speciesid

3: Output:

SUB SPESIS 1,SUB SPESIS 3,American Samoa

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

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;