1

I'm having issues importing norwegian characters like æ, ø, and å from an Oracle database using ROracle. I've had similar problems with RODBC before. I don't have the problem when importing from files like csv and xlsx, I'm sure the problem is with setting the encoding when importing with ROracle, but the documentation does't really tell me how to do that.

Various answers write a function for converting to UTF-8 after importing, but that is not a solution that works for me. I wish to make queries to the oracle DB using the dplyr package and need to be able to query using letters like æøå.

Here is my connection string, and the sql-query I use as a test:

library(ROracle)

drv <- dbDriver("Oracle", unicode_as_utf8 = TRUE, ora.attributes = TRUE)
# Create the connection string
host <- "xx.xxx.xx.x"
port <- xxxx
sid <- "xxxxxx"
connect.string <- paste(
  "(DESCRIPTION=",
  "(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
  "(CONNECT_DATA=(SID=", sid, ")))", sep = "")
con <- dbConnect(drv, username = "",
                 password = "",dbname=connect.string)

test <- dbGetQuery(con, "SELECT DECODE FROM T_CODE where key_id=17")

That gives me results like this: R?dgivningskontor

Here is my session info: 

R version 3.5.0 (2018-04-23)
Platform: x86_64-apple-darwin15.6.0 (64-bit)
Running under: macOS High Sierra 10.13.4

Matrix products: default
BLAS: /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib
LAPACK: /Library/Frameworks/R.framework/Versions/3.5/Resources/lib/libRlapack.dylib

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] ROracle_1.3-1 DBI_1.0.0    

loaded via a namespace (and not attached):
[1] compiler_3.5.0 tools_3.5.0    yaml_2.1.19
user2554330
  • 37,248
  • 4
  • 43
  • 90
Thomas Tallaksen
  • 97
  • 12
  • Are all parts of your application, including data transports in UTF-8? Is the original Oracle database set to be encoded in UTF-8 or another Unicode serialisation? – Dragonthoughts May 16 '18 at 08:55
  • The Oracle Database has NLS_CHARACTERSET WE8MSWIN1252, RStudio has UTF-8 set as encoding. – Thomas Tallaksen May 16 '18 at 08:57
  • Then you will need to read the original database with a driver that accepts WIN1252 Character set so you can transcode the data to UTF-8. – Dragonthoughts May 16 '18 at 09:00
  • ROracle should be able to do that, but I don't quite know how get the settings right. I've tried setting NLS_LANG = "AMERICAN_AMERICA.AL32UTF8" in /etc/profile, but no luck. – Thomas Tallaksen May 16 '18 at 09:03

2 Answers2

1

I have been struggling with this issue for 2 days, but I just found a solution.

My problem was to display characters like "ü" or "é" correctly (for testing purposes I also added "ø", which was displayed as "?"). I was trying to set the NLS_LANG environment variable on different ways and with different character sets.

At the end these have been the steps that worked for me:

  1. Add NLS_LANG="AMERICAN_AMERICA.AL32UTF8" to the .Renviron file. This applies in my case, where the Oracle DB shows NLS_CHARACTERSET=WE8ISO8859P15, and the Locale ID on the client side shows en_US.UTF-8.
  2. Save file and restart the RStudio session.

When running and querying the database again characters were displayed correctly at this point.

Ion Garaicoechea
  • 11
  • 1
0

Can't comment, so I write this as an answer.

The .Renviron solution worked for me with a spanish database, but when I used NLS_LANG="AMERICAN_AMERICA.AL32UTF8" I got an error with the password and username.

Then I find this article and finally understood, my solution was.

# First with 'usethis' library create the .Renviron
library(usethis)
usethis::edit_r_environ()

# Then add the NLS_LANG in Renviron
# NLS_LANG=".WE8ISO8859P1"

# If for some reason Renviron wont load you can use this in your script
readRenviron("path_to_the_file/.Renviron")

Of course restart RStudio and I finally can get the ñ character instead of a ? simbol.

Thanks to @Ion Garaicoechea

olitroski
  • 46
  • 3