在ROracle中编码 [英] Encoding in ROracle

问题描述

我在使用ROracle从Oracle数据库中导入æ，ø和å等挪威字符时遇到问题。我以前在RODBC上也遇到过类似的问题。从csv和xlsx等文件导入时，我没有问题，我确定问题在于在使用ROracle进行导入时设置编码，但是文档并没有真正告诉我该怎么做。

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.

各种答案都写了一个函数，用于在导入后转换为UTF-8，但这不是一个对我有用的解决方案。我希望使用dplyr软件包对oracle数据库进行查询，并且需要能够使用æøå之类的字母进行查询。

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 æøå.

这是我的连接字符串，以及我用作测试的sql查询：

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")

这给了我这样的结果： R？dgivningskontor

That gives me results like this: R?dgivningskontor

这是我的会话信息：

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  

推荐答案

我已经为这个问题苦苦挣扎了2天，但是我刚刚找到了解决方法。

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

我的问题是显示类似ü的字符。或é正确地（出于测试目的，我还添加了ø，其显示为？）。
我试图用不同的方式和字符集来设置NLS_LANG环境变量。

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. 将 NLS_LANG = AMERICAN_AMERICA.AL32UTF8 添加到 .Renviron 文件。这适用于我的情况，其中Oracle数据库显示 NLS_CHARACTERSET = WE8ISO8859P15 ，而客户端的语言环境ID显示 en_US.UTF-8 。


2. 保存文件并重新启动RStudio会话。

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.

这篇关于在ROracle中编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！