Over the past few days I’ve had a number of emails and comments based on my previous post. My previous post was called ‘Adding numbers between two values’. I included some PL/SQL code that can be used to add up the numbers between two values. I mentioned that this was a question that my pre-teen son (a few year pre-teen) had asked me.
There are two main solutions to the same problem. One involves just using a SELECT and the other involves using recursion. I will come back the these alternative solutions below.
But let me start off with a bit more detail and background to why I approached the problem the way that I did. The main reason is that my son is a pre-teen. Over the past couple of years he as expressed an interest in what his daddy does. We even have matching ORACLENERD t-shirts
When I was working through the problem with my son I wanted to show him how to take a problem and by breaking it down into its different parts we can work out an overall solution. We can then take each of these parts and translate them into code. In this case some PL/SQL, yes it is a bit nerdy and we do have the t-shirt. The code that I gave illustrates many different parts of the language and hopefully he will use some of these features as we continue on our learning experience.
It is good sometimes to break a problem down into smaller parts. That way we can understand it better, what works and what does not work, if something does not work then we will know what bit and also leads to easier maintenance. At a later point as you develop an in-depth knowledge of certain features of a language you can then rewrite what you have to be more efficient.
All part of the learning experience.
Ok lets take a look at the other ways to answer this problem. The first approach is to just use a single SELECT statement.
SELECT sum(rownum + &&Start_Number – 1)
CONNECT by level <= &End_Number – &&Start_Number + 1;
An even simpler way is
CONNECT BY level between &Start_Number and &End_Number;
These queries create a hierarchical query that produce all the numbers between the Start_Number parameter and the End_Number parameter. The SUM is needed to all all the numbers/rows produced. This is nice and simple (but not that easy for by son at this point).
Thank you to everyone who contacted me about this. I really appreciated your feedback and please keep your comments coming for all my posts.
November (2012) is going to be a busy month for Oracle users in Ireland. There is a mixture of Oracle User Group events, with Oracle Day and the OTN Developer Days. To round off the year we have the UKOUG Conference during the first week in December.
Here are the dates and web links for each event.
Oracle User Group
The BI & EPM SIG will be having their next meeting on the Tuesday 20th November. This is almost a full day event, with presentations from End Users, Partners and Oracle product management. The main focus of the day will be on EPM, but will also be of interest to BI people.
As with all SIG meetings, this SIG will be held in the Oracle office in East Point (Block H). Things kick off at 9am and are due to finish around 4pm with plenty of tea/coffee and a free lunch too.
Remember to follow OUG Ireland on twitter using #oug_ire
Oracle will be having their Oracle Day 2012, on Thursday 15th, in Croke Park. Here is some of the blurb about the event, “…to learn how Oracle simplifies IT, whether it’s by engineering hardware and software to work together or making new technologies work for the modern enterprise. Sessions and keynotes feature an elite roster of Oracle solutions experts, partners and business associates, as well as fascinating user case studies and live demos.”
This is a full day event from 9am to 5pm with 3 parallel streams focusing on Big Data, Enterprise Applications and the Cloud.
OTN Developer Days
Oracle run their developer days about 3 times a year in Dublin. These events are run like a Hands-on Lab. So most of the work during the day is by yourself. You are provided with a workbook, a laptop and a virtual machine configured for the hands-on lab. This November we have the following developers days in the Oracle office in East Point, Dublin.
As you can see we have almost a full week of FREE training from Oracle. So there is no reason not to sign up for these days.
UKOUG Conference – in Birmingham
In December we have the annual UKOUG Conference. This is the largest Oracle User Group conference in Europe and the largest outside of the USA. At this conference you will have some of the main speakers and presentations from Oracle Open World, along with a range of speakers from all over the work.
In keeping with previous years there will be the OakTable Sunday and new this year there will be a Middleware Sunday. You need to register separately for these events. Here are the links
The main conference kicks off on the Monday morning with a very full agenda for Monday, Tuesday and Wednesday. There are a number of social events on the Monday and Tuesday, so come well rested.
On the Monday evening there is the focus pubs. This year it seems to have an Irish Pub theme. At the focus pub event there will be table for each of the user group SIGs.
Come and join me at the Ireland table on the Monday evening.
I will be giving a presentation on the Tuesday afternoon titled Getting Real Business Value from Predictive Analytics (OBIEE and Oracle Data Mining). This is a joint presentation with Antony Heljula of Peak Indicators.
The Autumn 2012 edition of Oracle Magazine (I’m the deputy editor) is now available online. Like in other editions recently there is a bumper online edition.
Here is the marketing release with the details of the contents.
Welcome to Issue 48 of Oracle Scene
In this digital edition find out why you should attend this year’s UKOUG conference – Brendan Tierney tells us why he has been attending since 1998. Read about one organisation’s success story from System Integration to Employee Self-Service, what the new release of Oracle BI Applications 11g has to offer and how it has been improved. Find out about training opportunities in the fastest-changing profession in the world, and Tim Poynter sheds some light on UPK Professional. Plus, Jonathan Lewis outlines how to apply a new strategy for Star Transformation.
Click here to view the digital edition
The articles in this edition:
Discuss the essential ingredient of Oracle Gold Partner, Prōject, transformation
Explore the ABC of ADF and why it may matter to you
Look at Oracle Fusion Middleware and summarise the most common products
Explore solutions for space management of database and file system
Outline optimal Oracle configuration for efficient table scanning
And there’s more…
How to raise a Service Request with Oracle Support for Exadata
Introduction to Data Vault Modeling
Next Generation Service Patterns using Oracle Fusion Middleware
HR Platform – the changes and how to implement it successfully
Winners of the UKOUG 2012 Partner of the Year Awards
We hope you enjoy a good read.
The deadline for submitting an article for the Spring 2013 edition will be in early January.
Many thanks to Brigit Wells and the other member of the UKOUG office who have worked on getting this edition and every edition out to print and online. Also a thank you to Geoff Swaffer who is the editor, and myself who is the deputy editor
One of the most interesting of important aspects of a Decision Model is that we as a user can get to see what rules the machine learning algorithm has generated for our data.
I’ve give a number of examples in various blog posts over the past few years on how to generate a number of classification models. An example of the workflow is below.
In the Class Build node we get four models being generated. These include a Generalised Linear Model, Support Vector Machine, Naive Bayes and a Decision Tree model.
We can explore the Decision Tree model by right clicking on the Class Build Node, selecting View Models and then the Decision Tree model, which will be labelled with a ‘DT’ in the name.
As we explore the nodes and branches of the Decision Tree we can see the rule that was generated for a node in the lower pane of the applications. So by clicking on each node we get a different rule appearing in this pane
Sometimes there is a need to extract this rules so that they can be presented to a number of different types of users, to explain to them what is going on.
How can we extract the Decision Tree rules?
To do this, you will need to complete the following steps:
- From the Models section of the Component Palette select the Model Details node.
- Click on the Workflow pane and the Model Details node will be created
- Connect the Class Build node to the Model Details node. To do this right click on the Class Build node and select Connect. Then move the mouse to the Model Details node and click. The two nodes should now be connected.
- Edit the Model Details node, uncheck the Auto Settings, select Model Type to be Decision Tree, Output to be Full Tree and all the columns.
- Run the Model Details node. Right click on the node and select run. When complete you you will have the little green box with a tick mark, on the top right hand corner.
- To view the details produced, right click on the Model Details node and select View Data
- The rules for each node will now be displayed. You will need to scroll to the right of this pane to get to the rules and you will need to expand the columns for the rules to see the full details
Almost everyone starting out with Oracle have come across the schema SCOTT.
So who was SCOTT?
Bruce Scott was employee number 4 at Oracle back in the 1970s and was part of the team responsible for architecting and writing version 1, 2 and 3 of the Oracle Database.
So he set up the first schema for testing and demo-ing how to use SQL in the Oracle Database. The EMP and DEPT tables and the data that we still see in this schema is still based on the original data that he created.
How did he come up with the TIGER password ? Tiger was the name of his cat!!!
Sten Vesterli of Scott/Tiger Group
Bruce co-founded Gupta Technology (now known as Centura Software) in 1984 with Umang Gupta, and later became CEO and founder of PointBase, Inc.
The call for papers is now open for Oracle technical papers for publication in the UKOUG Oracle Scene magazine.
The submission date for completed papers is 24th August.
To get more information of paper guidelines and submission details go to,
The Winter edition will be published online and in print format around the end of October. This will be in time for the EPM & Hyperion, JDE and UKOUG 2012 conferences. So this is a chance to get your message across to these communities.
Did you get a presentation accepted for the UKOUG annual conference or were you disappointed ? Maybe you could consider writing a paper based on your presentation and submit it for consideration.
How about advertising in Oracle Scene. Over the past couple of editions we have had a significant increase in readership, with readership from countries around the world.
Over the past few years Oracle Scene has moved from being a regional User Group magazine to having a readership in 30+ countries around the world.
Why am I writing this post ? I’m a deputy editor of Oracle Scene
The call for presentations for Europe’s largest Oracle conference is currently open, but the deadline for submissions is approaching fast. The submission deadline is 1st June.
If you are interested in presenting there are a couple of things you need to do. The first step is that you need to register as a speaker. This just involves you registering your interest in being a speaker. The second step is to submit your presentation abstracts.
The conference will be in Birmingham between 3rd and 5th December. There are multiple streams including BI, Technology, Fusion, Middleware, Development, MySQL, Infrastructure, eBusiness Suite, Core Database, etc.
I gave my very first presentation at the annual UKOUG Conference a few years ago and I’ve presented a few times since. I would encourage everyone to give it a go. Pick a topic or topics that you have been working on over the past 12 months or more, or if you used a particular technique on a recent project, or you have discovered a particular work around, etc. submit a presentation on it.
I’ve submitted a few presentations, all of which are about data mining and the advanced analytics option in the Oracle Database. Two of these presentations will be co-presented with Antony Heljula, Peak Indicators. The presentations will be on including and using Oracle Data Mining models in OBIEE and on how we went about developing the Oracle Data Mining models for our project. Will they get accepted, I hope so, but the presentation selection is based on user voting. Everyone can get involved in the judging and voting of presentations.
Notifications of Acceptances or Rejections typically come out around the end of July or early August.
I’ve already booked by flight to Birmingham in December, so if my presentations get accepted or not, I’ll be there. It is a great conference.