Review Oracle Magazine- July/August 1998

The headline articles for the July/August1998 edition of Oracle Magazine were on designing, developing and delivering Data Marts using the Oracle Database and related tools. The main article looks at the different phases of developing a data mart in 90 days.

One of the key messages in these articles is to keep focused on the business problem and that the technology part is very minor in this. This message is still vey key to the analytics and big data world, keep focused on the business problem.

Other articles included:

• Oracle ships JDeveloper Suite. It included App Builder for Java, Oracle Application Server 4.0, Oracle 8 Database Server, Symantec’s Visual Page HTML editor, and a one year developer’s membership in the Oracle Technology Network. Yes there used to be a cost to be a member of OTN!!!.
• Oracle We Developer Suite wins the PC Magazine Editor Choice award. The suite comes with full development licences for Designer/2000 Release 2.1, including object extensions, Developer/2000 Release 2.1, Oracle App Builder for Java, Oracle Application Server 3.0, Oracle Database Server (releases 7 and 8) and the Oracle Web Development Kit
• Oracle Support announce plans to give read only access, via the web, to its Bug database.
• There was an advert for TOAD when it was still freeware and provided by ToadSoft.
• Security management for Oracle 8, has been increasing in importance over the past few years. For all those people who have some security responsibilities, here are some key elements for database security: System security, Data security, User security, Password management and System auditing. Security is more than just providing a Firewall.
• Building Message-based apps with Oracle 8’s Advanced Queuing, involves 5 main steps, including:
• Start the server’s AQ background process
• Create a database user account to manage queues
• Create a user-defined type for application messages
• Create a queue table and a corresponding queue of the user defined message type
• Build the application to enqueue and dequeue messages of the user defined message type
• For the DBAs there was an article on Fast Full Index Scan, how to enable it and gives a number of examples of the hints including the index_fss.

To view the cover page and the table of contents click on the image at the top of this post or click here.

My Oracle Magazine Collection can be found here. You will find links to my blog posts on previous editions and a PDF for the very first Oracle Magazine from June 1987.

Accepted for BIWA Summit–9th to 10th January

I received an email today to say that I had a presentation accepted for the BIWA Summit. This conference will be in the Sofitel Hotel beside the Oracle HQ in Redwood City.

The title of the presentation is “The Oracle Data Scientist” and the abstract is

Over the past 18 months we have seen a significant increase in the demand for Data Scientists. But how does someone become a data scientist. If we examine the requirements and job descriptions of this role we can see that being able to understand and process data are fundamental skills. So an Oracle developer is ideally suited to being a Data Scientist. The presentation will show how an  Oracle developer can evolve into a data scientist through a number of stages, including BI developer, OBIEE developer, statistical analysis, data miner and data scientist. The tasks and tools will be discussed and explored through each of these roles. The second half of the presentation will focus on the data mining functionality available in SQL and PL/SQL. This will consist of a demonstration of an Analytics Development environment and how you can migrate (and use) your models in a Production environment

For some reason Simon Cowell of XFactor fame kept on popping into my head and it now looks like he will be making an appearance in the presentation too. You will have to wait until the conference to find out what Simon Cowell and Being an Oracle Data Scientist have in common.

Check out the BIWA Summit website for more details and to register for the event.

I’ll see you there

Update on : Adding numbers between

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)
FROM    dual
CONNECT by level <= &End_Number - &&Start_Number + 1;

An even simpler way is

SELECT sum(level)
FROM    dual
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.

Adding numbers between two values

My son asked me the other day, what is the total number if you add all the numbers between Zero and 100.
We could have sat down to work it out with some paper and a pen, but instead I decided to introduce him to the world of Oracle, SQL and PL/SQL
The first step we took was to work out how you would do it on paper for some of the numbers. Then we translated this into some PL/SQL code. OK I did a lot this but he did seem to understand and follow what I was doing.
So the following Function is what we ended up with to add all the numbers between two numbers and return the answer.
CREATE or REPLACE function AddNumsBetween
       ( pStartNum IN NUMBER,
         pEndNum IN NUMBER)
   RETURN NUMBER
IS
   vSum   Number := 0;
BEGIN
   FOR i IN pStartNum .. pEndNum LOOP
      vSum := vSum + i;
   END LOOP;
   return vSum;
END;
/
The next step was to write some code to call this function. The code prompts the user to enter the Start number and End number.
set serveroutput on
DECLARE
   vStartNum  NUMBER := 0;
   vEndNum   NUMBER := 100;
   vAnswer    NUMBER := 0;
