CONCAT in Oracle

SELECT CONCAT('Name : ', Upper(a.NAME))
FROM BIODATA a
WHERE a.MATRIK = :matrik;

/*Result Sample :-
Name : HARIMADA SABALKUNAN
*/

ISNUMBER in Oracle

Until Oracle 10g doesnt have ISNUMBER function , this is another way to check either the data is a number or not...(until this written, oracle version used is 10g)

SELECT length(translate(trim(column_name),' +-.0123456789',' ')) FROM DUAL;

Will return you NULL if it is a number, Greater than zero if not number
(Actually returns the count of non numeric characters)

In Real Case will compute as :
SELECT A.KODMP,
   B.SETMP,
   decode(length(translate(substr(B.PENSY, 3, 8), ' +-.0123456789', ' ')), null, substr(B.PENSY, 3, 8), B.PENSY) as IS_STAFNO,
   C.0NMSKGR,
   B.SMGRED,
   DECODE((SELECT D.NAMA FROM FASAS D WHERE D.STAFNO = B.PENSY), NULL, 'Tiada',(SELECT D.NAMA FROM FASAS D WHERE D.STAFNO = B.PENSY )) AS NAMA
FROM FMAKK A, FKURPSY B, 0STSMSKGR C
WHERE B.KODMP = A.KODMP
AND B.SMGRED = C.0KMSKGR
AND A.KODFAK like :kodfak
AND B.SESI = :sesi
AND B.SEMESTER = :semester;