The following is the second set of demo code from my video on using R in the Oracle Database. Check out the video before using the following code. The blog post for the video will be updated to contain links to all blog posts that have the various demo code.
The following code gives a very quick demonstration of using the RORACLE R package to access the data in your Oracle schema. ROracle has a number of advantages over using RJDBC and most of the advantages are about the performance improvements. Typically when using ROracle you will see a many fold improvement with selecting data and moving it to your R client, processing data in the database and also writing data back to the Oracle Database. In some tests you can see a 7 times improvement in performance over RJDBC. Now that is a big difference.
But the problem with ROracle is that it is only available on certain platforms/OS. For example it is not officially available for the Mac. But if you google this issue carefully you will find unofficial ways over coming this problem.
ROracle is dependent on Oracle Client. So you will need to have Oracle Client installed on you machine and have it available on the search path.
When you have Oracle Client installed and the ROracle R package installed you are ready to start using it.
So here is the demo code from the video.
> library(ROracle)
> drv <- dbDriver("Oracle")
> # Create the connection string
> host <- "localhost"
> port <- 1521
> sid <- "orcl"
>connect.string <- paste("(DESCRIPTION=”, "(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
> "(CONNECT_DATA=(SID=", sid, ")))", sep = "")
> con <- dbConnect(drv, username = "dmuser", password = "dmuser",dbname=connect.string)
> rs <- dbSendQuery(con, "select view_name from user_views")
> # fetch records from the resultSet into a data.frame
> data <- fetch(rs)
> # extract all rows
> dim(data)
[1] 6 1
> data
VIEW_NAME
1 MINING_DATA_APPLY_V
2 MINING_DATA_BUILD_V
3 MINING_DATA_TEST_V
4 MINING_DATA_TEXT_APPLY_V
5 MINING_DATA_TEXT_BUILD_V
6 MINING_DATA_TEXT_TEST_V
> dbCommit(con)
> dbClearResult(rs)
> dbDisconnect(con)
Hi Brendan,
ReplyDeleteCould you please help me out in figuring out issue for the below error.
ORE 1.5 on WINDOWS platform
ORE> library(ROracle)
ORE> drv <- dbDriver("Oracle")
Error in .oci.Driver(.oci.drv(), interruptible = interruptible, unicode_as_utf8 = unicode_as_utf8, :
Error while trying to retrieve text for error ORA-01804
The example you have given above is not using Oracle R Enterprise (ORE)
ReplyDeleteWhen you use
> libary(ROracle)
you are using the R package ROracle. There are two things to need to ensure when using this packages. The first is that you have installed the ROracle package in your R environment (make sure you are using one of the supported platforms, for example MAC is not supported)
The second is that you need to make sure that the Oracle Client software is installed and configured. I would say this is the bit that you are missing.