Work Recipes et al

Load few columns from file to Oracle Load few columns from file to Oracle Problem: Load a text file directly into oracle, using R Previous attempt: Tried to load the file into a data frame, but RStudio started to hang. library(ROracle) con <- dbConnect(drv, username ="<dbuser>", password = "<dbpassword>", dbname="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=<host>)(Port=<port>))(CONNECT_DATA=(SERVICE_NAME=<db_service>)))") dbWriteTable(con,"<TABLENAME>",data.frame(gsub("\\s*,$", "",as.matrix( read.fwf( "C:\\Users\\<username>\\<filename>", widths = c(18,31,31,31,12,14,11,6,26,15,1), comment.char = "",stringsAsFactors = FALSE, skip = 1 )[,c(1,7)] ))),append = TRUE) Comments on the Solution: using ROracle read.fwf outputs a data frame as.matrix converts the dataframe into a matrix for gsub gsub outputs a m...

In RStudio: drv <- dbDriver("Oracle") con <- dbConnect(drv, username ="user", password = "password", dbname="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=host)(Port=port))(CONNECT_DATA=(SERVICE_NAME=service_name)))") df <-  read.csv(url("http://samplecsvs.s3.amazonaws.com/Sacramentorealestatetransactions.csv")) dbWriteTable(con,"TEST",df) *NB : Table name is case sensitive. if you use lowercase, you will have to access the table using double quotes (select * from "Test") Created table description in SQLPlus desc TEST Name      Null Type            --------- ---- --------------  street         VARCHAR2(4000)  city           VARCHAR2(4000)  zip            NUMBER(38)      state          VARCHAR2(4000)  beds           NUMBER(38)      ba...

Download ROracle http://www.oracle.com/technetwork/database/database-technologies/r/roracle/downloads/index.html set the following Windows variables, according to your installation OCI_INC = C:\Oracle11_201\product\11.2.0\dbhome_1\OCI\include OCI_LIB64 = C:\Oracle11_201\product\11.2.0\dbhome_1\BIN Install ROracle in a CMD window C:\Users\user\Documents>"C:\Program Files\R\R-3.2.2\bin\r" CMD INSTALL ROracle_1.2-1.zip In RStudio > library(ROracle) Error: package ‘DBI’ required by ‘ROracle’ could not be found > install.packages("DBI") Installing package into ‘C:/Users/karatrj1/Documents/R/win-library/3.2’ (as ‘lib’ is unspecified) trying URL 'https://cran.rstudio.com/bin/windows/contrib/3.2/DBI_0.3.1.zip' Content type 'application/zip' length 162723 bytes (158 KB) downloaded 158 KB package ‘DBI’ successfully unpacked and MD5 sums checked The downloaded binary packages are in ...