如何将R中的数据插入到带有标识列的Oracle表中

Question

假设我在Oracle数据库中有一个简单的表

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)
)

我在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))

我正在尝试使用代码将data插入Oracled_test表

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

但以下错误返回

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

怎么了？我该怎么修？非常感谢。

Answer 1

这是纯Oracle(我不知道R(。

样品表：

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.

我最初的想法是建议您在ID列中插入任何值，希望Oracle放弃它并生成自己的值。然而，这是行不通的。

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

---

但是，如果您能够重新创建表，使其不会自动生成ID列的值，而是使用"；变通办法"；(我们无论如何都使用了，因为标识列在Oracle中是相对较新的(-一个序列和一个触发器-您可能能够"；"修复"；

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.

仅插入name(Oracle将使用触发器填充ID(：

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

这就是你在代码中要做的——提供伪ID值，只是为了避免

ORA-00947：值不足

错误。触发器将丢弃并无论如何使用序列：

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>

Answer 2

处理此问题的方法是使用GENERATED BY DEFAULT ON NULL AS IDENTITY创建表，如

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)
)