Monday 15 June 2009

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

Wednesday 10 June 2009

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:











LevelDescription
SEVEREThe highest value; intended for extremely important messages (e.g. fatal program errors).
WARNINGIntended for warning messages.
INFOInformational runtime messages.
CONFIGInformational messages about configuration settings/setup.
FINEUsed for greater detail, when debugging/diagnosing problems.
FINEREven greater detail.
FINESTThe 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

Tuesday 9 June 2009

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:











LevelDescription
SEVEREThe highest value; intended for extremely important messages (e.g. fatal program errors).
WARNINGIntended for warning messages.
INFOInformational runtime messages.
CONFIGInformational messages about configuration settings/setup.
FINEUsed for greater detail, when debugging/diagnosing problems.
FINEREven greater detail.
FINESTThe 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.

Thursday 7 May 2009

Javascript to Change Message Based on the Time of Day

This Javascript function checks the time of day and returns Good Morning, if it's before midday, Good Afternoon, if it's between midday and 6pm and Good Evening if it's after 6pm.

function welcomeMessage()
{
var now = new Date();
var hours = now.getHours();
var msg;
if(hours<12)
msg = "Good Morning";
else if(hours<18)
msg = "Good Afternoon";
else
msg = "Good Evening";
return(msg);
}

As the date is taken from the client, so it doesn't matter where in the World the user is, they will get the message relevant to them.


Goodbye and

Tuesday 5 May 2009

PHP to Change Background Based on the Day of the Year

This PHP will change the background image based on the day of the year.

It expects there to be 366 images to change to called 001.jpg, 002.jpg ... 366.jpg.

<html>

<body style="background-image: url(<?php echo str_pad(date(z),3,'0'); ?>.jpg)">

</body>

</html>

To change the background using Javascript see here

Changed....again

Thursday 30 April 2009

Javascript to Change Background Based on the Day of the Year

This Javascript will change the background image based on the day of the year.

It expects there to be 366 images to change to called 001.jpg, 002.jpg ... 366.jpg.

<html>

<body>

<h1>Day of Year Background</h1>

<script type="text/javascript">

var firstJan = Math.floor((new Date().setFullYear(new Date().getFullYear(),0,1))/86400000);
var today = Math.ceil((new Date().getTime())/86400000);
var dayOfYear = today-firstJan;

var bgdImage;
if((dayOfYear+'').length == 1)
bgdImage = '00'+dayOfYear+'.jpg';
else if((dayOfYear+'').length == 2)
bgdImage = '0'+dayOfYear+'.jpg';
else
bgdImage = dayOfYear+'.jpg';

document.body.style.backgroundImage=("url(\""+bgdImage+"\")");

</script>

</body>

</html>

For articles on changing backgrounds on a daily basis or based on the season, See Daily Background Image Change or Seasonal Background Image Change here.

To change the background using Javascript see here

Changed

Wednesday 29 April 2009

Javascript Function to Return the Day of the Year

This simple Javascript function returns the day of the year.

<html>

<head>

<script type="text/javascript">

function dayOfYear()
{
var firstJan = Math.floor((new Date().setFullYear(new Date().getFullYear(),0,1))/86400000);
var today = Math.ceil((new Date().getTime())/86400000);
return(today-firstJan);
}

</script>

</head>

<body>

<h1>Day of Year</h1>

Today is day <script type="text/javascript">document.write(dayOfYear());</script> of the year.

</body>

</html>

Today is day of the year.

Wednesday 22 April 2009

Simple Java XML Tag Value Extractor

This Java method is a simple routine to extract values from a simple xml message. It is not a replacement for XPath, which is much more fundamental.

The requirement for this method arose when I had to extract a couple of tags from small xml files, that weren't actually xml! As XPath is pretty strict it couldn't be used as the files wouldn't compile successfully.

So we have "simpleGetTagValue", which accepts the xml and the tag as Strings.

private static String simpleGetTagValue(String xml, String tag) {
String tagValue = new String();
StringBuilder startTag = new StringBuilder("<").append(tag).append(">");
StringBuilder endTag = new StringBuilder("");
int startIndex = xml.indexOf(startTag.toString());
int endIndex = xml.indexOf(endTag.toString());
if(startIndex!=-1 && endIndex !=-1)
tagValue = xml.substring(startIndex+(startTag.length()), endIndex);
return tagValue;
}


