OUG Ireland Super SIG Day : Sept. 24 2014
The next set of Ireland Oracle User Group SIG meetings will be on Wednesday 24th September. This will be a super SIG event with the following SIGs being run in parallel and all in one venue (Jurys Inn, Customs House, Dublin).
OUG Ireland BI & EPM SIG Meeting
OUG Ireland Technology SIG Meeting
Click on the links above for each SIG to get the details of agenda. There is a great line up of presentations for each SIG with some true experts.
From what I’ve been hearing that has been a lot of registrations already for these events. So if you are interested in attending then sign up now to reserve your place. Click on the SIG you want to attend and there will be a registration button on the webpage, just below the agenda.
Unfortunately I won’t be at these event 😦 The 24th September is the day that I travel out to Oracle Open World. Yes I know OOW doesn’t start until the 29th, but I will be attending the Oracle ACE Director briefing at Oracle HQ in Redwood Shores on the 25th and 26th.
Hopefully there will be lots of twitter action during these SIGs and don’t forget to use the OUG Ireland twitter hash tag of #oug_ire That way I and others can follow what is happening during the day.
me at Oracle Open World 2014
This day 3 weeks time (on the 24th Sept) I will be heading out to San Francisco for Oracle Open World. Over the course on 9 days I will be taking part in the Oracle ACE Director briefing and in Oracle Open World. The following outlines my main activities, sessions that I’m presenting at and sessions that I have been asked to attend.
25th-26th Sept. I will be at the Oracle ACE Director briefing. This will be my second briefing as it is a fantastic event where most of the SVPs and various VPs come in to tell us what plans Oracle has for the next few months and what announcements will be made at OOW. We even get Thomas Kurian in to talk to us for about 90 minutes.
Sunday 28th Sept. 9:00-9:45. I will be presenting with Roel Hartman on how to integrate Oracle Data Mining into APEX. The demonstration will involve how you can built BI dashboards in APEX and how by using Oracle Data Mining you can monitor customer sentiment over time. If we get time we home to show you how you can also use this customer sentiment (using Oracle Data Mining) to build some workflows in APEX. The presentation is part of the User Group Sunday Forum and will be in room 304 in the Moscone West building.
Monday 29 Sept. 18:30-20:00. Steven Feuerstein and Tom Kyte are hosting a special event that is a Celebration of SQL and Pl/SQL. Oracle Press has asked me and a few other authors to be at this event and this might have some copies of out books available. I was going to this event anyway as it is a great way to celebrate something that we us on a daily basis and for me for the past 21+ years.
Tuesday 30th Sept. 8:30-12:00. I will be at a Oracle Publishers Seminar. This is a special event arranged for people who have published books on Oracle with the main book publishers. I’ve been invited to this by Oracle Press.
Wednesday 1st Oct. is going to be a busy day between sessions, the Oracle Press book signing and the bloggers meet up.
10:15-11:00 (Moscone South 301) Fiserv will be presenting their case study on how they used Oracle Data Mining to combat fraud in online payments. Charlie Berger, the product manager for ODM, will be co-presenting. He has said he will be giving my book a plug and will point to me in the audience if anyone would like to talk to me about it.
13:00-13:00 Oracle Press has arranged a meet the authors / Book signing session. So during this time you will find me at the Oracle Press store at OOW. If you buy my book I will be on hand to sign it for you or if you have already bought the book then bring it along and I can sign it for you.
18:00-20:00 Every year at OOW this is a bloggers meet-up sponsored by OTN and Phytian. This is where us independent bloggers from around the world get to meet each other face-to-face and get to bond over a drink. As must of us blogger communicate in the virtual world this is a great event for us to put faces to names and to talk about our blogs. This will be in Jullian’s beside the Moscone centre.
There are lots of other sessions and events on during OOW. I hope to get to as many as possible during the week. But the sessions above are the ones (at the time of writing this) that you will definitely find me at.
A big thank you has to go out to Oracle and OTN who are sponsoring my flights and hotel during this trip. This is part of the Oracle ACE Director programme. Also for many of the other events that go on during the week (and a bit) of OOW. Also a thank you to DIT for giving me the time to go to OOW.
Oracle Magazine-January/February 2001
The headline articles of Oracle Magazine for January/February 2001 were on how the current set of Oracle products supported the development and deployment of mobile and internet applications.
Other articles included:
- Oracle and HP form an alliance to develop and delivery CRM solutions aimed at helping companies keep up with customers in a world where rapid change is common place.
- In Tom Kyte’s regular article he look at and gives examples of using function based indexes, automatically calculating percentages in queries, password protecting the Listener functions, making user lists and securing your data using DBMS_OBFUSCATION_TOOLKIT.
- Oracle announces the release of Oracleportal.com and Oracle Portal Studio.
- Over the past few months Oracle has gone about renaming a number of their products. A full page id give over to this list that maps the old product name to the new product name.
- There was a number of small announcements of collaborations between Oracle and Sun. The start of a beautiful friendship that eventually ended in a marriage.
- Steven Feuerstein had an article on Dynamic Approaches to Multirow Queries. Based on the native dynamic SQL introduced in Oralce 8i he shows us how to handle multiple row results either individually or as a collection.
- In the article on Net8 examples are given showing how net service names are resolved and using LDAP.
- The Oracle 8i Database has components that allow you to take advantage of XML technology. These include XSQL pages, how to install XSQL Servlet, registering the xsql file, processing dynamic xml documents from SQL queries and supporting XSLT tags.
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.
Ice Bucket Challenge
Yes I was nominated by Ronald Vargas in Costa Rica to take up the challenge.
Here is the video.
My nominees are Mark Rittman, Heli Helskyaho and Chet Justice (aka Oracle Nerd)
You have 24 hours to take up the challenge and to post a video for all to see 🙂
OUG Mexico
We (Gorcan, Glen, Debra and I) arrived into Mexico around 10pm and had a few minutes wait for our local user group contact to meet us. They had arranged transportation to our hotel.
The next morning (Friday) was the day of the OUG Mexico conference. We were collected from the hotel and taken to the conference venue. Our transport had to made a number of trips to/from the conference venue to cater for all the speakers, so some of us arrived in the middle of the opening keynote by Noel Portugal from Oracle.
After lunch we had a group photo of all the speakers.
My 2 sessions were on in the afternoon so I got to relax a bit and hang out with some of the other speakers. My first session was on Oracle Data Mine and my second session was on Oracle R Enterprise. Just like in Costa Rica I had a good attendance and again they seemed to enjoy the presentations as they were laughing along at my attempts at jokes 🙂
The Mexico conference was on Friday the 8th August and this was an important day as my book was officially available that day. Just like in my previous countries I had a copy of my book to give away. Here is a photo of me with the winner.
I had the last session of the day. Some of the sessions before this had over ran and I was under a bit of pressure to finish up my last presentation by 5pm. So apologies if this presentation seemed rushed.
Also many thanks to everyone who came up to me afterwards for a chat, to have your photo taken with me and to get my autograph! 🙂
Then it was time to catch a taxi to my next hotel in Mexico city centre. This would be my 6th hotel in 6 days 😦
After checking into the hotel it was time to go for dinner. Renne Antunez had invited some of the speakers to his house for dinner. These was a very enjoyable evening and for me marked the end of my OTN tour. Many of the other speakers were going to over countries over the next week.
Many thanks to Renne and his wife for hosting this dinner, especially as it was Renne’s wife birthday. I hope he bought her a good present.
The next morning (Saturday) I was up at 5am to get a taxi to the airport and my long long travels home, from Mexico to Newark to Dublin getting home on Sunday morning at 8am.
A big thank you to the organisers of the OTN Tour and to each of the countries that invited me to present at their conference. I really enjoyed the experiences and hopefully I can join in another OTN tour sometime soon. Also a special thank you to Vikki in OTN for sorting out all the funding and everything else.
The finals thank you goes out to my travel companions for the tour, Gorcan, Debra and Glen. Without your companionship throughout the week it wouldn’t have been as much fun.
OUG Costa Rica
After the OUG Panama conference we arrived later that night in San Jose the capitol of Costa Rica. The whole emigration, luggage pick up and customs was the smoothest experiences I have ever experienced at an airport. All was done in a matter of minutes. All the booths for emigration and customs was open and staffed. When was the last time you have ever seen this before. It was a very positive start to our short couple of days in Costa Rica. We had arranged for our hotel to have a taxi pick us up and sure enough the driver was there with a good clear sign. From time of landing to being in my hotel room, which was a 20 minute drive, took no more than 1 hour.
The next morning (Tuesday) we (Debra, Glenn, Gurcan and I) headed out to do some exploring of San Jose on foot. Our first task was to get some breakfast but none of us had any local currency. Debra volunteered to use one of the enclosed ATM machines to get some money. But there was some issue with the machine and she got locked into the cubical. At the same time she got a phone call from her bank in the UK. Her ATM transaction was flagged as a potential fraudulent transaction. I most say Debra’s bank was very quick to contact her and to make all the necessary changes so that she could withdraw some money. After which we had a local breakfast.
Then we sent the next 3 hours walking around San Jose.
Then it was back to the hotel in the early afternoon as the local Oracle User Group had arranged a mini bus to take us up to a hotel that was near the conference location in San Carlos. We had been told that the journey would take 2 hours. Well after 2 hours of driving up hill we were told we were only just over half ways there. It was a long long journey but the views were really beautiful. We had a 10 minute break at little village with a nice church and a really cool garden in front of it.
After anther hour we arrived at the hotel, checked in, and we met up with some other presenters for some dinner. Then it was time for bed as we were being collected at 7:30am.
On Wednesday morning we got collected and taken to the conference venue which happened to be a local university. The User Group had proved a bus service from San Jose to the conference venue and there was a sizeable number of industry people mixed in with some of the university students.
My first presentation was in the auditorium just after the opening keynote. This has to be one of finest rooms I’ve seen and presented in. Even better we had a HD projector. This has to be a first, so I had no resolution issues with running my VMs. I had a really good turn out for my presentation and the photo below shows only some of those who attended.
In the after noon I had my 2nd and 3rd presentations. For my last presentation all the seats were taken and there was even some people standing. There was a lot of people who came to all my presentations which I was delighted with.
After my last presentation I had a raffle for a copy of my book. There seemed to be a lot of interest in this :-). Here is a photo of me with the winner of the book and someone else who was having a read of it afterwards.
This was a very enjoyable conference and the attendees at my sessions were laughing at some of my jokes or maybe they were laughing at me. Anyway I took it that they were enjoying my presentations. For the first time, I had people come up to me after my presentations to have their photography taken with me and to ask me for my autograph.
Here is a picture of the Oracle ACE Directors who presented at the OUG Costa Rica conference.
We were told that our bus journey the day before was the scenic route so that is why it took 3 hours. As soon as the conference was finished we got back on the mini bus to be taken back to our hotel in San Jose. Theoretically it should have been a much quick journey as we were going back the quickest route. This time it took 2 hours 40 minutes.
On Thursday at 2:45am we were woken by a 4.7M earth quake. The room was shaking and everything was shaking in it including me. After breakfast on the Thursday morning, Debra had arranged for us to go on a tour of a Coffee plantation and factory. This coffee tour was hilarious and the best tours I have ever been on. Our tour guides were a comedy double act 🙂
After the tour it was back to the hotel and to get our taxi out to the airport. Did I mention how well operated the airport was. How often have you gone through security when all of the scanners were in operation and fully staffed. This meant that there was almost no queue and I was through security in no time at all. From check in to the departure gate took about 2 minutes.
Our (Glen, Debra, Gorcan and I) next stop was Mexico
OUG Panama
The OTN Tour of Latin America has come to an end and I was very lucky to have been a part of Northern part of the tour.
My OTN Tour consisted of visiting 3 countries in one week and giving 8 hours of presentations. This blog post looks back at the OUG Panama conference and I’ll have separate blog posts for each of the other countries.
My travels stared the Saturday before the OUG Panama conference which was on Monday 4th. My flights consisted of flying from Dublin to Newark and then onto Panama. All of that seemed to go smoothly until I reached Panama. After passing through emigration I was then in a very chaotic baggage hall. After about 1 hour of searching for my suit case I sought help from the airline. They were able to tell me that my suitcase was scanned off in Panama. So after another 30 minutes of searching for the suitcase with airline staff, there was still no sign of it. So a lost suitcase report was filed and off I went to meet the person who was collecting me from the airport. But there was no sign of them. I managed to find some wifi and sent a few messages looking for help and how could I get to my hotel. I got a quick reply and and 45 minutes later I was on my way to the airport.
This is the 3rd time that this particular airline has “lost” my suitcase.
I was very glad to reach the hotel but I had no change of cloths or toiletries with me 😦 For all future travels that involve a flight change I will be packing some of these in my hand luggage.
On the Sunday morning, after several hours trying to contact the airline, I finally got approval from them to buy some items. It was then time to meet up with Gurcan Orhan and we headed out to explore Panama city on foot (and to buy me some things). We spent about 3 hours walking around exploring Panama city where we say lots of differences in living standards right beside each other. Then it was back to the hotel as the local user group had arranged a tour for us.
Our tour guide and one of the helpers for the user group was Melin took us to see the Panama canal, the old town and lots of other areas of Panama. We finished of the evening with a dinner in a traditional Panama restaurant. On getting back to the hotel my suitcase was there 🙂 but no communication from the airline about what happened.
On the Monday morning I met up with Gorcan, Debra and Glen who were to be my travel companions for the week. There was a bus arranged to pick us up from the hotel and off we went to the conference venue which was in one of the Universities in Panama. We arrived at the venue very early and not many knew about the conference or where we should go. After a while we worked it and found the conference location.
Many thanks to the User Group in Panama for inviting us and for all the arrangements. There was plenty of helpers on hand.
My 3 sessions were not on until the afternoon so I hung out in a session by Gurcan and also one by Dan Morgan. Then a bit of hanging around with the conference people and talking about some Oracle tech stuff. For my 3 sessions I completely over ran on my first session and then for the last 2 sessions I was playing catchup. Especially for the last presentation as there was a bus booked to take us to the airport at 4pm. So I basically ran out of the presentation and straight into the bus to the airport. But before I did that I was able to give away the very first copy of my book. It wasn’t released yet (it would be on the 8th Aug). I had a raffle for everyone who attended my sessions and here is a picture of me with the lucky winner.
We arrived at the airport, with a minor detour via the hotel, with lots and lots of time to spare for our flight. Our next location was Costa Rica.
my Oracle Data Miner Book
Some of you may be aware that I have been writing a on Oracle Data Miner. Actually the book covers the Oracle Data Miner GUI that is part of SQL Developer, the SQL and PL/SQL functions, procedures and packages that form the Oracle Data Mining option in the database and lots of other topics for the DBA, Developer and BI/DW people.
Today is a bit day for this book as it is officially released and available for purchase. See below for some links to where you can but the book in print and e-book formats. It has been published by McGraw-Hill/Oracle Press.
The book is aimed at a variety of people and the aim of the book is to introduce them to using the Oracle Data Miner tool and how to perform various data mining and predictive analytics tasks using SQL and PL/SQL.
The book will not teach you about how each of the data mining algorithms works. There is a bit of an assumption that you know a bit about these already. There are lots of books and resources about that cover that material. You can look on my book as an getting start / how to use type of book.
Below are are the images of the front cover and the back cover.
For more details of the book and for some updates keep an eye on my ODM Book page. On this page I’m adding a FAQ secion. This will be based on questions that I receive about the book.
If you buy the book then I hope you will find it helpful. If you are going to attend one of my presentations at an Oracle User Group meeting then bring the book along and I can sign it for you. Alternatively if you are at Oracle Open World 2014, come along to the Oracle Press Book Store, as I will be there to sign books on Wednesdays 1st October between 13:00 and 13:30.
Where can you Buy my Oracle Data Miner book (print and e-book).
You can buy the book from the McGraw-Hill/Oracle Press website and from Amazon. Each site will offer discounts so check out which one is the best for you.
McGraw-Hill/Oracle Press
For USA locations (enter promo code Tierney to save 20% and free delivery) www.mhprofessional.com
For UK & Ireland locations (enter promo code Tierney to save 20% and free delivery) www.mcgraw-hill.co.uk/tpr
Amazon
Click here to buy it on www.amazom.com
Click here to but it on www.amazon.co.uk
Oracle Magazine-November/December 2000
The headline articles of Oracle Magazine for November/December 2000 were on how application service providers are changing the way that business operate, everything from project management collaboration, professional services, online marking and how to go about becoming one.
Other articles included:
- Tom Kyte has an article on how working with Java is simpliernow that it is a native part of the Oracle database. He gives some examples of how to get some of the typical DB information and processing the results.
- Oracle backs the development of a Common Warehouse Meta-model. This is and XML based metadata standard that was developed by the OMG. This will allow the exchange of e-business information and hopes to accelerate a company’s ability to move from data to decisions..
- There was a number of articles on the Oracle Internet Platform, how it all works and its various parts. These articles helped to support the article on the application service providers.
- Doug Obeid gives a good introduction article on the Secure Sockets Layer(SSL). He looks at securing transmissions, cryptography, authentication, various protocols and messaging.
- The part 2 of the article on Indexing Oracle 8i interMedia for managing and deploying rich data on the internet.
- Locally Managed Tablespaces looks to explain how the DBA can setup and manage tablespaces, so that the amount of space required can be minimized.
- Steven Feuerstein has an article on using Native Dynamic SQL using DBMS_SQL.
- Following up on previous articles (in previous editions) we have another article on how to go about analysing the Statspack Report. Statspack came with release 8.1.6 of the Oracle Database. The Statspack report is extremely useful for analysing and monitoring your database.
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.
Tokenizing a String : Using Regular Expressions
In my previous blog post I gave some PL/SQL that performed the tokenising of a string. Check out this blog post here.
Thanks also to the people who sent me links examples of how to tokenise a string using the MODEL clause. Yes there are lots of examples of this out there on the interest.
While performing the various searches on the internet I did come across some examples of using Regular Expressions to extract the tokens. The following example is thanks to a blog post by Tanel Poder
I’ve made some minor changes to it to remove any of the special characters we want to remove.
column token format a40
define separator=” “
define mystring=”$My OTN LA Tour (2014?) will consist of Panama, CostRica and Mexico.”
define myremove=”\?|\#|\$|\.|\,|\;|\:|\&|\(|\)|\-“;
SELECT regexp_replace(REGEXP_REPLACE(
REGEXP_SUBSTR( ‘&mystring’||’&separator’, ‘(.*?)&separator’, 1, LEVEL )
, ‘&separator$’, ”), ‘&myremove’, ”) TOKEN
FROM
DUAL
CONNECT BY
REGEXP_INSTR( ‘&mystring’||’&separator’, ‘(.*?)&separator’, 1, LEVEL ) > 0
ORDER BY
LEVEL ASC
/
When we run this code we get the following output.
So we have a number of options open to use to tokenise strings using SQL and PL/SQL, using a number of approaches including substring-ing, using pipelined functions, using the Model clause and also using Regular Expressions.
BUCKET_WIDTH: Calculating the size of the bucket
Some time ago I had some blog posts introducing some of the basic Statistical function available in Oracle. Here are the links to these.
- The first blog post in the series looked at the DBMS_STAT_FUNCS PL/SQL package, what it can be used for and I give some sample code on how to use it in your data science projects. I also give some sample code that I typically run to gather some additional stats.
- The second blog post looks at some of the other statistical functions that exist in SQL that you will/may use regularly in your data science projects.
- The third blog post provides a summary of the other statistical functions that exist in the database.
Most people do not realise that Oracle has over 250+ statistical functions that are available (no addition cost) in all the database versions.
I’ve had a query about one of the functions BUCKET_WIDTH. The question was wondering if it was possible to get the width of the bucket in each case. There does not seem to be a build in feature to get this value, so we have to calculate this ourselves.
Here is an example of how to calculate the bucket width, as on the example I used in my previous blog post.
SELECT bucket, max(age)-min(age) BUCKET_WIDTH, count(*)
FROM (SELECT cust_id,
  age,
width_bucket(age,
(SELECT min(age) from mining_data_build_v),
(select max(age)+1 from mining_data_build_v),
10) bucket
FROM mining_data_build_v
GROUP BY cust_id, age )
GROUP BY bucket
ORDER BY bucket;
What this query gives is an approximate value of the size of the Bucket Width based on the values/records that are in a bucket. The actual values used cannot be determined exactly as there is not function/value in SQL that tells us the actual value.
Tokenizing a String
Over the past while I’ve been working a lot with text strings. Some of these have been short in length like tweets from Twitter, or longer pieces of text like product reviews. Plus others of various lengths.
In all these scenarios I have to break up the data into individual works or Tokens.
The examples given below illustrate how you can take a string and break it into its individual tokens. In addition to tokenising the string I’ve also included some code to remove any special characters that might be included with the string.
These include ? # $ . ; : &
This list of special characters to ignore are just an example and is not an exhaustive list. You can add whatever characters to the list yourself. To remove these special characters I’ve used regular expressions as this seemed to be the easiest way to do this.
Using PL/SQL
The following example shows a simple PL/SQL unit that will tokenise a string.
DECLARE
vDelimiter VARCHAR2(5) := ‘ ‘;
vString VARCHAR2(32767) := ‘Hello Brendan How are you today?’||vDelimiter;
vPosition PLS_INTEGER;
vToken VARCHAR2(32767);
vRemove VARCHAR2(100) := ‘\?|\#|\$|\.|\,|\;|\:|\&’;
vReplace VARCHAR2(100) := ”;
BEGIN
dbms_output.put_line(‘String = ‘||vString);
dbms_output.put_line(”);
dbms_output.put_line(‘Tokens’);
dbms_output.put_line(‘————————‘);
vPosition := INSTR(vString, vDelimiter);
WHILE vPosition > 0 LOOP
vToken := LTRIM(RTRIM(SUBSTR(vString, 1, vPosition-1)));
vToken := regexp_replace(vToken, vRemove, vReplace);
vString := SUBSTR(vString, vPosition + LENGTH(vDelimiter));
dbms_output.put_line(vPosition||’: ‘||vToken);
vPosition := INSTR(vString, vDelimiter);
END LOOP;
END;
/
When we run this (with Serveroutput On) we get the following output.
A slight adjustment is needed to the output of this code to remove the numbers or positions of the token separator/delimiter.
Tokenizer using a Function
To make this more usable we will really need to convert this into an iterative function. The following code illustrates this, how to call the function and what the output looks like.
CREATE OR replace TYPE token_list
AS TABLE OF VARCHAR2(32767);
/
CREATE OR replace FUNCTION TOKENIZER(pString IN VARCHAR2,
pDelimiter IN VARCHAR2)
RETURN token_list pipelined
AS
vPosition INTEGER;
vPrevPosition INTEGER := 1;
vRemove VARCHAR2(100) := ‘\?|\#|\$|\.|\,|\;|\:|\&’;
vReplace VARCHAR2(100) := ”;
vString VARCHAR2(32767) := regexp_replace(pString, vRemove, vReplace);
BEGIN
LOOP
vPosition := INSTR (vString, pDelimiter, vPrevPosition);
IF vPosition = 0 THEN
pipe ROW (SUBSTR(vString, vPrevPosition ));
EXIT;
ELSE
pipe ROW (SUBSTR(vString, vPrevPosition, vPosition – vPrevPosition ));
vPrevPosition := vPosition + 1;
END IF;
END LOOP;
END TOKENIZER;
/
Here are a couple of examples to show how it works and returns the Tokens.
SELECT column_value TOKEN
FROM TABLE(tokenizer(‘It is a hot and sunny day in Ireland.’, ‘ ‘))
, dual;
How if we add in some of the special characters we should see a cleaned up set of tokens.
SELECT column_value TOKEN
FROM TABLE(tokenizer(‘$$$It is a hot and sunny day in #Ireland.’, ‘ ‘))
, dual;
Ora-lytics 
You must be logged in to post a comment.