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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s