ROracle连接Oracle导入挪威字符编码异常，需支持dplyr含æøå查询

Hey there, let's work through this encoding problem you're having with ROracle and Norwegian characters like æ, ø, å. I've run into similar issues with non-ASCII characters in Oracle connections, so here's a step-by-step breakdown of what to check and fix—no post-import conversion needed, which is perfect for your dplyr query needs.

Your Current Setup Recap #

First, let's recap your current code and environment to make sure we're on the same page:

Connection Code #

library(ROracle) 
drv <- dbDriver("Oracle", unicode_as_utf8 = TRUE, ora.attributes = TRUE) 

# Connection details
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") 

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 

Step 1: Confirm Your Oracle Database's Character Set #

First things first—we need to make sure R's encoding matches what your Oracle database is using. Run this query in SQL*Plus, SQL Developer, or another Oracle client to get the database's character set:

SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

Common sets that support Norwegian characters are AL32UTF8 (UTF-8, preferred) or WE8ISO8859P1 (Latin-1). Jot down what you get here—we'll need it for the next steps.

Step 2: Tweak ROracle Connection Settings #

The unicode_as_utf8 = TRUE parameter is a good start, but we can add explicit NLS settings to align R and Oracle:

Option A: Add NLS_LANG to Your Connection String #

Modify your connection string to include the NLS_LANG parameter that matches your database's character set:

connect.string <- paste( 
  "(DESCRIPTION=", 
  "(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))", 
  "(CONNECT_DATA=(SID=", sid, ")))", 
  "?NLS_LANG=NORWEGIAN_NORWAY.AL32UTF8",  # Replace AL32UTF8 with your DB's charset
  sep = "") 

If your database uses WE8ISO8859P1, use NORWEGIAN_NORWAY.WE8ISO8859P1 instead.

Option B: Set NLS_LANG as an Environment Variable #

On macOS, you can set the NLS_LANG variable in R before loading ROracle—this ensures the Oracle client picks it up:

Sys.setenv(NLS_LANG = "NORWEGIAN_NORWAY.AL32UTF8")  # Match your DB's charset
library(ROracle)
# Rest of your connection code follows...

Step 3: Set Session-Level NLS Parameters #

If the connection string tweaks don't work, explicitly set the NLS settings for your Oracle session right after connecting:

# Run these right after dbConnect()
dbExecute(con, "ALTER SESSION SET NLS_CHARACTERSET = 'AL32UTF8'")  # Your DB's charset here
dbExecute(con, "ALTER SESSION SET NLS_LANGUAGE = 'NORWEGIAN'")
dbExecute(con, "ALTER SESSION SET NLS_TERRITORY = 'NORWAY'")

This forces your session to use the correct encoding for both reading and writing characters.

Step 4: Ensure dplyr Queries Use Correct Encoding #

When using dplyr with dbplyr to query the database, make sure your string literals are properly encoded. Since your R locale is already UTF-8, you can explicitly confirm the encoding with enc2utf8() to avoid mismatches:

library(dplyr)
library(dbplyr)

# Example query filtering with Norwegian characters
result <- tbl(con, "T_CODE") %>%
  filter(key_id == 17, DECODE == enc2utf8("Rådgivningskontor")) %>%
  collect()

This ensures the string is sent to Oracle in the correct encoding, so your query matches the stored values properly.

Step 5: Check Your Oracle Instant Client #

ROracle relies on the Oracle Instant Client—make sure it's installed and configured to support your database's character set. On macOS, double-check that the client version matches your database version, and that the NLS_LANG variable is set correctly system-wide if needed (though setting it in R should be enough).

Troubleshooting If It's Still Not Working #

• Try flipping the unicode_as_utf8 parameter: set it to FALSE in dbDriver(). Sometimes older Oracle clients behave better with this setting.
• Test with a super simple query (e.g., SELECT 'æøå' FROM DUAL) to see if the characters come through correctly—this helps isolate if the issue is with your table data or the connection.
• Check if other Oracle clients (like SQL Developer) display the characters correctly. If they do, the problem is definitely in the ROracle/R configuration.

内容的提问来源于stack exchange，提问作者Thomas Tallaksen