Search code examples
rdbiroracle

Special Characters are Converted to ? When Inserting into Oracle Database Using R

I'm making a connection to an oracle database using the ROracle package and DBI package. When I try to execute insert statements that have special characters, the special characters get converted to non-special characters. (I'm sure there's more correct terms for "special" and "non-special" that I'm not aware of).

First I make the following connection:

connection <- dbConnect(
  dbDriver("Oracle"),
  username = "xxxxx",
  password = "xxxxx",
  dbname = "xxxx"
  )

Then I execute the following insert statement on a table I already have created. Column A has a type of nvarchar2.

dbSendQuery(connection, "insert into TEST_TABLE (A) values('£')")

This is what gets returned:

Statement:            insert into TEST_TABLE (A) values('#') 
Rows affected:        1 
Row count:            0 
Select statement:     FALSE 
Statement completed:  TRUE 
OCI prefetch:         FALSE 
Bulk read:            1000 
Bulk write:           1000

As you can see, the "£" symbol gets replaced by a "#". I can execute the insert statement directly in PL/SQL and there's no issue, so it seems to be an issue with R. Any help is appreciated.

Solution

  • This was resolved by running Sys.setenv(NLS_LANG = "AMERICAN_AMERICA.AL32UTF8") before creating the connection.