Using ROracle dbWriteTable to write POSIXct back to Oracle DB

Question

In Oracle DB:

DESCRIBE ORACLE_DB_TABLE;

|---------------------------------------|
| Column Name      | Data Type          |
|---------------------------------------|
| TRANSACTION_TIME | DATE               |
| TRANSACTION_ID   | VARCHAR2 (20 Byte) |
| TRANSACTION_AMT  | NUMBER (38,10)]    |
|---------------------------------------|

In R:

> r_data_table
      TRANSACTION_TIME TRANSACTION_ID TRANSACTION_AMT
1: 2015-04-28 11:12:24            ABC             123
> dbWriteTable(conn, "ORACLE_DB_TABLE", r_data_table, overwrite = F, append = T, row.names = F)
> Error in .oci.WriteTable(conn, name, value, row.names = row.names, overwrite = overwrite,  :
  Error in .oci.ValidateZoneInEnv(FALSE) :
  environment variable 'ORA_SDTZ()' must be set to the same time zone region as the the environment variable 'TZ(Europe/London)'
> dbGetQuery(conn, "SELECT SESSIONTIMEZONE FROM DUAL")
  SESSIONTIMEZONE
1   Europe/London

As you can see from the error message, I've set the environment variable TZ in R = "Europe/London". Also, from the query above, you can see that the session time zone in Oracle is 'Europe/London' as well.

Why is the error message complaining that the time zone is different between Oracle and R?

How can I write a POSIXct from R to a DATE column in Oracle DB?

Chantey

Answer 1

The following works:

> Sys.setenv(TZ = "GMT")
> Sys.setenv(ORA_SDTZ = "GMT")
> dbWriteTable(conn, "ORACLE_DB_TABLE", r_data_table, overwrite = F, append = T, row.names = F)

Chantey

This solution also works for me when importing from and writing to our Oracle data warehouse. – Willowwillowy

Sys.getenv("TZ") and Sys.getenv("ORA_SDTZ") will display the current values for these parameters (if anything), so you can make sure you're not accidentally overwriting anything unknowingly. – Multiplicand