Bad tasting Pie

The material looks great for “Oracle R”. So, I thought I’d get a few books and try a quick lab for myself.

The books covered data loading, data manipulation, statics stuff I’ve either forgotten or never learned, some plotting and a host of other useful stuff to be discovered.

So to focus my learning and usefulness I quickly eliminated data loading and data manipulation since I have tons of data, and already have a rich pl/sql language to do that anyway. So maybe some data retrieval from the database and a plot.

At several of my recent clients auditing has been a top priority, so how about a pie chart showing the status of all users.

I’m using the default user “RQUSER” and played around with a few of the demos. So far I like the “RStudio” app.

and find:


> ore.ls()
[1] "DF1" "DF2" "IRIS" "IRIS_TABLE"

So, no visibility to dba_users, what I’d normally pick.

Create local maybe?

GRANT SELECT ANY TABLE TO rquser
/
create synonym users for sys.dba_users
/

And no luck, local synonyms don’t show up on the R tools. And I did grant select any table and no luck with ore.ls() either. So more reading.

How about creating a local table so I can move passed this and have some pie – charts.


create table users as select * from dba_users;

Progress:
> ore.ls()
[1] “DF1” “DF2” “IRIS” “IRIS_TABLE” “USERS”

Now for my pie, I’d like to show a group by account status. Well I can’t directly.

I type:
> head(USERS)

and it shows my table correctly, but don’t see any clear functions to get there. I think they are there, but I just haven’t discovered them yet. I do see some good filtering, but I’m used to writing a good query and being done with it. So, moving on. Next plan, create a table with the right columns, need my pie.


create table users as select account_status, count(*) status_count from dba_users group by account_status;

> ore.ls()
[1] "DF1" "DF2" "IRIS" "IRIS_TABLE" "USERS"

Progress...
head(USERS)
ACCOUNT_STATUS STATUS_COUNT
1 LOCKED 3
2 OPEN 7
3 EXPIRED & LOCKED 7

Now for Pie:
pie(USERS$STATUS_COUNT,labels=USERS$ACCOUNT_STATUS)
Error in seq.int(x[i], x[i + 1], length.out = n) :
'length.out' must be a non-negative number

Oops, Houston we have a problem.

So what’s going on here?

Let’s try something different, using one of the more normal examples (without the Oracle part).


&GT; slices <- c(3,7,7)
&GT; lbls <- c("LOCKED","OPEN","EXPIRED & LOCKED")
&GT; pie(slices,labels=lbls)

Success with the data going in directly:
Rplot

So what’s up?

I know that Oracle uses Oracle uses data frames to store some of it’s objects and that the R packages are overloaded so let’s check the classes.


> class(slices)
[1] "numeric"
> class(USERS$STATUS_COUNT)
[1] "ore.numeric"
attr(,"package")
[1] "OREbase"

Trying the plot package:

plot(USERS$STATUS_COUNT,USERS$ACCOUNT_STATUS)

Rplot_PLOT

That worked, adding values instead of labels for each number, I’m sure there’s a way to change that, but the important thing is that it accepted my values with no errors and produced a plot.

OK, well lots of learning, but no sweet pie. Too much for Christmas anyway.

Installing Oracle R

I’m trying to learn R, particularly Oracle R. Setting it up on a W7 laptop first and see what can be learned. Then possibly some bigger machines, later.

Pulled a getting started doc from Oracle’s website http://www.oracle.com/technetwork/database/options/advanced-analytics/r-enterprise/index.html
“ore-trng1-gettingstarted-1501628.pdf” The whole series of training documents are helpful to get started.

Adding a user went fine as well running “rquser.sql” (provided by Oracle), but I prefer a less cryptic script. So I replaced some of the code in rquser.sql for the next time I run it:

-- create RQUSER user
create user &&new_user_name identified by &&new_password
default tablespace &&new_default_tablespace
temporary tablespace &&new_temp_tablespace
quota &&new_quota on &&new_default_tablespace;

-- grant privileges
grant create session, rqrole
to &&new_user_name;

-- BUG: the following grant does not work when granted to RQROLE
grant execute on rqsys.rqGroupEvalImpl to &&new_user_name;

alter session set current_schema = &&new_user_name;

Not sure why you’d grant quota to anything other than the default tablespace on Oracle XE.

Next, started the two R clients. The first was the generic R 64-bit version and ran a few of the demo packages. Then I tried the RStudio and compared the graphics pretensions between the two. Caught me off guard when it asked me what tool to use to open the demo file, from RStudio, of course RStudio, it worked.

I did get the error:

Error: cannot change value of locked binding for 'all'

Going back into the database, found I had to grant execute on SYS.DBMS_SYSTEM to rqsys for rqsys.libPath.

Overall the instructions provided by Oracle are good. They worked perfectly where I followed them.

Now on to more.

Think I might learn Oracle R

With the Big Data explosion happening, coming or whatever it’s doing. I think it’s time for some new skills. I think I might learn Oracle R. Got some basic hadoop knowledge, but realize the next gen Geeks may be more supermen with multiple skills of statistics, hardware, software, business analysts, network engineers, system engineers and more. Time to learn some more skills and tricks.

I’ve seen the tech world make some drastic changes before. Mainframe to VAX VMS, then Unix, then the rise of the Personal Computer, phone, pad. Central Computing to Client Server… Oracle 5,6,7,8i,9i,10g,11g, 12… Central Data Center to Matrix workers. …makes my brain hurt thinking all the times I’ve had to learn a new technology just to keep current. Pondering some “Expanding/Contracting Theories of Computing” … too deep. Whatever is happening, I’m hearing more rumors of some solid changes toward Big Data, so it’s that time again and I’d better get ready.

Christmas just passed, and I attempted to teach my boys the many important things like “Baby Jesus”, and it’s complicated. Some change and some stay the same. There are things that stay the same like God and His adult son, but technology changes. So with my laptop, Doritos, and a “red-hot smokin’ wife” at my side, it’s time to grow again. Next up, “ORACLE R”.