Done.

Tuesday 21 April 2009

Java Method to Read Text File Contents Into a String

This Java method takes a File as a parameter and returns the text contents of that file as a String.

private static String readFileToString(File f) throws IOException {
StringBuffer sb = new StringBuffer(1000);
BufferedReader br = new BufferedReader(new FileReader(f));
char[] buf = new char[1024];
int readSoFar = 0;
while((readSoFar = br.read(buf)) != -1) {
sb.append(String.valueOf(buf, 0, readSoFar));
buf = new char[1024];
}
br.close();
return sb.toString();
}

It can be modified to take the qualified file name as a String

private static String readFileToString(String f) throws IOException {
...

Done.

Tuesday 14 April 2009

JSP Substring (and other string manipulations)

I only have a little knowledge of jsp, so my first stab at trying to modify a page I thought I'd try just using some Java to see happened.

The modification was just to substring a variable that already existed, so in went the Java and out came the error.

The function substring must be used with a prefix when a default namespace is not specified

What? So I did some searches for this error and eventually found that using Java was very wide of the mark.

You need to use the jstl tag library for string manipulation.

So after adding the taglib to the top of the page, my substring that trims the first five characters and the last character looks something like:

<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
...
<c:set var="myValue" value="${fn:substring(myValue,5,fn:length(myValue)-1)}" />
...

A summary of all the functions in the jstl library, including: contains, endsWith, escapeXml, indexOf, join, length, replace, split, trim, can be found at JSTL functions.


${fn:replace("Done","one","umb")}

Quick Find for Browser User Agent

To quickly find a browsers user agent enter the following line into the address bar of the browser.

javascript:document.write(navigator.userAgent)

This browser user agent is:



Done.

Thursday 2 April 2009

Position an Element Horizontally and Vertically Using CSS. (Take 2)

Positioning an element so that it is centred on the page, both horizontally and verticalally, is very simple to achieve. We do need to know the width of the element, so this does need to be fixed.

It has been tested in IE7, Firefox3 and Safari3 and all work fine. (It may work in other browsers I just haven't tested it)
This idea expands our first idea as this only worked in quirks mode. This second solution uses the xhtml transitional doctype.

The idea is quite straight forward, we position the element so the top left corner is in the middle of the page. Then we adjust the top and left margins so the centre of the element is in the middle of the page.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<style>

* {
margin: 0;
padding: 0;
}

#layoutwrapper {
height: 200px; /* Adjust as appropriate */
width: 550px; /* Adjust as appropriate */
margin-left: -275px; /* (Width/2)*-1 */
margin-top: -100px; /* (Height/2)*-1 */
left: 50%;
top: 50%;
overflow: hidden;
position: absolute;
background: yellow;
}

</style>
</head>
<body>

<div id="layoutwrapper">
Enter content here
</div>

</body>
</html>

...Centred with doctype...

Friday 20 March 2009

Convert a String to an ASCII / Unicode String with JavaScript

This JavaScript function converts a string of characters to a corresponding string of each characters unicode value.

function stringToAscii(s)
{
var ascii="";
if(s.length>0)
for(i=0; i<s.length; i++)
{
var c = ""+s.charCodeAt(i);
while(c.length < 3)
c = "0"+c;
ascii += c;
}
return(ascii);
}

If a characters unicode value is less than one hundred, zeros are prefixed to for that character, so that each original character has three digits in the returned unicode string.

And a test page

<html>
<head>
<script type="text/javascript">

function convertMe()
{
var converted = stringToAscii(document.getElementById('string').value);
document.getElementById('answer').innerHTML = converted;
}

function stringToAscii(s)
{
var ascii="";
if(s.length>0)
for(i=0; i<s.length; i++)
{
var c = ""+s.charCodeAt(i);
while(c.length < 3)
c = "0"+c;
ascii += c;
}
return(ascii);
}

</script>
</head>
<body>

<h1>String To ASCII/Unicode</h1>
<input id="string" type="text"/>
<input type="button" value="Convert" onclick="convertMe();"/>
<h1 id="answer"></h1>

</body>
</html>

068111110101.

Wednesday 11 March 2009

Javascript Function to Check Email Address Format using RegExp

This Javascript function performs a basic test to validate an email address.

The only true way of validating an email is to send an email and ask for a reply of some kind. This function is an initial step by making sure the format is correct.

function isEmail(e)
{
var emailPattern = new RegExp(/^\s*[\w\-\+_]+(\.[\w\-\+_]+)*\@[\w\-\+_]+\.[\w\-\+_]+(\.[\w\-\+_]+)*\s*$/);
return(emailPattern.test(e));
}

And an example html page.

<html>
<head>
<script>

function testEmail(e)
{
if(isEmail(e))
alert('true');
else
alert('false');
}

function isEmail(e)
{
var emailPattern = new RegExp(/^\s*[\w\-\+_]+(\.[\w\-\+_]+)*\@[\w\-\+_]+\.[\w\-\+_]+(\.[\w\-\+_]+)*\s*$/);
return(emailPattern.test(e));
}


</script>
</head>

<body>
<h1>Email RegExp Test</h1>
<input id="email" type="text"/>
<input type="button" value="Test" onclick="testEmail(document.getElementById('email').value);"/>
</body>

</html>

Validated.

Wednesday 25 February 2009

How to find the exact Oracle Database and Install Component Version Numbers

When you log on to an Oracle Database with SQL*Plus the version number is normally displayed for you. However what about the other components or what if I'm using another tool?

The following query gives the version numbers of the database and the installed components.

SELECT 'Version' source,
banner
FROM sys.v_$version
UNION ALL
SELECT 'Registry',
banner
FROM sys.all_registry_banners

Done...

Friday 20 February 2009

How to find the exact version of an Oracle Application Server installation

It's a pain to find out the exact version of Oracle Application Server if you don't know where to look (10GR2 but most versions are the same). The homepage will tell you the first three numbers but that's not always accurate enough.

The exact version including applied patches can be found at $home/config/ias.properties, look for the version name value pair.

Annoying easy.

Wednesday 28 January 2009

Position a DIV Horizontally and Vertically Using CSS.

Currently missing from CSS is the ability to position an element so that it is centred on the page, both horizontally and verticalally. But fear not, this effect is very simple to achieve.

This has been tested in IE7, Firefox3 and Safari3 and all work fine. (It may work in other browsers I just haven't tested it)

The idea is quite straight forward:

  • We build a standard horizontal layout, using text-align:center (for IE) and a wrapper div with margin:0 auto.

  • Then we add a div above the wrapper with height:50% and an top negative offset position of half the height of the wrapper div.


The wrapper will then be centred horizontally and vertically.

<html>
<head>
<style>

html, body {
margin: 0;
padding: 0;
text-align: center;
}

#layoutspacer {
position: relative;
margin-top: -100px; /* Half height of layoutwrapper */
height: 50%;
width: 100%;
}

#layoutwrapper {
background: yellow;
position: relative;
margin: 0 auto;
height: 200px; /* Adjust as appropriate */
width: 200px; /* Adjust as appropriate */
overflow: hidden;
}

