Dbplyr tbl() returns wrong column type

tidyverse
1

I have a very simple Oracle table with two columns, one a character and another an integer.
Here is the definition:

CREATE TABLE "MYACCOUNT"."ITEMS" (
  "ID" VARCHAR2(6 CHAR) NOT NULL ENABLE, 
	"VERSION" NUMBER(1,0)
   ) TABLESPACE "XYZZY" ;

I can successfully connect to it

library('ROracle')
usr <- "MYACCOUNT"
pwd <- ""
dbn <- "MYDBNANE"
drv <- DBI::dbDriver("Oracle")
con <- ROracle::dbConnect(drv, external_credentials = TRUE, username = usr, dbname = dbn)

# populate `items` from some existing data...
# DBI::dbWriteTable(con, "ITEMS", itemss, overwrite = TRUE)

#...and re-read them: yahhaay!
m <- dplyr::tbl(con, "ITEMS") 
m
# Source:   table<ITEMS> [?? x 2]
# Database: OraConnection
   id          version
   <chr>    <dbl>
 1 0083c3      2.
 2 00a155      0.
 3 00b0f3      0.
 4 00b1f8      2.
 5 00b1fa      2.
 6 010009      0.
 7 01000f      0.
 8 01001b      0.
 9 010094      0.
10 0100dc      0.
# ... with more rows

Is it normal to get <dbl> for the version column which is an integer?
What did I do wrong eventually?

2

Are you sure NUMBER command in Oracle is integer and not a double? Looking at this link I don't see it saying that it will return an integer, e.g.:

TO_NUMBER('1210.73')
Result: 1210.73
3

The issue seems to stem from the command I used to populate the table

> items
# A tibble: 5,220 x 2
   id          version
   <chr>    <int>
 1 0083c3       2
 2 00a155       0
 3 00b0f3       0
 4 00b1f8       2
 5 00b1fa       2
 6 010009       0
 7 01000f       0
 8 01001b       0
 9 010094       0
10 0100dc       0
# ... with 5,210 more rows

DBI::dbWriteTable(con, "ITEMS", items, overwrite = TRUE)

If I instead use append = TRUE instead of overwrite = TRUE things work...!!!
Sorry for the fuss

3 Likes
4

Thanks for your post...!