How to insert data from R into Oracle table with identity column?-CodePudding

Assume I have a simple table in Oracle db

CREATE TABLE schema.d_test
(
    id_record integer GENERATED AS IDENTITY START WITH 95000 NOT NULL,
    DT DATE NOT NULL,
    var varchar(50),
    num float,
    PRIMARY KEY (ID_RECORD)
)

And I have a dataframe in R

dt = c('2022-01-01', '2005-04-01', '2011-10-02')
var = c('sgdsg', 'hjhgjg', 'rurtur')
num = c(165, 1658.5, 8978.12354)

data = data.frame(dt, var, num)%>% 
  mutate(dt = as.Date(dt))

I'm trying to insert data into Oracle d_test table using the code

data %>% 
  dbWriteTable(
    oracle_con,
    value = .,
    date = T,
    'D_TEST',
    append = T,
    row.names=F,
    overwrite = F
  )

But the following error returned

Error in .oci.WriteTable(conn, name, value, row.names = row.names, overwrite = overwrite,  : 
  Error in .oci.GetQuery(con, stmt, data = value) : 
  ORA-00947: not enough values

What's the problem? How can I fix it? Thank you.

CodePudding user response：

This is pure Oracle (I don't know R).

Sample table:

SQL> create table test_so (id number generated always as identity not null, name varchar2(20));

Table created.

SQL> insert into test_so(name) values ('Name 1');

1 row created.

My initial idea was to suggest you to insert any value into the ID column, hoping that Oracle would discard it and generate its own value. However, that won't work.

SQL> insert into test_so (id, name) values (-100, 'Name 2');
insert into test_so (id, name) values (-100, 'Name 2')
                     *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

But, if you can afford recreating the table so that it doesn't automatically generate the ID column's value but use a "workaround" (we used anyway, as identity columns are relatively new in Oracle) - a sequence and a trigger - you might be able to "fix" it.

SQL> drop table test_so;

Table dropped.

SQL> create table test_so (id number not null, name varchar2(20));

Table created.

SQL> create sequence seq_so;

Sequence created.

SQL> create or replace trigger trg_bi_so
  2    before insert on test_so
  3    for each row
  4  begin
  5    :new.id := seq_so.nextval;
  6  end;
  7  /

Trigger created.

Inserting only name (Oracle will use a trigger to populate ID):

SQL> insert into test_so(name) values ('Name 1');

1 row created.

This is what you'll do in your code - provide dummy ID value, just to avoid

ORA-00947: not enough values

error you have now. Trigger will discard it and use sequence anyway:

SQL> insert into test_so (id, name) values (-100, 'Name 2');

1 row created.

SQL> select * from test_so;

        ID NAME
---------- --------------------
         1 Name 1
         2 Name 2       --> this is a row which was supposed to have ID = -100

SQL>

CodePudding user response：

The way you can handle this problem is to create table with GENERATED BY DEFAULT ON NULL AS IDENTITY like this

CREATE TABLE CM_RISK.d_test
(
    id_record integer GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 5000 NOT NULL ,
    DT date NOT NULL,
    var varchar(50),
    num float,
    PRIMARY KEY (ID_RECORD)
)