Write only into a subset of database table columns

Question

On Oracle I have a table with SDO_GEOMETRY data type:

CREATE TABLE T_GEO_TABLE
   (
    "LONGITUDE" DOUBLE, 
    "LATITUDE" DOUBLE, 
    "GEO" "SDO_GEOMETRY"
   );

In R i have a data frame with longitude and latitude. I want to write this table to Oracle with DBI (RJDBC) and populate the "GEO" field later with an UPDATE statement in SQL.

The function dbWriteTable requires the R table to have exactly the same columns as the database table. If I make an empty column "GEO" in R and try to write this table to Oracle:

r_table <- r_table %>% mutate(GEO = NA)

dbWriteTable(conn = con_oracle, name = "T_GEO_TABLE", value = r_table, overwrite = FALSE, append = TRUE)

, I get an error: ORA-00932: inconsistent datatypes: expected MDSYS.SDO_GEOMETRY got CHAR. The same thing happens of course with other NA types (NA_character_, NA_integer, ...).

The obvious solution is to insert first into a temporary table on Oracle with only longitude and latitude columns and then do the rest in SQL with dbSendUpdate.

Is there an elegant way to achieve this without the temp table? My question is not specific to SDO_GEOMETRY type. Can you somehow force a DBI function to insert into only a subset of columns.

Just found a possible solution here: stackoverflow.com/questions/72212230/… — tomaz
– tomaz, Commented Feb 21, 2023 at 14:32
I don't have access to Oracle, but ... writing a subset of columns works in every other DBMS I've tried: duckdb, sqlite, postgres, sql server, and (I think) mariadb/mysql. That is, if I start with create table r2temp (a int, b int, c int), I can then do DBI::dbWriteTable(con, "r2temp", data.frame(a=1L), create=F, overwrite=F, append=T) and it works. Does RJDBC specifically impose this restriction? It isn't DBI, and this seems like an onerous (and unnecessary) constraint that should be handled at the table level (e.g., b INT NOT NULL). — r2evans
– r2evans, Commented Feb 21, 2023 at 14:45
Can you create a view over the table and expose only the columns you will be inserting into? Then insert into the view. — Paul W
– Paul W, Commented Feb 21, 2023 at 16:38
@r2evans: I tried to reproduce your example. With DBI::dbWriteTable(con_oracle, "tt_temp", data.frame(a=1L), create=F, overwrite=F, append=T), I get Error in .local(conn, statement, ...) : execute JDBC update query failed in dbSendUpdate (ORA-00955: name is already used by an existing object). With DBI::dbWriteTable(con_oracle, "TT_TEMP", data.frame(a=1L), create=F, overwrite=F, append=T) I get Error in .local(conn, statement, ...) : execute JDBC update query failed in dbSendUpdate (ORA-00947: not enough values). Thanks anyway. — tomaz
– tomaz, Commented Feb 22, 2023 at 9:08
Interesting. name is already used suggests that the RJDBC driver is ignoring or misinterpreting the create=, overwrite=, and/or append= arguments. Sorry, since I don't use RJDBC, I can only comment on how DBI and ODBC are supposed to work given its specification and such. It works in other DBMSes, certainly suggesting either a key difference in Oracle (certainly possible), in JDBC (on which RJDBC relies), or lacking both of those possibly a mis-implementation in RJDBC (I'm assuming it's one of the first two, but who knows). — r2evans
– r2evans, Commented Feb 22, 2023 at 14:55

tomaz · Accepted Answer · 2023-02-22 09:33:27Z

0

Solution from Paul W in comments works:

CREATE OR REPLACE VIEW V_GEO_TABLE AS
SELECT LONGITUDE, LATITUDE FROM T_GEO_TABLE;

dbWriteTable(conn = con_oracle, name = "V_GEO_TABLE", value = r_table, overwrite = FALSE, append = TRUE)

answered Feb 22, 2023 at 9:33

tomaz

5225 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

Write only into a subset of database table columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related