# NOT RUN { # } # 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 # } # 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) # }
Run the code above in your browser using DataCamp Workspace