The following is the third 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 is illustrates some simple examples of using Oracle R Enterprise. In these example you will see how to connect to the Oracle Database, how to query and process some of the tables and views in the Oracle Database, how to check that you are working with objects in the database, how to move data to the database and query it.
> library(ORE)
> # ore.connect(user="rquser", sid="orcl", host="localhost", password="rquser", port=1521, all=TRUE);
> ore.connect(user="dmuser", sid="orcl", host="localhost", password="dmuser", port=1521, all=FALSE);
> # Test the connection
> ore.is.connected()
[1] TRUE
> # List all the tables and views
> ore.ls()
character(0)
> # Use ore.sync to only include the tables and views listed
> ore.sync()
> ore.ls()
[1] "DEMO_R_APPLY_RESULT" "DEMO_R_TABLE" "INSUR_CUST_LTV_SAMPLE" "MINING_DATA_APPLY"
[5] "MINING_DATA_APPLY_V" "MINING_DATA_BUILD_V" "MINING_DATA_TEST_V" "MINING_DATA_TEXT_APPLY_V”
[9] "MINING_DATA_TEXT_BUILD_V" "MINING_DATA_TEXT_TEST_V"
> # Disconnect and reattached with no meta-data sync
> ore.disconnect()
> ore.connect(user="dmuser", sid="orcl", host="localhost", password="dmuser", port=1521, all=FALSE);
> ore.sync(table = c("MINING_DATA_BUILD_V", "MINING_DATA_TEST_V", "INSUR_CUST_LTV_SAMPLE"))
> ore.ls()
[1] "INSUR_CUST_LTV_SAMPLE" "MINING_DATA_BUILD_V" "MINING_DATA_TEST_V"
> # Check for the existance of a table or view
> ore.exists("MINING_DATA_BUILD_V")
[1] TRUE
> # list the objects in the DMUSER schema
> ore.ls("DMUSER")
[1] "INSUR_CUST_LTV_SAMPLE" "MINING_DATA_BUILD_V" "MINING_DATA_TEST_V"
> #
> # Load data from a file into a new table
> ore.exists("DEMO_R_TABLE")
[1] TRUE
> ore.drop(table='DEMO_R_TABLE')
> ore.ls()
[1] "INSUR_CUST_LTV_SAMPLE" "MINING_DATA_BUILD_V" "MINING_DATA_TEST_V"
> titanic <- read.table("c:/R/titanic2.txt", header=T, sep="\t")
> ore.create(titanic, table="DEMO_R_TABLE")
> tData <- ore.get("DEMO_R_TABLE")
> head(tData)
NAME PCLASS AGE SEX SURVIVED
1 Fynney, Mr Joseph J 2nd 35 male 0
2 Gale, Mr Harry 2nd 35 male 0
3 Gale, Mr Shadrach 2nd 38 male 0
4 Garside, Miss Ethel 2nd 24 female 1
5 Gaskell, Mr Alfred 2nd 16 male 0
6 Gavey, Mr Lawrence 2nd 26 male 0
> # Use ORE to pull data from the Database to local R
> # ore.pull -- United States of America
> mdbv <- ore.get("MINING_DATA_BUILD_V")
> mdbv_data <- ore.pull(mdbv)
Warning message:ORE object has no unique key - using random order
> head(mdbv_data,3)
CUST_ID CUST_GENDER AGE CUST_MARITAL_STATUS COUNTRY_NAME CUST_INCOME_LEVEL EDUCATION OCCUPATION
1 101501 F 41 NeverM United States of America J: 190,000 - 249,999 Masters Prof.
2 101502 M 27 NeverM United States of America I: 170,000 - 189,999 Bach. Sales
3 101503 F 20 NeverM United States of America H: 150,000 - 169,999 HS-grad Cleric.
HOUSEHOLD_SIZE YRS_RESIDENCE AFFINITY_CARD BULK_PACK_DISKETTES FLAT_PANEL_MONITOR HOME_THEATER_PACKAGE
1 2 4 0 1 1 1
2 2 3 0 1 1 0
3 2 2 0 1 0 0
BOOKKEEPING_APPLICATION PRINTER_SUPPLIES Y_BOX_GAMES OS_DOC_SET_KANJI
1 1 1 0 0
2 1 1 1 0
3 1 1 1 0
> class(mdbv_data)
[1] "data.frame”
> summary(mdbv_data)
