## Not run:
# 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
# ## End(Not run)
## Not run:
# 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)
# ## End(Not run)
Run the code above in your browser using DataLab