Thursday 12 August 2010

Oracle SQL Query to Show Oldest Lock per User

This simple query on dictionary views v$locks and v$session, shows who has a lock and how old it is.
The query formats the v$lock.ctime column from seconds to hours minutes and seconds.

COLUMN "Hr:Min:Sec" FORMAT A12

WITH data AS
( SELECT s.username,
MAX(l.ctime) lockSecs
FROM v$session s,
v$lock l
WHERE s.sid = l.sid
GROUP BY s.username )
SELECT username,
LPAD(TRUNC(lockSecs/3600),6)||':'
||LPAD(MOD(TRUNC(lockSecs/60),60),2,'0')||':'
||LPAD(MOD(lockSecs,60),2,'0') "Hr:Min:Sec"
FROM data
ORDER BY lockSecs DESC
;


Done.

Tuesday 3 August 2010

Oracle Function to Convert Numbers to Words

This is a basic function to covert integers to words. Pass the number into the function and it will return it spelt out, in your own language.

OK there are limitations to this, only positive integers less than 5373485, but if this fits your requirements then here you go. Should it hit a limitation it just returns the number back.

The Function:

FUNCTION simpleIntegerToWords
( p_number IN NUMBER ) RETURN VARCHAR2 IS
BEGIN
RETURN(TO_CHAR(TO_DATE(p_number,'j'),'jsp'));
EXCEPTION
WHEN OTHERS THEN
RETURN(p_number); --// For numbers > 5373484
END;

Run:

BEGIN

DBMS_OUTPUT.PUT_LINE(simpleIntegerToWords(1560));
DBMS_OUTPUT.PUT_LINE(simpleIntegerToWords(5373484));
DBMS_OUTPUT.PUT_LINE(simpleIntegerToWords(5373485));
DBMS_OUTPUT.PUT_LINE(simpleIntegerToWords(53.73));
DBMS_OUTPUT.PUT_LINE(simpleIntegerToWords(-53));
DBMS_OUTPUT.PUT_LINE(simpleIntegerToWords(2007));

END;
/


The result:

one thousand five hundred sixty
five million three hundred seventy-three thousand four hundred eighty-four
5373485
53.73
-53
two thousand seven

Spelt.