1

I am a client of an Oracle DB. I use the odbc driver "Oracle in instant_client_12_2" to access the DB via R and other tools. The issue I have is the following:

Let's say I create the following data frame in R and write it to a table in the DB:

library(odbc)
con <- dbConnect(odbc::odbc(), "MYDB", timeout = 10, name, pwd, encoding = 'utf8')

df <- data.frame(TEST = c('abcd', 'eöäü', 'ßdef' ))
dbWriteTable(con, "ENCODING_TEST", df, overwrite = TRUE)

The result I get is:

Oracle DB Result

There are several topics on this, but maybe I did something wrong or it does not work for me. Here an extract of what I have already tried:

con <- dbConnect(odbc::odbc(), "MYDB", timeout = 10, name, pwd, encoding = 'windows-1252')
con <- dbConnect(odbc::odbc(), "MYDB", timeout = 10, name, pwd, encoding = 'cp1252')
con <- dbConnect(odbc::odbc(), "MYDB", timeout = 10, name, pwd, encoding = 'latin1')
con <- dbConnect(odbc::odbc(), "MYDB", timeout = 10, name, pwd)
Sys.setenv(NLS_CHARACTERSET = "AL32UTF8") # I tried also other combinations
Sys.setenv(NLS_LANG = "AL32UTF8")

When I run the following command in DBeaver (SQL Client):

SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER LIKE '%ERSET';

I get:

NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_CHARACTERSET    AL32UTF8

By the way, in DBeaver I have no such issues. I can export everything fine while setting the encoding to windows-1252 in the export wizard.

Another problem but maybe the same issue: When I load data from the oracle server into Power BI, the characters ä,ü,ö are transformed into a,u,o.

Does anyone has an idea what I have to change so it will write the correct encoded values into the db?

Marl
  • 103
  • 1
  • 11
  • Looks like I was able to fix the issue finally with https://docs.oracle.com/cd/E12102_01/books/AnyInstAdm784/AnyInstAdmPreInstall18.html – Marl Dec 14 '21 at 12:03
  • The specified encoding must match with the `NLS_LANG` parameter. For example `encoding = 'windows-1252'` -> `Sys.setenv(NLS_LANG = ".WE8MSWIN1252"` (don't miss the dot "."), See also https://stackoverflow.com/questions/33783902/odbcconnection-returning-chinese-characters-as/33790600#33790600 – Wernfried Domscheit Dec 14 '21 at 12:22
  • Two comments: the [ROracle package](https://www.oracle.com/database/technologies/roracle-downloads.html) is the preferred way to use R with Oracle. Also it is generally better to set Oracle environment variables before the process starts, since they need to be set before Oracle libraries are initialized and this may occur at process start. – Christopher Jones Apr 12 '23 at 01:31

0 Answers0