2

I created a table in Oracle like

Create table t1 
(id_record  NUMERIC GENERATED AS IDENTITY START WITH 500000 NOT NULL,
col1 numeric(2,0),
col2 varchar(10),
primary key(id_record))

where id_record is identity column the value of which is generated automatically when appending data to table.

I create a data.frame in R with 2 columns (table_in_R <- data.frame(col1, col2)). Let's skip the values of data frame for simplicity reasons.

When I append data from R to Oracle db using the following code

dbWriteTable(con, 't1', table_in_R, 
         append =T, row.names=F, overwrite = F)

where con is a connection object the error ORA-00947 arises and no data appended.

When I slightly modify my code (append = F, overwrite = T).

dbWriteTable(con_dwh, 't1', table_in_R, 
         append =FALSE, row.names=F, overwrite = TRUE)

the data is appended, but the identity column id_record is dropped.

How can I append data to Oracle db without dropping the identity column?

Improve this question
1
  • I added an workaround that may work with the default on null identity - use at your own risk... Commented Jan 17, 2022 at 12:53

1 Answer 1

Reset to default
2

I'd never (based on this answer) recommend this one step approach where the dbWriteTabledirectly maintains the target table.

Instead I'd recommend a two step approach, where the R part fills a temporary table (with overwrite = T i.e. DROP and CREATE)

df <- data.frame(col1 = c(as.integer(1),as.integer(0)), col2 = c('x',NA))
dbWriteTable(jdbcConnection,"TEMP", df,   rownames=FALSE, overwrite = TRUE, append = FALSE)

In the second step you simple adds the new rows to the target table using

insert into t1(col1,col2) select col1,col2 from temp;

You may call it direct with a database connection or also from R:

res <-   dbSendUpdate(jdbcConnection,"insert into t1(col1,col2) select col1,col2 from temp")

Note there is a workaround anyway:

  1. Define the identity column as

    id_record NUMERIC GENERATED BY DEFAULT ON NULL AS IDENTITY

This configuration of the identity column provides the correct sequence value instead of the NULL value - but you will fail on the above linked problem of Inserting NULL in a Number column.

  1. So the second trick is to use a character NA in the data.frame

Add the identity column to your data.frame and fill it with all as.character(NA).

df <- data.frame(id_record =c(as.character(NA),as.character(NA) ), col1 = c(as.integer(1),as.integer(0)), col2 = c('x',NA))
dbWriteTable(jdbcConnection,"T1", df,   rownames=FALSE, overwrite = F, append = T)

Test works fine, but as mentioned I'd recommend the two step approach.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

I experience the issue with overwrite = T on the 1 step because it changes the types of variables in the temp table. For example the date column is changed by varchar variable. This is the big source of problem when I append data to the target table on the 2 step.
Yes, this is exact the reason I'd prefer the two step appproach with a temp table. Let dbWriteTable with best effort to insert the data and than explicitely fix the problems. Do not let dbWiteTable make implicit conversions. Use to_date to repair the DATE columns, fix the numeric NULLS etc. BTW you'll have to use overwrite = F if your VARCHAR2 columns are longer than 255, otherwise you'll get ORA-12899: value too large for column ... maximum: 255). Good luck! @Davit
to_date for example doesn't work in my case. I've already trid this version too. Unfortunately didn't succed.
Caution SO doesn't like doesn't work;) Whats wrong with to_date(col,'yyyy-mm-dd hh24:mi:ss')? @Davit
The problem is when I run this code to_date(col,'yyyy-mm-dd hh24:mi:ss') the variable I get is varchar, not date as I would like.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.