BEGIN
   vStartNum := &Start_Number;
   vEndNum := &End_Number;
   vAnswer := AddNumsBetween(vStartNum, vEndNum);
   dbms_output.put_line('The sum of numbers between '||vStartNum||' and '||vEndNum||' is '||vAnswer||'.');
END;
/
To answer by son’s original query, we used Zero and 100 as our inputs.

The answer to the question is 5,050.

Events for Oracle Users in Ireland-November 2012

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 Day

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.

Click here to register for this event.

Click here for the full details and agenda.

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.

Tuesday 27th November (9:45-15:00) : Real Application Testing

Wednesday 28th November (9:00-14:00) : Partitioning/Advanced Compression

Thursday 29th November (9:15-13:30) : Database Security

Friday 30th November (9:45-16:00) : Business Process Management Using BPM Suite 11g

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

OakTable Sunday

Middleware Sunday

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.

The full agenda in now live and you can get all the details here.

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.

OOW content/slides are still available

For those people who where lucky to get the Oracle Open World (OOW) and for all the many thousands of people who were not able to make it to OOW, the slides from almost all the presentations are still available.

To get your hands on these presentation slides, all you need to do is to go to the Oracle Open World website

http://www.oracle.com/openworld/index.html

Click on the Tools option on the menu and then click on Schedule Builder. You will need your Oracle Single-Sign-On username and password. Once entered you should get conference content and Schedule page.

You can search the content catalog for the presentations you are interested in and download the presentations.

There was been some mutterings that the presentation slides and access to the schedule build will be restricted at some time in the near future.

So get the conference material now.

While you are on the Oracle Open World site, why not sign up for notifications for the 2013 conference. They will probably start around early March.

ASCII to character conversion in Oracle

Here is code code that will produce formatted output of the characters and their ascii values. The formatting is broken into lower case letters, uppercase letters, characters with an ascii value less than an ‘a’ and characters whose ascii characters are greater than a ‘z.

Code

set serveroutput on FORMAT WRAPPED
DECLARE
    vTab           VARCHAR2(5) := CHR(9);
    vNum          NUMBER := 0;
    vString       VARCHAR2(80) := '';
BEGIN  
   --
   -- Formatted lower case letter to ASCII values
   --
   dbms_output.put_line('Formatted Lower Case Letters to ASCII values');
   dbms_output.put_line('-------------------------------------------------------');
   FOR i IN ASCII('a') .. ASCII('z') LOOP
      IF vNum < 6 THEN
         vString := vString||CHR(i)||' : '||i||vTab;
         vNum := vNum + 1;
      ELSIF vNum = 6 then
         dbms_output.put_line(vString||CHR(i)||' : '||i);
         vNum := 0;
         vString := '';
      ELSE
         dbms_output.put_line('ERROR');
      END IF;
   END LOOP;
   dbms_output.put_line(vString);

   --
   -- Formatted upper case letter to ASCII values
   --
   vString := '';
   vNum := 0;

   dbms_output.new_line;
   dbms_output.new_line;
   dbms_output.put_line('Formatted Upper Case Letters to ASCII values');
   dbms_output.put_line('-------------------------------------------------------');
   FOR i IN ASCII('A') .. ASCII('Z') LOOP
      IF vNum < 6 THEN
         vString := vString||CHR(i)||' : '||i||vTab;
         vNum := vNum + 1;
      ELSIF vNum = 6 then
         dbms_output.put_line(vString||CHR(i)||' : '||i);
         vNum := 0;
         vString := '';
      ELSE
         dbms_output.put_line('ERROR');
      END IF;
   END LOOP;
   dbms_output.put_line(vString);

   --
   -- Formatted chars less than 'a' to ASCII values
   --
   vString := '';
   vNum := 0;

   dbms_output.new_line;
   dbms_output.new_line;
   dbms_output.put_line('Formatted Letters, less than a  to ASCII values');
   dbms_output.put_line('-------------------------------------------------------');
   FOR i in 0 .. ASCII('a')-1 LOOP
      IF vNum < 6 THEN
         vString := vString||CHR(i)||' : '||i||vTab;
         vNum := vNum + 1;
      ELSIF vNum = 6 then
         dbms_output.put_line(vString||CHR(i)||' : '||i);
         vNum := 0;
         vString := '';
      ELSE
         dbms_output.put_line('ERROR');
      END IF;
   END LOOP;
   dbms_output.put_line(vString);

   --
   -- Formatted chars greater than 'Z' to ASCII values
   --
   vNum := 0;
   vString := '';

   dbms_output.new_line;
   dbms_output.new_line;
   dbms_output.put_line('Formatted Letters, greater than z  to ASCII values');
   dbms_output.put_line('-------------------------------------------------------');
   FOR i IN ASCII('z') .. ASCII('z')+133 LOOP
      IF vNum < 6 THEN
         vString := vString||CHR(i)||' : '||i||vTab;
         vNum := vNum + 1;
      ELSIF vNum = 6 then
         dbms_output.put_line(vString||CHR(i)||' : '||i);
         vNum := 0;
         vString := '';
      ELSE
         dbms_output.put_line('ERROR');
      END IF;
   END LOOP;
   dbms_output.put_line(vString);

