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.

Deleting Duplicate Rows in Oracle

A quick guide to deleting duplicate rows in Oracle.

Test Setup:

CREATE TABLE dupTest (
col1 VARCHAR2(30),
col2 VARCHAR2(30)
);
/

INSERT INTO dupTest VALUES ('A','Ant');
INSERT INTO dupTest VALUES ('A','Ant');
INSERT INTO dupTest VALUES ('B','Bear');

SELECT * FROM dupTest;


COL1 COL2
------ ----
A Ant
A Ant
B Bear

To delete the duplicate rows, check that the rowid is in a select of the max rowids from the table where the count is greater than 1.

DELETE FROM dupTest
WHERE ROWID IN ( SELECT MAX(ROWID)
FROM dupTest
GROUP BY col1, col2
HAVING COUNT(*) > 1 );

SELECT * FROM dupTest;


COL1 COL2
------ ----
A Ant
B Bear

The duplicate row has been deleted.

And finally cleanup:

DROP TABLE dupTest;

De-duped

Console,Text and XML Logger Output with java.util.logging.Logger

Enhancing the previous blog "Getting Started with java.util.logging.Logger", this example looks at the next step, to output different message levels to the console than to file and shows how to output text or xml.

The logging levels are:

Level	Description
SEVERE	The highest value; intended for extremely important messages (e.g. fatal program errors).
WARNING	Intended for warning messages.
INFO	Informational runtime messages.
CONFIG	Informational messages about configuration settings/setup.
FINE	Used for greater detail, when debugging/diagnosing problems.
FINER	Even greater detail.
FINEST	The lowest value; greatest detail.

So an example would look something like this:

ackage com.wownow;

import java.io.IOException;
import java.util.logging.ConsoleHandler;
import java.util.logging.FileHandler;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.logging.SimpleFormatter;
import java.util.logging.XMLFormatter;

public class MyLoggingTest2 {

public static void main(String[] args) throws IOException {

Level consoleLogLevel = Level.CONFIG;
Level defaultLogLevel = Level.FINER;

ConsoleHandler ch = new ConsoleHandler();
ch.setLevel(consoleLogLevel);

FileHandler txtfh = new FileHandler("MyLogFile.txt");
txtfh.setFormatter(new SimpleFormatter());
txtfh.setLevel(defaultLogLevel);

FileHandler xmlfh = new FileHandler("MyLogFile.xml");
xmlfh.setFormatter(new XMLFormatter());
xmlfh.setLevel(defaultLogLevel);

Logger logger = Logger.getLogger("MyLogger");
logger.setUseParentHandlers(false);
logger.setLevel(defaultLogLevel);

logger.addHandler(ch);
logger.addHandler(txtfh);
logger.addHandler(xmlfh);

logger.severe("My Severe Message");
logger.warning("My Warning Message");
logger.info("My Info Message");
logger.config("My Config Message");
logger.fine("My Fine Message");
logger.finer("My Finer Message");
logger.finest("My Finest Message");

txtfh.close();
xmlfh.close();

}

}

In this example we:

• Create two level variables,one for the console and one for the files


• Then create a console handler, and set the level of this handler, in the case to "CONFIG"


• We then create a handler for the text file, set its formatter to SimpleFormatter, so the output is in plain text, then set its level


• Then we create a third handler for the xml and set its formater to XMLFormatter (this could be ommitted as it's the default), and again set its level


• The logger object is then created, we use setUseParentHandlers to false to stop duplicated messages appearing on the console


• The logger level is set, this assumes the defaultLogLevel is lower than the consoleLogLevel, otherwise you could use Level.ALL


• Next the three handlers are added to the logger


• Next we output a message for each logging level. Only the messages above the level "CONFIG" will be output to the console or "FINER" written to the file.


• We then close the file handlers.

My Done Message

Getting Started with java.util.logging.Logger

The basic method of logging would be to open a file and write your data to it. Not with Java! The Logger has some niffty features and is even easier than writing to a file.

Loggers main offering is a level that can be set, anything at or above that level gets output, anything below gets silently ignored. So you can keep your debug messages in your code and only turn them on when needed.

The logging levels are:

Level	Description
SEVERE	The highest value; intended for extremely important messages (e.g. fatal program errors).
WARNING	Intended for warning messages.
INFO	Informational runtime messages.
CONFIG	Informational messages about configuration settings/setup.
FINE	Used for greater detail, when debugging/diagnosing problems.
FINER	Even greater detail.
FINEST	The lowest value; greatest detail.

A quick start example would look something like this:

package com.wownow;

import java.io.IOException;
import java.util.logging.FileHandler;
import java.util.logging.Level;
import java.util.logging.Logger;

public class MyLoggingTest {

public static void main(String[] args) throws IOException {

Level logLevel = Level.FINE;

FileHandler fh = new FileHandler("MyLogFile.xml");

Logger logger = Logger.getLogger("MyLogger");
logger.setLevel(logLevel);
logger.addHandler(fh);

logger.severe("My Severe Message");
logger.warning("My Warning Message");
logger.info("My Info Message");
logger.config("My Config Message");
logger.fine("My Fine Message");
logger.finer("My Finer Message");
logger.finest("My Finest Message");

fh.close();

}

}

In this example we:

• first set the logLevel to "FINE",


• then create our log file "MyLogFile.xml", by default Logger outputs to file in xml format.


• We then create the logger object, set the level and add the file handler.


• Next we output a message for each logging level. Only the messages above the set level, "FINE", will be output. The logger also outputs to console but only to "INFO" level by default.


• We then close the file handler.

The xml output looks something like:

<?xml version="1.0" encoding="windows-1252" standalone="no"?>
<!DOCTYPE log SYSTEM "logger.dtd">
<log>
<record>
<date>2009-06-09T12:23:56</date>
<millis>1244546636747</millis>
<sequence>0</sequence>
<logger>MyLogger</logger>
<level>SEVERE</level>
<class>com.rrf.mypackage.MyLoggingTest</class>
<method>main</method>
<thread>10</thread>
<message>My Severe Message</message>
</record>
<record>
<date>2009-06-09T12:23:56</date>
<millis>1244546636763</millis>
<sequence>1</sequence>
<logger>MyLogger</logger>
<level>WARNING</level>
<class>com.rrf.mypackage.MyLoggingTest</class>
<method>main</method>
<thread>10</thread>
<message>My Warning Message</message>
</record>
...
</log>

There are also two special levels Level.ALL and Level.OFF

See Console,Text and XML Logger Output to see how to output different levels and formats to different destinations.

There is much more to Logger, see here.

Logged.