search

ankane / dbx

A fast, easy-to-use database library for R
Other
187 stars 15 forks source link

ORA-00933 when upserting into Oracle #20

Closed reisner closed 3 years ago

reisner commented 3 years ago

Hi there, I'm using ROracle to connect / upsert into an oracle DB:

RORacle_1.3-1 and dbx_0.2.7, and running:

records = data.frame(
  NOTES = "test",
  STATUS = 'status',
  REVIEWED = 1,
  MCI_REF = 'def',
  MC_REF = 'abc',
  RECNUM = 1234,
  SEQ_NR = 1234,
  PERIOD = '202007'
)
where_cols = c("MCI_REF", "MC_REF", "RECNUM", "SEQ_NR", "PERIOD")
res = dbx::dbxUpsert(con, "CCRISKFACTORS_REV", records, where_cols)

Warning: Error in .oci.SendQuery: ORA-00933: SQL command not properly ended

  88: .oci.SendQuery
  87: .local
  86: dbSendQuery
  84: dbSendStatement
  82: DBI::dbExecute
  77: execute
  76: selectOrExecute
  75: f
  74: inBatches
  73: dbx::dbxUpsert
  72: observeEventHandler [/shinyapp/server.R#329]
   1: shiny::runApp

I can insert just fine through the console with:

insert into ccriskfactors_rev (NOTES, STATUS, REVIEWED, MCI_REF, MC_REF, RECNUM, SEQ_NR, PERIOD)
values ('test', 'status', 1, 'def', 'abc', 1234, 1234, '202007')

Any ideas, or how I can debug this?

ankane commented 3 years ago

Hey @reisner, dbx doesn't support upsert for Oracle unfortunately.

ankane commented 3 years ago

Cleaning up issues