</style>
</head>
<body>

<div id="layoutspacer"></div>
<div id="layoutwrapper">
Enter content here
</div>

</body>
</html>

...Centred...

Update: This solution only works in quirks mode, for a better solution see here

Tuesday 20 January 2009

T-SQL Function to Calculate Working Days Between Given Dates

This T-SQL function for SqlServer 2008 calculates the number of working days between two given dates.

IF OBJECT_ID('dbo.fnGetWorkingDays') IS NOT NULL
DROP FUNCTION dbo.fnGetWorkingDays;
GO

CREATE FUNCTION dbo.fnGetWorkingDays
( @pStartDate DATE,
@pEndDate DATE,
@pWorkingDays VARCHAR(7) = 'YYYYYNN' )
RETURNS INT
AS
BEGIN

DECLARE
@cMonday INT = DATEPART(dw,CONVERT(DATE,'29 OCT 2001',103)),
@vCheckDate DATE = @pStartDate,
@vDay INT,
@vDuration INT,
@vNoOfWeeks INT

IF @pStartDate IS NOT NULL AND @pEndDate IS NOT NULL
BEGIN

SET @vDuration = 0

WHILE @vCheckDate <= @pEndDate
BEGIN
SET @vDay = CAST(REPLACE((DATEPART(dw,@vCheckDate)+8-@cMonday)%7,'0','7') AS INT);
IF DATEDIFF(day,@vCheckDate,@pEndDate) < 7 OR @vDay != 1
BEGIN
IF SUBSTRING(@pWorkingDays,@vDay,1) = 'Y'
SET @vDuration = @vDuration + 1;
SET @vCheckDate = DATEADD(day,1,@vCheckDate);
END
ELSE
BEGIN
SET @vNoOfWeeks = FLOOR(DATEDIFF(day,@vCheckDate,@pEndDate)/7);
SET @vCheckDate = DATEADD(day,@vNoOfWeeks*7,@vCheckDate);
SET @vDuration = @vDuration+(@vNoOfWeeks*LEN(REPLACE(@pWorkingDays,'N','')));
END
END

