Eric Guo's

in HTML5, JavaScript, Ruby & Rails, Python, and Cloud MES!

Fetch Oracle DATA in R Language Using ROracle/RODBC and Make a Bubble Charts


Inspired by Nathan Yau’s tutorials - How to Make Bubble Charts, fetch the data directly from Oracle.

Using ROracle to link, notice ROracle can be installed on Linux or windows, but if you are in Windows, you have to download ROracle from Oracle R enterprise site instead of building yourself.

drv <- dbDriver("Oracle")
con <- dbConnect(drv, username="user", password="passwd", dbname="tnsname")
res <- dbSendQuery(con, "
SELECT pc.packagecategoryname Package,
replace(replace(,'nm',''),'NM','') Tech,
replace(p.densitycode, 'G','') Density, count(p.qaddevice) PN_Number
FROM product p
INNER JOIN a_packagecategory pc ON pc.packagecategoryid=p.packagecategoryid
INNER JOIN a_productlevel ptl ON ptl.productlevelid=p.productlevelid
WHERE p.isavailable=1
AND ptl.productlevelname IN ('54-82')
AND p.densitycode NOT IN ('512M')
AND NOT IN ('-')
AND pc.packagecategoryname like 'INAND_'
GROUP BY pc.packagecategoryname,
replace(p.densitycode, 'G','')
data <- fetch(res, n=-1)
radius <- sqrt(data$PN_NUMBER/ pi)
symbols(data$DENSITY, data$TECH, circles=radius, inches=0.35,
main = "Number of PN per nano technology and density",
fg="white", bg="red", xlab="Density", ylab="Technology")
text(x=data$DENSITY, y=data$TECH, labels=data$PACKAGECATEGORY, cex=0.5)
using below if you are in Windows
db <- odbcConnect(dsn="mesprd", uid="mesprd", pwd="wip24ux")
data <- sqlQuery(db, "SELECT SYSDATE from DUAL")