END;
/

Output

Formatted Lower Case Letters to ASCII values
-------------------------------------------------------
a : 97  b : 98  c : 99  d : 100 e : 101 f : 102 g : 103
h : 104 i : 105 j : 106 k : 107 l : 108 m : 109 n : 110
o : 111 p : 112 q : 113 r : 114 s : 115 t : 116 u : 117
v : 118 w : 119 x : 120 y : 121 z : 122

Formatted Upper Case Letters to ASCII values
-------------------------------------------------------
A : 65  B : 66  C : 67  D : 68  E : 69  F : 70  G : 71
H : 72  I : 73  J : 74  K : 75  L : 76  M : 77  N : 78
O : 79  P : 80  Q : 81  R : 82  S : 83  T : 84  U : 85
V : 86  W : 87  X : 88  Y : 89  Z : 90

Formatted Letters, less than a  to ASCII values
-------------------------------------------------------
  : 0   ☺ : 1   ☻ : 2   ♥ : 3   ♦ : 4   ♣ : 5   ♠ : 6
: 7 : 8                 : 9
: 13   ♂ : 11  ♀ : 12
♫ : 14  ☼ : 15  ► : 16  ◄ : 17  ↕ : 18  ‼ : 19  ¶ : 20
§ : 21  ▬ : 22  ↨ : 23  ↑ : 24  ↓ : 25  → : 26  ← : 27
∟ : 28  ↔ : 29  ▲ : 30  ▼ : 31    : 32  ! : 33  " : 34
# : 35  $ : 36  % : 37  & : 38  ' : 39  ( : 40  ) : 41
* : 42  + : 43  , : 44  - : 45  . : 46  / : 47  0 : 48
1 : 49  2 : 50  3 : 51  4 : 52  5 : 53  6 : 54  7 : 55
8 : 56  9 : 57  : : 58  ; : 59  < : 60  = : 61  > : 62
? : 63  @ : 64  A : 65  B : 66  C : 67  D : 68  E : 69
F : 70  G : 71  H : 72  I : 73  J : 74  K : 75  L : 76
M : 77  N : 78  O : 79  P : 80  Q : 81  R : 82  S : 83
T : 84  U : 85  V : 86  W : 87  X : 88  Y : 89  Z : 90
[ : 91  \ : 92  ] : 93  ^ : 94  _ : 95  ` : 96

Formatted Letters, greater than z  to ASCII values
-------------------------------------------------------
z : 122 { : 123 | : 124 } : 125 ~ : 126 ⌂ : 127 Ç : 128
ü : 129 é : 130 â : 131 ä : 132 à : 133 å : 134 ç : 135
ê : 136 ë : 137 è : 138 ï : 139 î : 140 ì : 141 Ä : 142
Å : 143 É : 144 æ : 145 Æ : 146 ô : 147 ö : 148 ò : 149
û : 150 ù : 151 ÿ : 152 Ö : 153 Ü : 154 ø : 155 £ : 156
Ø : 157 × : 158 ƒ : 159 á : 160 í : 161 ó : 162 ú : 163
ñ : 164 Ñ : 165 ª : 166 º : 167 ¿ : 168 ® : 169 ¬ : 170
½ : 171 ¼ : 172 ¡ : 173 « : 174 » : 175 ░ : 176 ▒ : 177
▓ : 178 │ : 179 ┤ : 180 Á : 181 Â : 182 À : 183 © : 184
╣ : 185 ║ : 186 ╗ : 187 ╝ : 188 ¢ : 189 ¥ : 190 ┐ : 191
└ : 192 ┴ : 193 ┬ : 194 ├ : 195 ─ : 196 ┼ : 197 ã : 198
à : 199 ╚ : 200 ╔ : 201 ╩ : 202 ╦ : 203 ╠ : 204 ═ : 205
╬ : 206 ¤ : 207 ð : 208 Ð : 209 Ê : 210 Ë : 211 È : 212
ı : 213 Í : 214 Î : 215 Ï : 216 ┘ : 217 ┌ : 218 █ : 219
▄ : 220 ¦ : 221 Ì : 222 ▀ : 223 Ó : 224 ß : 225 Ô : 226
Ò : 227 õ : 228 Õ : 229 µ : 230 þ : 231 Þ : 232 Ú : 233
Û : 234 Ù : 235 ý : 236 Ý : 237 ¯ : 238 ´ : 239 ­ : 240
± : 241 ‗ : 242 ¾ : 243 ¶ : 244 § : 245 ÷ : 246 ¸ : 247
° : 248 ¨ : 249 · : 250 ¹ : 251 ³ : 252 ² : 253 ■ : 254
  : 255

PL/SQL procedure successfully completed.

Observations

There are two things that stand out in this. The first is there is sound produced. This is because one of the characters is defined this way. It is ASCII number 7. This can be repeated using the following:

select chr(7) from dual.

The second is the formatting of the lines for ascii codes 8 to 12. We can see that one of the ascii codes does not get displayed and the ordering of this is not as expected. This is due to ascii 10 being a line feed.

Oracle Scene (Autumn 2012) now available

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 Click here to view the digital edition

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. Click here to view the digital edition

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

New features for Developers in Oracle 12c & Tools

Continuing on from my previous posts on new features in the Advanced Analytics Option and the 12c Databases, this post will focus on the proposed new features for Developers in 12c and in the new releases of the development tools.

Health Warning: As with all the presentations at OOW that talked about what may be in or may be in the next release, there is no guarantee that these features will actually be in the released version of the database. Here is the slide that gives the Safe Harbor statement.

• APEX 4.2 is out now and has lots of new features in particular features for creating mobile applications including transitions, gestures, changes in orientation and HTML 5 support. Coming in 12c APEX will be able to support the pluggable database environment. You will have the option to install APEX in the Contain database or in the pluggable databases. It will also support the extended VARCHAR2 size
• SQL Developer will have Data Pump to allow for fast movement of data and for scheduling of the movements. The Database Difference tool has been redesigned to give more options and gives a more reliable comparison. A redesigned Database Copy (more options), improved Migrations and PDF report generation. SQL Developer is not the admin tool to manage the APEX Listener. UI improvements include more/better drag and drop, GRANT statement support in SQL editor and database Doc reporting. A new release of SQL Developer will be made available with the release of 12c that includes all the 12c new features
• Better Data Compression of data being sent to/from the client/server. So how you use the ORDER BY clause will become more important
• We will now have BOOLEAN in 12c but only in PL/SQL
• 12c will allow you to grant ROLES to PL/SQL program units. Or we could specify a White List that lists what other code units can call your code. This is a great security enhancement, although it involves more admin work, but it is worth it.
• 12c will allow you to include PL/SQL in WITH
• 12c will allow you to create duplicate indexes on the same set of columns. Sometimes you might want two different types of index on the same data, for example a bit map index and a b-tree index.
• Cursor results sets can now be returned implicitly instead of the existing explicit method
• The Warning messages when compiling our PL/SQL code can be filtered based on if they are Severe, Performance related or just Informational. This can be set at a System or Session level.

alter session set plsql_warnings='enable:severe';

alter session set plsql_warnings='enable:performance';

alter session set plsql_warnings='enable:informational';

There was a large number of exhibits at OOW. All of them were giving things away. For some of these you had to endure a sales pitch. One of the popular type of give away was a t-shirt. If you really wanted to, you could get enough t-shirts to keep you going for a few years. I popped into the exhibits for JavaOne and the pictures below is my faviourate t-shirt from OOW, by CloudBees.

Some of the exhibits were also giving away money. If you would sit through a 10 minute presentation you were given a ticket and if your number was picked your would could win anything from $20 up to $100. Many thanks to Intel

Oracle Database next release (12c) new features

At Oracle Open World there was a number of presentation on the new features that we will see in the new release of the database, which is being called 12c, where c is for the cloud.

The 12c Database/Next Release was announce during Larry’s Sunday keynote speech. 

Health Warning: As with all the presentations at OOW that talked about what may be in or may be in the next release, there is no guarantee that these features will actually be in the released version of the database. Here is the slide that gives the Safe Harbor statement.

In addition to this key note there was a few additional presentations on 12c with the main one, for me, by Tom Kyte on the Thursday morning.

• The 12c database will be a multi-tenant database. What does this mean? If means that we can have multiple databases running on the same server sharing the same memory and process management. Under the current configuration, when we create a new database on a server we create a separate set of processes and memory allocation.  Oracle is calling this, Pluggable Databases

• MapReduce in the Database - MapReduce can be run from PL/SQL directly in the database.
• Improved default value management. We will be able to clearly how to allocate a value to or calculate a value as a default for a column. The first of these is that we will be able to assign a sequence to a column. Example x int default s.nextval primary key. We can then insert into a table and not worry about how to calculate a primary key value, insert into t(y) values ('hello world');.  We will also be able to specify a default value when no value is being inserted into a column, z number default on null 42
• Increased size for VARCHAR2, NVARCHAR2 and RAW data types up to 32K in size. You will need to se the Max_SQL_String_Size in the init.ora to EXTENDED. But this new size limit is not supported in clustered and index organised tables.
• Some improvements to PARTITIONing, with asynchronous global index maintenance for DROP and TRUNCATE partition. New CASCADE functionality for TRUNCATE and EXCHANGE partition. You will be able to perform multiple partition operations with a single DDL command.
• There will be better Statistics for query performance including Hybrid Historgrams, Session Private Statistics and statistics will be gathered during data loads automatically.
• Temporary UNDO for objects will be handled in TEMP
• Greater insight of data usage i.e .what is being used or not being used and how frequently. This allows policies in table definition (new ILM clause) to compress or archive data after time.

ALTER TABLE orders
ILM ADD CompressionPolicy
COMPRESS Partitions for Query
AFTER 90 days from creation;

This post focused on the core database new features what may be coming in 12c.

In my next blog post I will look at some of the coding/development changes that are coming in 12c and associated Oracle development tools

Oracle Advanced Analytics Option in Oracle 12c

At Oracle Open World a few weeks ago there was a large number of presentations on Big Data and Analytics.  Most of these were marketing type presentations, with a couple of presentations on using R and how it can not be integrated into the Oracle Database 11.2.

In addition this these there was one presentation that focused on the Oracle Advanced Analytics (OAA) Option.

The Oracle Advanced Analytics Option covers the Oracle Data Mining features and the Oracle R Enterprise features in the Database.

The purpose of this blog post is to outline and summarise what was mentioned at these presentations, and will include what changes are/may be coming in the “Next Release” of the database i.e. Oracle 12c.

Health Warning: As with all the presentations at OOW that talked about what may be in or may be in the next release, there is not guarantee that the features will actually be in the release version of the database. Here is the slide that gives the Safe Harbor statement.

• 12c will come with R embedded into it. So there will be no need for any configurations.
• Oracle R client will come as part of the server install.
• Oracle R client will be able to use the Analytics functions that exist in the database.
• Will be able to run R code in the database.
• The database (12c) will be able to spawn multiple R engines.
• Will be able to emulate map-reduce style algorithms.
• There will be new PREDICTION function, replacing the existing (11g) functionality. This will combine a number of steps of building a model and applying it to the data to be scored into one function.  But we will still need the functionality of the existing PREDICTION function that is in 11g. So it will be interesting to see how this functionality will be kept in addition to the new functionality being proposed in 12c.
• Although the Oracle Data Miner tool will still exits and will have many new features. It was also referred to as the ‘OAA Workflow’.  So those this indicate a potential name change?  We will have to wait and see.
• Oracle Data Miner will come with a new additional graphing feature. This will be in addition to the Explore Node and will allow us to produce more typical attribute related graphs. From what I could see these would be similar to the type of box plot, scatter, bar chart, etc. graphs that you can get from R.
• There will be a number of new algorithms too, including a useful One Class Support Vector Machine. This can be used when we have a data set with just one class value. This algorithm will work out what records/cases are more important and others.
• There will be a new SQL node. This will allow us to write our own data transformation code.
• There will be a new node to allow the calling of R code.
• The tool also comes with a slightly modified layout and colour scheme.

Again, the points that I have given above are just my observations. They may or may not appear in 12c, or maybe I misunderstood what was being said.

It certainly looks like we will have a integrate analytics environment in 12c with full integration of R and the ODM in-database features.

Extracting the rules from an ODM Decision Tree model

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