In a previous blog post I posted a video on using R with the Oracle Database and using Oracle R Enterprise. This is a part 1 extension of that blog post that gives the first set of demo code.
This first set of demonstration code is for using RJDBC to connect to the Oracle Database. Using RJDBC relies on using the JDBC jar file for Oracle. It is easily found in various installations of Oracle products and will be called something like ojdbc.jar. I like to take a copy of this file and place it in the root/home directory.
> library(RJDBC)
> # Create connection driver and open
> connectionjdbcDriver <- JDBC(driverClass="oracle.jdbc.OracleDriver", classPath="c:/ojdbc6.jar")
> jdbcConnection <- dbConnect(jdbcDriver, "jdbc:oracle:thin:@//localhost:1521/orcl", "dmuser", "dmuser")
> #list the tables in the schema
> #dbListTables(jdbcConnection)
> #get the DB connections details - it get LOTS of info - Do not run unless it is really needed
> dbGetInfo(jdbcConnection)
> # Query on the Oracle instance name.
> #instanceName <- dbGetQuery(jdbcConnection, "SELECT instance_name FROM v$instance")
TABLE_NAME1
1 INSUR_CUST_LTV_SAMPLE2
2 OUTPUT_1_2
> #print(instanceName)tableNames <- dbGetQuery(jdbcConnection, "SELECT table_name from user_tables where
table_name not like 'DM$%' and table_name not like 'ODMR$%'")
> print(tableNames)
> viewNames <- dbGetQuery(jdbcConnection, "SELECT view_name from user_views")print(viewNames)
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
> v <- dbReadTable(jdbcConnection, "MINING_DATA_BUILD_V")
> names(v)
[1] "CUST_ID" "CUST_GENDER" "AGE"
[4] "CUST_MARITAL_STATUS" "COUNTRY_NAME" "CUST_INCOME_LEVEL"
[7] "EDUCATION" "OCCUPATION" "HOUSEHOLD_SIZE"
[10] "YRS_RESIDENCE" "AFFINITY_CARD" "BULK_PACK_DISKETTES"
[13] "FLAT_PANEL_MONITOR" "HOME_THEATER_PACKAGE" "BOOKKEEPING_APPLICATION”
[16] "PRINTER_SUPPLIES" "Y_BOX_GAMES" "OS_DOC_SET_KANJI"
> dim(v)
[1] 1500 18
> summary(v)
CUST_ID CUST_GENDER AGE CUST_MARITAL_STATUS COUNTRY_NAME
Min. :101501 Length:1500 Min. :17.00 Length:1500 Length:1500
1st Qu.:101876 Class :character 1st Qu.:28.00 Class :character Class :character
Median :102251 Mode :character Median :37.00 Mode :character Mode :character
Mean :102251 Mean :38.89
3rd Qu.:102625 3rd Qu.:47.00
Max. :103000 Max. :90.00
CUST_INCOME_LEVEL EDUCATION OCCUPATION HOUSEHOLD_SIZE YRS_RESIDENCE
Length:1500 Length:1500 Length:1500 Length:1500 Min. : 0.000
Class :character Class :character Class :character Class :character 1st Qu.: 3.000
Mode :character Mode :character Mode :character Mode :character Median : 4.000
Mean : 4.089
3rd Qu.: 5.000
Max. :14.000
> hist(v$RESIDENCE)
> hist(v$AGE)
> dbDisconnect(jdbcConnection)
Make sure to check out the other demonstration scripts that are shown in the video.
