Tuesday, 31 July 2012

Some useful links for creating cron expressions

If you ever have to create cron expressions (I do while working with the Quartz Scheduler) these links may be helpful to you.

Firstly a basic cron expression tutorial for quartz (what they are, the basics and some examples)

some more examples of working with the cron format

And finally a useful website which can generate basic cron expressions for you and can be used to check that cron expression you have written do what you think they do.

Hope this helps

Tuesday, 24 July 2012

Using Anchors in oracle apex

When creating an apex chart I put in a feature where the user could refresh the page after changing a select list value. This left me with a problem as the chart was at the bottom of the page and when the page reloaded  the focus was repositioned at the top meaning the user would have to scroll down. To fix this I used an anchor in the page and then navigated to the anchor in the branch of my page.
   I thought I'd blog this as I only saw how to do it as a throw away comment on a forum post and if like me you didn't know how to do this you may find this entry useful.

The technique is simple really create an anchor in the location I want the page to scroll to. In apex this would most likely be the top of a region so you can put it in the title of the region e.g.

<a name ="CHART" ></a>Hourly Report

CHART will be the anchor and Hourly Report the title of your region

Now if you have a branch that submits the page to refresh the graph then you can use the value of the anchor to navigate to that region.

To do this add #CHART (or # followed by your anchor value) to the "with these values" field under actions (branch to page in this application with the page set to the current page). Now that branch will take you to the anchor. this technique will work for anchors on other pages as well.

Note: if you look at the branch later it will redirect to a url like

Hope this helps

Wednesday, 11 July 2012

Finding Duplicate and similar records in Oracle Table

One of the things I have recently learnt is a way to find duplicates in Oracle tables. I have seen ways to do this but have not come across the method I am going to put in this blog so hopefully some of you will find it useful.

The technique is to use aliases rather than counts (which I have seen on other forums) to select from the table twice.

The basic query is

  SELECT t1.column_name,
FROM table t1, table t2
where t1.column_name = t2.column_name,
and t1.column_ID <> t2.column_ID

This means that we are checking if there exist records that share the column value we are testing (column_name) but have a different ID/primary key.

The real problem I faced however was finding records where part of the value was a duplicate. In this case I needed to identify values such as example_xxx and example_yyy with the yyy ending being an incorrect entry.

To do this I used the replace function so the query looks like this

  SELECT t1.column_name,  t2.column_name
FROM table t1, table t2
where replace(t1.column_name, '_xxx', '') = replace(t2.column_name,'_yyy',''),
and t1.column_ID <> t2.column_ID