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]
# insert data in Date format
a<-as.Date("2014-01-01")
dbWriteTable(con, 'TEMP', data.frame(a), date = TRUE)
# using attribute to map NCHAR, CLOB, BLOB, NCLOB columns correctly in
# dbWriteTable
str1 <- paste(letters, collapse="")
lstr1 <- paste(rep(str1, 200), collapse="")
raw.lst <- vector("list",1)
lraw.lst <- vector("list",1)
raw.lst[[1L]] <- charToRaw(str1)
lraw.lst[[1L]] <- rep(charToRaw(str1), 200)
a <- as.POSIXct("2014-01-01 14:12:09.0194733")
b <- as.POSIXct("2014-01-01 14:12:09.01947")
test.df <- data.frame(char=str1, nchar=str1, varchar=str1, clob=lstr1,
nclob=lstr1, stringsAsFactors=FALSE)
test.df$raw.typ <- raw.lst
test.df$blob <- lraw.lst
test.df$char_max <- str1
test.df$raw_max.typ <- raw.lst
test.df$nvchar <- str1
test.df$nvchar_max <- str1
test.df$date_tz <- a
test.df$date_ltz <- b
# adding attributes
attr(test.df$clob, "ora.type") <- "CLOB"
attr(test.df$blob, "ora.type") <- "BLOB"
attr(test.df$nclob, "ora.type") <- "CLOB"
attr(test.df$nclob, "ora.encoding") <- "UTF-8"
attr(test.df$char_max, "ora.maxlength") <- 3000
attr(test.df$raw_max.typ, "ora.maxlength") <- 1000
attr(test.df$nvchar, "ora.encoding") <- "UTF-8"
attr(test.df$nvchar_max, "ora.encoding") <- "UTF-8"
attr(test.df$nvchar_max, "ora.maxlength") <- 1500
attr(test.df$char, "ora.type") <- "CHAR"
attr(test.df$date_tz, "ora.type") <- "timestamp with time zone"
attr(test.df$date_ltz, "ora.type") <- "timestamp with local time zone"
attr(test.df$nchar, "ora.type") <- "CHAR"
attr(test.df$nchar, "ora.encoding") <- "UTF-8"
attr(test.df$date_tz, "ora.fractional_seconds_precision") <- 9
R> # displaying the data frame
R> test.df
char nchar
1 abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz
varchar
1 abcdefghijklmnopqrstuvwxyz
clob
1 abcdefghijklmnopqrstuvwxyz...
nclob
1 abcdefghijklmnopqrstuvwxyz...
raw.typ
1 61, 62, 63, 64, 65, 66, 67, 68, 69, 6a, 6b, 6c, 6d, 6e, 6f, 70, 71, 72, 73,
74, 75, 76, 77, 78, 79, 7a
blob
1 61, 62, 63, 64, 65, 66, 67, 68, 69, 6a, 6b, 6c, 6d, 6e, 6f, 70, 71, 72, 73,
74, 75, 76, 77, 78, 79, 7a,...
char_max
1 abcdefghijklmnopqrstuvwxyz
raw_max.typ
1 61, 62, 63, 64, 65, 66, 67, 68, 69, 6a, 6b, 6c, 6d, 6e, 6f, 70, 71, 72, 73,
74, 75, 76, 77, 78, 79, 7a
nvchar nvchar_max
1 abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz
date_tz date_ltz
1 2014-01-01 14:12:09.019473 2014-01-01 14:12:09.01946
dbWriteTable(con, name="TEST_TAB", value=test.df)
res <- dbReadTable(con, name="TEST_TAB")
R> res
char
1 abcdefghijklmnopqrstuvwxyz
nchar
1 abcdefghijklmnopqrstuvwxyz
varchar
1 abcdefghijklmnopqrstuvwxyz
clob
1 abcdefghijklmnopqrstuvwxyz...
nclob
1 abcdefghijklmnopqrstuvwxyz...
raw.typ
1 61, 62, 63, 64, 65, 66, 67, 68, 69, 6a, 6b, 6c, 6d, 6e, 6f, 70, 71, 72, 73,
74, 75, 76, 77, 78, 79, 7a
blob
1 61, 62, 63, 64, 65, 66, 67, 68, 69, 6a, 6b, 6c, 6d, 6e, 6f, 70, 71, 72, 73,
74, 75, 76, 77, 78, 79, 7a,...
char_max
1 abcdefghijklmnopqrstuvwxyz
raw_max.typ
1 61, 62, 63, 64, 65, 66, 67, 68, 69, 6a, 6b, 6c, 6d, 6e, 6f, 70, 71, 72, 73,
74, 75, 76, 77, 78, 79, 7a
nvchar nvchar_max
1 abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz
date_tz date_ltz
1 2014-01-01 14:12:09.019473 2014-01-01 14:12:09.01946
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