Forum Stats

  • 3,752,284 Users
  • 2,250,483 Discussions
  • 7,867,775 Comments

Discussions

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

User_UV83W
User_UV83W Member Posts: 2
edited Jul 1, 2020 5:22AM in R Technologies

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

Answers

  • Sherry Lamonica-Oracle
    Sherry Lamonica-Oracle Posts: 437 Employee
    edited Apr 15, 2020 8:27PM

    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
    User_UV83W Member Posts: 2
    edited Jul 1, 2020 5:22AM

    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