Fun* with Oracle’s ROW_NUMBER()

(*Fun in a very loose tense of the word)

ROW_NUMBER returns a unique row number for each row of a query. It can be combined with an PARTITION BY and ORDER BY clause, so that you have control of the results.

For example, using the order by clause within a subquery we can return specific row numbers. Something we can’t do with the traditional ROWNUM value.

WITH myView AS
( SELECT myColumn,
ROW_NUMBER() OVER ( ORDER BY myColumn ) rn
FROM myTable )
SELECT myColumn
FROM myView mv
WHERE mv.rn BETWEEN 5 AND 10;

This query would have returned 5 rows much more effctly than using ROWNUM.

We can link ROW_NUMBER with SYS_CONNECT_BY_PATH to return row data in a single column.

WITH myView AS
( SELECT myColumn,
ROW_NUMBER() OVER ( ORDER BY myColumn ) rn
FROM myTable )
SELECT MAX(SUBSTR(SYS_CONNECT_BY_PATH(myColumn,','),2)) myRowData
FROM myView
START WITH rn = 1
CONNECT BY rn = PRIOR rn+1;

See here to see how adding the PARTITION BY clause gives further options if you want to convert row data into a column strings grouped by further data criteria.

Done.

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.

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.

Oracle: Convert CSV String into Rows

This nifty piece of code takes a comma separated string and converts it into rows.

I’m using sqlplus and a variable to simulate the data.

The data:

VAR csv VARCHAR2(100)
EXEC :csv := 'abc,de,fg,hij,klmn,o,pq,rst,uvw,xyz';

The query:

WITH data AS
( SELECT SUBSTR(csv, INSTR(csv,',',1,LEVEL)+1,
INSTR(csv,',',1,LEVEL+1) - INSTR(csv,',',1,LEVEL)-1 ) token
FROM ( SELECT ','||:csv||',' csv FROM SYS.DUAL )
CONNECT BY LEVEL < LENGTH(:csv)-LENGTH(REPLACE(:csv,',',''))+2 )
SELECT token
FROM data;

The result:

TOKEN
-----------------
abc
de
fg
hij
klmn
o
pq
rst
uvw
xyz

10 rows selected.

Just replace the inline query SELECT ','||:csv||',' csv FROM SYS.DUAL to whatever will return your csv string, (but keep the outer commas).

Split.