Feature request for ROracle: option to create case-insensitive table names with dbWriteTable()

User_UV83W

I use ROracle package heavily these days. One thing that bites me for quite some time is the case-sensitivity of my tables created by dbWriteTable() method. I understand that this may be a good default behavior and consistent with some best-praciteces guidelines, but I would appreciate if this there was an optional logical argument to allow case-insensitivity.

Best,

Vaclav

Sherry Lamonica-Oracle

Vaclav,

An enhancement will be submitted for supporting case sensitivity with a local argument.  For now, here's an example that shows how this can e accomplished:

options(digits.secs=6)

options(error = expression(NULL))

Sys.setlocale("LC_ALL", "C")

library(ROracle)

con<-dbConnect(Oracle(), 'user', 'pass', dbname="PDBNAME")

# creating use cases for testing

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_max.df <- data.frame(char=str1,

                          stringsAsFactors=FALSE)

test_max.df$clob <- lstr1

test_max.df$blob <- lraw.lst

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

# checking the attributes assigned

attr(test.df$clob, "ora.type")

attr(test.df$blob, "ora.type")

attr(test.df$nclob, "ora.type")

attr(test.df$nclob, "ora.encoding")

attr(test.df$char_max, "ora.maxlength")

attr(test.df$raw_max.typ, "ora.maxlength")

attr(test.df$nvchar, "ora.encoding")

attr(test.df$nvchar_max, "ora.encoding")

attr(test.df$nvchar_max, "ora.maxlength")

attr(test.df$char, "ora.type")

attr(test_max.df$char, "ora.type")

attr(test_max.df$char, "ora.maxlength")

attr(test.df$date_tz, "ora.type")

attr(test.df$date_ltz, "ora.type")

attr(test.df$nchar, "ora.type")

attr(test.df$nchar, "ora.encoding")

attr(test.df$date_tz, "ora.fractional_seconds_precision")

attr(test_max.df$clob, "ora.type")

attr(test_max.df$blob, "ora.type")

dbWriteTable(con, name="\'test_TAB\'", value=test.df)

res <- dbReadTable(con, name="\'test_TAB\'")

# verifying the result column fields

> print(dbGetInfo(res1, what = 'fields'))

$fields

          name    Sclass                           type  len precision scale nullOK

1         char character                           CHAR 2000         0     0   TRUE

2        nchar character                          NCHAR 1000         0     0   TRUE

3      varchar character                       VARCHAR2 4000         0     0   TRUE

4         clob character                           CLOB   NA         0     0   TRUE

5        nclob character                          NCLOB   NA         0     0   TRUE

6      raw.typ       raw                            RAW 2000         0     0   TRUE

7         blob       raw                           BLOB   NA         0     0   TRUE

8     char_max character                       VARCHAR2 3000         0     0   TRUE

9  raw_max.typ       raw                            RAW 1000         0     0   TRUE

10      nvchar character                      NVARCHAR2 2000         0     0   TRUE

11  nvchar_max character                      NVARCHAR2 1500         0     0   TRUE

12     date_tz   POSIXct       TIMESTAMP WITH TIME ZONE   NA         0     9   TRUE

13    date_ltz   POSIXct TIMESTAMP WITH LOCAL TIME ZONE   NA         0     6   TRUE

User_UV83W

Thank you for your response, but I am not sure what should I take from it. Your example results in a table with case-sensitive column names (at least in our Oracle 19c Enterprise database).

My intent is to create a table that can be queried without the need to quote all the colnames.

I believe there is no workaround as long as there is this code in the .oci.WriteTable function in the ROracle package:

cnames <- sprint('"%s"', names(value))

on the line number 616 in oci.R