END;

RETURN(@vDuration);

END
GO

To query the function for working days between 10th October 2009 and 11th November 2009

SELECT dbo.fnGetWorkingDays('10/10/2009','11/11/2009',DEFAULT) 'Working Days'
=
Working Days
23

This figure is for the default working days, Monday to Friday. This can be changed by providing a pWorkingDays parameter. The parameter contains seven characters of either Y or N. The first character is for Monday, second for Tuesday and so on. A Y represents it is a working day, an N represents it is a non-working day.

For those lucky enough not to work on a Wednesday, the same query would look like.

SELECT dbo.fnGetWorkingDays('10/10/2009','11/11/2009','YYNYYNN')
=
Working Days
18

Done

Thursday 15 January 2009

T-SQL Alternative to Oracle's REPLACE Stored Procedure Option

When working with Oracle (11g) stored procedures it's easy to add the "OR REPLACE" option so you can keep making modifications and re-creating the code.

However no such direct alternative exists in SqlServer T-SQL (2008). So when running scripts you need to add a check before creating your code to drop it if it exists.

So, a (very basic) Oracle PLSQL stored procedure, using the OR REPLACE option, may look like

CREATE OR REPLACE PROCEDURE spTest AS
BEGIN
NULL;
END;
/

the T-SQL script alternative needs to look like

IF OBJECT_ID('dbo.spTest') IS NOT NULL
DROP PROCEDURE dbo.spTest;
GO

CREATE PROCEDURE dbo.spTest AS
BEGIN
RETURN;
END
GO

Replaced.

Wednesday 7 January 2009

Display Child Rows as a Column With T-SQL (SQLServer)

It is often required to display data as a single column, especially data from child tables. This example looks at displaying a comma seperated list of data from a child table as a column in each row.

Senerio: Two tables, dept and emp, which stores departments and employees respectively. We want to display a comma seperated list of employee names as a column on the department record.

CREATE TABLE dept
( deptNo INT,
deptName VARCHAR(30) );

CREATE TABLE emp
( empNo INT,
deptNo INT,
empName VARCHAR(30) );

INSERT INTO dept VALUES (1,'Accounts');
INSERT INTO dept VALUES (2,'Sales');
INSERT INTO dept VALUES (3,'Operations');

INSERT INTO emp VALUES (1,1,'Frank');
INSERT INTO emp VALUES (2,1,'Jane');
INSERT INTO emp VALUES (3,2,'Joe');
INSERT INTO emp VALUES (4,2,'Harry');
INSERT INTO emp VALUES (5,2,'Sarah');
INSERT INTO emp VALUES (6,3,'Nate');
INSERT INTO emp VALUES (7,3,'Buddy');

To add the emp data as a column we include a select statement as a column. To make the select statement return one row, rather than each record as a new row, we use the "FOR XML PATH" clause.

SELECT d.deptNo,
d.deptName,
( SELECT (CASE WHEN ROW_NUMBER()OVER(ORDER BY e.empName)=1 THEN '' ELSE ',' END)+e.empName
FROM emp e
WHERE e.deptNo = d.deptNo
ORDER BY e.empName
FOR XML PATH('') ) employees
FROM dept d;

deptNo deptName employees
------ -------- ---------
1 Accounts Frank,Jane
2 Sales Harry,Joe,Sarah
3 Operations Buddy,Nate

The CASE clause checks the ROW_NUMBER value, to avoid a leading, (or trailing) comma.

Done

See Also: Oracle SQL equivilent, to display row data as a column