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 :-)
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.
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.
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
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.
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:
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.
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.
Some time ago I had some blog posts introducing some of the basic Statistical function available in Oracle. Here are the links to these.
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.
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;
For a few years now I (and I'm sure you have too) have heard about and followed the various Oracle User Group tours that OTN arranges/facilitates. A tour consists of a number of Oracle User Groups in a region coordinating together to have their conferences organised so that they can get speakers from across the world to come and present.
For most presenters it involves lots of travel. So instead of them doing all that travelling to present at one conference, they can now extend their travels a little and present in a number of countries. Most of the speakers are Oracle ACE Directors and OTN is very generous with their support in that they pay for all the flights, transportation and hotels. Without the generous support of OTN these tours and perhaps many of the conference would not take place.
With envy I used to follow the various speakers on tweeter as they talked about their travels from country to country and their experiences of meeting the people and exploring the various countries. Yes their time in each country seemed to be limited but they always got to see and do so much.
Earlier this year there was an call for presentations for the various OTN Tours in 2014. I submitted 3 presentations that coverd Oracle Advanced Analytics Option (Oracle Data Mining and Oracle R Enterprise). I thought I didn't stand a chance given the speakers that have participated in previous years.
A couple of weeks ago I received an email saying that I had been accepted onto the OTN Latin America (North) Tour. So you can imagine my excitement. The full OTN Tour North leg covers a number of countries across central and south America and is over a 2 week period. Unfortunately I'm not able to be away for that long, so I was accepted for the conferences on the first week of the tour. This will include Panama, Costa Rica and Mexico :-)
Some of you might think this is a bit of a golly and a holiday. What I've discovered over the past week or more is that it will be far from that. There is a lot of work in preparing the presentations, giving the presentation, setting up live demos between presentation, various meetings with people at the conferences etc etc etc. Then there is all the travel, all the airports, all the airport transfers, all the overnights in hotels. Over the course of 7 days I will be staying 6 different hotels.
I have spent the last week just trying to arrange my flights and hotels. This also involved trying to coordinate with other speakers so that we can travel together as much as possible.
Here are the dates and the presentations that I will be giving at these conferences:
4th August : Panama (in Panama City)
10:00-11:00 : Getting Started with Oracle Data Miner & Predictive Analytics
11:00-12:00 : Combining the Power of R and in-Datbaase Data Mining. Running R in the Database. Seriously!
13:00-13:40 : Sentiment Analysis Using Oracle Data Mining
6th August : Costa Rica (in San Carlos)
10:00-11:00 : Getting Started with Oracle Data Miner & Predictive Analytics
13:00-14:00 : Sentiment Analysis Using Oracle Data Mining
16:00-17:00 : Combining the Power of R and in-Datbaase Data Mining. Running R in the Database. Seriously!
8th August : Mexico (in Mexico City)
14:00-15:00 : Getting Started with Oracle Data Miner & Predictive Analytics
15:00-16:00 : Combining the Power of R and in-Datbaase Data Mining. Running R in the Database. Seriously!
When the agenda for the conferences are available I will have another blog post with their details.
If you are at one of these conference do please say hello :-)
I've finally booked all my flights and hotels. Many thanks to my fellow ACE Director presenters for your research and sharing of travel plans. It looks like there will be a groups of us all travelling together.
Now the next challenge is to prepare the presentations and live demos (yes live demos).
I hope to blog about each of the conferences and my travels to/from each country. It really depends on what time I will have and access to the internet. Perhaps this is something I will try to do on my various plane flights or waiting at the airports. So watch out for these :-)
Updated with some stats on my travels
My travel plans for the OTN Latin America tour of user group conferences involves
Why do we do this? Because we love sharing with the Oracle User Groups around the world. I'm only doing 1 week of the tour. Some people are doing 2 weeks :-(
The headline articles of Oracle Magazine for September/October 2000 were on e-Business Integration, including online healthy prescription for online retailing, streamlineing the pulp and fiber industries, and the health care industry. Plus there was lots and lots of articles and news items all on businesses delivering solutions via the internet.
As this was the Oracle Open World edition (and you see the label on the cover saying Biggest Ever) you can imagine there was a LOT of advertisements and sponsored articles. The following of other articles below will not cover these and will only look at the main content articles.
Other articles included:
After you have installed I ORE on your client and server (see my previous blog posts on these), you are not ready to start getting your hand dirty with ORE. During the installation of ORE you setup a test schema in your database to test that you can make a connection. But you will not be using this schema for you ORE work. Typically you will want to use one of your existing schemas that contains the data in the relevant tables and views. Plus you will want to be able to create some tables, creates some ORE temporary objects and stores, and to be able to store some of you ORE scripts in the schema that contains the data. In a previous blog post I gave some steps needed to setup your schema to be able to use ORE and the embedded feature.
In this post I want to show you some of the commands you will need to use to get connected to your Oracle schema and some initial commands you will need to know an use.
ore.connect command
The first command that you need to use is the ore.connect command that allows you to establish a connection to a database schema. You can also use this command to connect to HIVE tables in a Hadoop cluster.
The general syntax of the ore.connect function is
ore.connect(user = "", sid = "", host = "localhost", password = "",
port = 1521, service_name = NULL, conn_string = NULL,
all = FALSE, type = c("ORACLE", "HIVE"))
From this list of available parameters you only need to specify some of them. For a basic connection you do not need to specify the conn_string, type and all. The following is an example of using this connection string to connect to a schema called DMUSER that is located in a database whose service_name is ORCL and the host is the localhost.
> ore.connect(user="dmuser", sid="orcl", host="localhost", password="dmuser", port=1521, all=TRUE);
> ore.ls()
[1] "DEMO_R_APPLY_RESULT" "DEMO_R_TABLE" "DEMO_SUBSET_TABLE" "INSUR_CUST_LTV_SAMPLE"
[5] "MINING_DATA_APPLY" "MINING_DATA_APPLY_V" "MINING_DATA_BUILD_V" "MINING_DATA_TEST_V"
[9] "MINING_DATA_TEXT_APPLY_V" "MINING_DATA_TEXT_BUILD_V" "MINING_DATA_TEXT_TEST_V"
I generally explicitly include the all=TRUE. The reason for this might become clear below when I show the alternative.
When you us the all=TRUE, the ore.connect function will also run the ore.sync and a ore.attach functions. This will result in synchronizing and attaching all tables and views in the ORE schema. The amount of time to run ore.sync grows linearly with the number of visible tables and views.
If you already have an ORE connection open and you try to establish a new ORE connection then your already existing ORE connection will be automatically disconnected. So you will need to be careful with the sequencing of your ORE code.
You schema might have lots and lots of object. As you work on building your advanced analytics environment you will end up build many more objects. You can imagine that over time every time you establish a connection it will start to take longer and longer. The following commands creates a connection to the schema, but this time it does not sync or attach the database objects, as shown using the ore.ls function.> ore.connect(user="dmuser", sid="orcl", host="localhost", password="dmuser", port=1521, all=FALSE);
> ore.ls()
character(0)
If you use the all=FALSE like is shown in the above example you will need to issue a ore.sync function to synchronise the meta-data and then the ore.attach function to add the synchronised objects to the search space of the local R environment.
is.ore.connected
You can use this command to check that you are connected to your ORE connection is live or not. As you develop your ORE scripts you might build in various connection and disconnections. This command is very useful to check your current status. If you have an ORE connection then you will get a response of TRUE. If you don't have an open ORE connection then you will get a response of FALSE.
> ore.is.connected()
[1] FALSE
After we establish our ORE connection the next time we run this command
> ore.is.connected() [1] TRUE
The following commands can be used to check to see if we have a connection and if not then establish a connection and list the objects in the schema.
> if (!ore.is.connected())
ore.connect(user="dmuser", sid="orcl", host="localhost", password="dmuser", port=1521, all=TRUE)
> ore.ls()
[1] "DEMO_R_APPLY_RESULT" "DEMO_R_TABLE" "DEMO_SUBSET_TABLE"
[4] "INSUR_CUST_LTV_SAMPLE" "MINING_DATA_APPLY" "MINING_DATA_APPLY_V"
[7] "MINING_DATA_BUILD_V" "MINING_DATA_TEST_V" "MINING_DATA_TEXT_APPLY_V"
[10] "MINING_DATA_TEXT_BUILD_V" "MINING_DATA_TEXT_TEST_V"
> ore.disconnect()
NOTE: Some of the documentation seems to refer to the command is.ore.connected(). This generates and error and seems to be an error in some of the ORE documentation.
ore.ls()
The ore.ls() command is used to find out what objects you have in your schema. The objects that it will list are all the tables and views that are defined in the schema.
> ore.ls()
[1] "DEMO_R_APPLY_RESULT" "DEMO_R_TABLE" "DEMO_SUBSET_TABLE"
[4] "INSUR_CUST_LTV_SAMPLE" "MINING_DATA_APPLY" "MINING_DATA_APPLY_V"
[7] "MINING_DATA_BUILD_V" "MINING_DATA_TEST_V" "MINING_DATA_TEXT_APPLY_V"
[10] "MINING_DATA_TEXT_BUILD_V" "MINING_DATA_TEXT_TEST_V"
ore.sync
The ore.sync() command is used to synchronise the meta-data about the objects in the oracle schema with the ORE environment. When no parameters are used in the command then all the meta-data for the tables and views are synchronised.
> ore.sync()
There are a a few variants for this command. These can include a specified list of tables and specifying a schema. These become increasingly important as the number of objects (tables and views) increase in your schema. Realistically as your analytical environment grows so will the number of objects. Therefore it the length of time it takes the ore.sync() command to run will start to take longer and long. So instead of synchronising all of the object, you can only synchronising the objects that you need.
The following command synchronises the meta-data for the listed tables.
ore.sync(table = c("MINING_DATA_BUILD_V", "MINING_DATA_TEST_V", "INSUR_CUST_LTV_SAMPLE"))
If you want to synchronise objects from another schema you can specify the schema name. Only the objects that you have privileges on will be synchronised.
ore.sync("SH")
ore.attach
After you have run the ore.sync() command then you can add the objects that were synchronised to the search path of what R objects you can access and use. To do this you need to run the ore.attach() command.
> ore.sync()
> ore.attach()
The ORE objects are added at position 2. This means that they are listed after the local R workspace objects and before all previously attached packages and environment. You cannot change the position to be 1, but you can change it to 3
> ore.attach("DMUSER", 3)
NOTE: If you use the all=TRUE in your ore.connect command than the connection will automatically execute the ore.sync() and the ore.attach() commands.
ore.rm
The ore.rm() command can be used to remove an object from the R search space. This does not remove or delete the object from your ORE schema.
> ore.rm("MINING_DATA_BUILD_V")
ore.disconnect
After you have finished all your ORE work you will need to disconnect from your schema. To do this you can use the ore.disconnect function. As part of the ore.disconnect function all temporary objects created during the session will be removed/deleted from the database / your schema. The ore.disconnect function does return a value. If you are unsure if the disconnect has worked then you can use the ore.is.connected command.
> ore.disconnect() > ore.is.connected() [1] TRUE
OREShowDoc()
The OREShowDoc() command will open up a web browser and will display the home page of the ORE documentation. This will be the ORE documentation on you machine. My test machine is a VM with ORE installed on it, so the home page for the will be based on ORE installed directories on the server. My laptop is a Mac and ORE is currently not supported on the Mac, so I was not able to test how this works on the client.
> OREShowDoc
