Oracle invalid SQL data type <-25>

[mlaviolet]

Issue Description and Expected Result

dbListFields() throws error Invalid SQL data type <-25>

Database

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Example

A full reprex was not possible, but here are all the steps which led to the error. My IT wants to eliminate R as the source of the issue before they attempt any assistance. Thanks for any help!

library(DBI)
con <- dbConnect(odbc::odbc(), dsn = "edwp", dbname = "nhedwp", uid = "michael.j.laviolette", 
    pwd = rstudioapi::askForPassword())
dbListFields(con, "GEOGRAPHY_TRANSFER", schema_name = "WRQPRD")
#> Error in connection_sql_columns(conn@ptr, table_name = name, catalog_name = catalog_name,  : 
#>  nanodbc/nanodbc.cpp:2525: HY004: [Oracle][ODBC]Invalid SQL data type <-25>. 

Session Info

devtools::session_info()
#> Session info -------------------------------------------------------------
#>  setting  value                       
#>  version  R version 3.5.1 (2018-07-02)
#>  system   x86_64, mingw32             
#>  ui       RTerm                       
#>  language (EN)                        
#>  collate  English_United States.1252  
#>  tz       America/New_York            
#>  date     2018-08-08
#> Packages -----------------------------------------------------------------
#>  package   * version date       source        
#>  backports   1.1.2   2017-12-13 CRAN (R 3.5.0)
#>  base      * 3.5.1   2018-07-02 local         
#>  compiler    3.5.1   2018-07-02 local         
#>  datasets  * 3.5.1   2018-07-02 local         
#>  devtools    1.13.6  2018-06-27 CRAN (R 3.5.0)
#>  digest      0.6.15  2018-01-28 CRAN (R 3.5.0)
#>  evaluate    0.11    2018-07-17 CRAN (R 3.5.1)
#>  graphics  * 3.5.1   2018-07-02 local         
#>  grDevices * 3.5.1   2018-07-02 local         
#>  htmltools   0.3.6   2017-04-28 CRAN (R 3.5.0)
#>  knitr       1.20    2018-02-20 CRAN (R 3.5.0)
#>  magrittr    1.5     2014-11-22 CRAN (R 3.5.0)
#>  memoise     1.1.0   2017-04-21 CRAN (R 3.5.0)
#>  methods   * 3.5.1   2018-07-02 local         
#>  Rcpp        0.12.18 2018-07-23 CRAN (R 3.5.1)
#>  rmarkdown   1.10    2018-06-11 CRAN (R 3.5.0)
#>  rprojroot   1.3-2   2018-01-03 CRAN (R 3.5.0)
#>  stats     * 3.5.1   2018-07-02 local         
#>  stringi     1.2.4   2018-07-20 CRAN (R 3.5.1)
#>  stringr     1.3.1   2018-05-10 CRAN (R 3.5.0)
#>  tools       3.5.1   2018-07-02 local         
#>  utils     * 3.5.1   2018-07-02 local         
#>  withr       2.1.2   2018-03-15 CRAN (R 3.5.0)
#>  yaml        2.2.0   2018-07-25 CRAN (R 3.5.1)

[edwardhkng]

I encountered the same problem. Any help appreciated.

[stvrd]

Same problem for me. Any help is appreciated.

[cderv]

I am curious as an Oracle user too. I did not encounter such error. Do you know the data types of your column inside the Oracle table ?

[stvrd]

According to this link, it is the SQL_C_SBIGINT ODBC type, which corresponds to the int64 SPL type which causes problems.

[jimhester]

Yes, this seems to be a oracle driver limitation, I don't think there is much that the odbc package can do about it.

[mlaviolet]

I got around the error by using tbl() to set up a virtual table object and then applied colnames(). Thanks, everyone!