con <- dbConnect(Oracle(), "scott", "tiger")
if (dbExistsTable(con, "FOO", "SCOTT"))
dbRemoveTable(con, "FOO")
foo <- dbReadTable(con, "EMP")
row.names(foo) <- foo$EMPNO
foo <- foo[,-1]
dbWriteTable(con, "FOO", foo, row.names = TRUE)
dbWriteTable(con, "FOO", foo, row.names = TRUE, overwrite = TRUE)
dbReadTable(con, "FOO", row.names = 1)
dbGetQuery(con, "delete from foo")
dbWriteTable(con, "FOO", foo, row.names = TRUE, append = TRUE)
dbReadTable(con, "FOO", row.names = 1)
dbRemoveTable(con, "FOO")
dbListTables(con)
dbListFields(con, "EMP")
if (dbExistsTable(con, "RORACLE_TEST", "SCOTT"))
dbRemoveTable(con, "RORACLE_TEST")
# example of POSIXct usage
# A table is created using:
createTab <- "create table RORACLE_TEST(row_num number, id1 date,
id2 timestamp, id3 timestamp with time zone,
id4 timestamp with local time zone )"
dbGetQuery(con, createTab)
# Insert statement
insStr <- "insert into RORACLE_TEST values(:1, :2, :3, :4, :5)";
# Select statement
selStr <- "select * from RORACLE_TEST";
# Insert time stamp without time values in POSIXct form
x <- 1;
y <- "2012-06-05";
y <- as.POSIXct(y);
dbGetQuery(con, insStr, data.frame(x, y, y, y, y));
# Insert date & times stamp with time values in POSIXct form
x <- 2;
y <- "2012-01-05 07:15:02";
y <- as.POSIXct(y);
z <- "2012-01-05 07:15:03.123";
z <- as.POSIXct(z);
dbGetQuery(con, insStr, data.frame(x, y, z, z, z));
# Insert list of date objects in POSIXct form
x <- c(3, 4, 5, 6);
y <- c('2012-01-05', '2011-01-05', '2013-01-05', '2020-01-05');
y <- as.POSIXct(y);
dbGetQuery(con, insStr, data.frame(x, y, y, y, y));
dbCommit (con)
# Selecting data and displaying it
res <- dbGetQuery(con, selStr)
res[,1]
res[,2]
res[,3]
res[,4]
res[,5]
df <- data.frame(A=c(0,1,NaN,4), B=c(NA, 2,3,NaN))
con <- dbConnect(Oracle(), "scott", "tiger")
dbWriteTable(con,"TEST", df, row.names = FALSE, ora.number=FALSE)Run the code above in your browser using DataLab