Oracle Magazine September/October 2000

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:

• Tom Kyte's article is on Tips for Migrating, Indexing and Using Packaged Procedures. In his article he gives some tips for migrating to Oracle 8.1i. He also discusses some scenarios around creating (or not) indexes on foreign keys. He also looks at the scenario of compiling linked procedures and how the use of packages avoids the identified issues.
• Do you remember the Internet File System. There was an article that gave an overview of this that was available in Oracle 8i and was capable of managing over 150 different file types.
• Autodesk releaseed OnSite, an enterprise solution for bringing design and location based information to the point of work via mobile devices. Autodesk On Site used Oracle 8i Lite and the Palm OS platform to provide an interactive, two way communication environment between the mobile worker and the overall decision support system.
• The Oracle Academic Initiative began in 1997. In 2000 Oracle donated software licences, support services and Oracle training material to 17 educational institutions valued at $60 million
• There was page after page, after page of announcements and news from various Oracle Partners.
• Douglas Scherer gives the first part of an article that looks at how you can use Oracle 8i interMedia for managing and deploying content rich data on the internet.
• Managing Your Resources looks at some of the new Oracle 8i EE helps DBAs to define plan, assign users to groups and prioritise resource allocations.
• With the release of Oracle 8.1.6 came the new Statspack. Connie Dialeris and Graham Wood give an overview of the main features of Statspack, providing some guidance on how to use it in a proactive manner and gives a step-by-step guide to how you can trouble shoot performance problems with Statspack.
• The final article was on Oracle Warehouse Builder (OWB). This was an overview type of article and gave an overview of the main components and gives some guidelines for setting up some different types of integration.

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.

ORE Getting Connected: ore.connect and other commands

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")

Disconnecting & Other things

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

If you exit from your R session or application and implicit ore.disconnect will be issued. But it is always good practice to issue this command yourself.

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

OUG Finland (Harmony) 2014

I'm back a few days now after an eventful OUG Finland Conference. It was a great 2 days of Oracle techie stuff in one of the best conference locations in the world.

The conference kind of started on the Wednesday. It seemed like most of the speakers from across Europe and USA were getting into Helsinki around lunch time. Heli had arranged to be a tour guide for the afternoon and took us to see some of the sights around Helsinki, ending up at dinner at a Viking restaurant. At this we got to meet up with some of the other speakers.

We got to try the local Tar ice-cream. Let's just say it was an experience :-)

The conference kicked off on the Thursday morning at a nature reserve (Haltia) about 30 minutes outside of Helsinki. I'm sure you have seem some of the photos that all of us were sharing on twitter. Heli did the opening welcome and got to show off how good her English is and how easily she can switch between Finnish and English (you had to be there). Each day consisted of 6 tracks and we also had some keynotes too. I think the highlight for most people on the first day was the keynote by James Morle. If you were not there, then hopefully he will be invited to give it at other conferences around the world.

I did my usual thing of tweeting throughout the conference along with most of the other presenters, ACEs and ACE Directors.

My first presentation of the conference was my Sentiment Analysis using Oracle Data Mining. I had a decent enough attendance at this considering the glorious sunshine we had and also who was also presenting at the same time.

That evening there was the conference BBQ and we were entertained by the a band consisting of Oracle Finland employees. When all of the entertainment was finished it was time for some people to get the bus back to their hotel in Helsinki. But for 15 of us we headed off for an overnight camping in the local forest. Many thanks to Heli and Ann for arranging this and Ollie for setting up the camp and looking after us. The camp was a bit of fun but the mosquitoes were a bit of a issue for me. Lets just say 'They love me'

Yes the safety briefing did talk about bears

The next morning we got back in to the conference centre. After the keynote from Graham Wood (who was one of the campers), I had my second presentation on using ORE and how to get started with it. I was very surprised with the attendance, in that there was perhaps twice the number at it that I was hoping to have. This presentation ran on a little longer than hoped and it is more suited to a 1 hour slot than a 45 minute slot.

After that my presenting duties were over and I got to enjoy many of the presentations during the rest of the day. One of these was the Standard Edition Round table organised by Philippe Fierens, Jan Karrmans and Ann Sjokvit. There was lots of great discussion at this round table and I really hope they get to host this at other conferences.

Did I mention I got to meet Chris Date. Did I mention I got to meet Chris Date :-)

As the conference came to a close the committee made sure that everyone had a small gift before going home. For all of those that were left waiting for the bus back into Helsinki there was one last (or several) jumps to do. Again the photos are on twitter, etc.

This was a great conference that had attendees (not just speakers) from a number of countries across Europe. I enjoyed the conference and getting to meet some old and new friends. Hopefully next year I will be able to fit OUG Finland into my calendar and so should you.

As Heli says "I'm Finnish and this is the end"

ore.parallel

In ORE there are a number ways to get you R scripts to run in parallel in the database. One way is to enable the Parallel option in ORE. This is what will be shown in this post. There are other methods of running various ORE commands/scripts in parallel. With these the scripts are divided out and several parallel R processes are started on the server.

But what if you want to use the database parallel feature on some of your ORE other commands?

Why would you want to do this?

Well the main answer is that you might want to use the parallel option of the database for the creation on objects (tables etc) and for selecting and manipulating the data in the database.

How can you enable your ORE connection to use the in-database parallel feature?

ORE 1.4 has a new option that enables the parallel option for your ORE connection in the database. This option is called ore.parallel.

When you enable or set the ore.parallel option, it seems to be the equivalent of running the following:

ALTER SESSION ENABLE PARALLEL DDL;

ALTER SESSION ENABLE PARALLEL DML;

ALTER SESSION ENABLE PARALLEL QUERY;

The exact details is a little unclear, but it seems to be above commands.

The following commands illustrates some options for using the ore.parallel option.

> #

> # Check to see if the ore.parallel is enabled for your ORE connection

> options("ore.parallel")

$ore.parallel

NULL

The NULL returned value tells us that your ORE connections does not have the Parallel option enabled. If the schema had Parallel enabled by default then we would have have a response of TRUE.

The following command turns on the Parallel option for your ORE connection / schema.

> options("ore.parallel" = TRUE)

> options("ore.parallel")

$ore.parallel

[1] TRUE

When the Parallel option is enabled (TRUE above) the database will use the degree of parallel that is set as default for the schema or the degree of parallel that is defined for the table when it is being used in your ORE commands.

You can changed the degree of parallelism by passing the required degree as a value to the ore.parallel command. In the following, the degree of parallelism is set to 8. We then as ORE what the degree is set to and it tells us that it is 8. So it was set correctly.

> options("ore.parallel" = 8)

> options("ore.parallel")

$ore.parallel

[1] 8