Oracle copy_to errors with index

[willshen99]

Issue

I'm running copy_to with odbc connecting to an Oracle database. The command:

copy_to(conn,mtcars, indexes = list('mpg'))

I ran into an error:

Caused by error in `run()`:
! ODBC failed with error 00000 from [Oracle][ODBC][Ora].
✖ ORA-14452: attempt to create, alter or drop an index on temporary table already in use
• Help: https://docs.oracle.com/error-help/db/ora-14452/

Cause

copy_to with index got translated to steps:

1. Create Global Temporary Table
2. Insert data
3. Add index

The temporary oracle table is created with ON COMMIT PRESERVE ROWS https://github.com/r-dbi/odbc/blob/bf7cd404b5417b925dbec1026ac559d1ee09d88d/R/driver-oracle.R#L30-L32 And Oracle will lock the metadata for temp table after data is inserted, which forbids further add index command. I can reproduce the error with below sql script:

CREATE GLOBAL TEMPORARY TABLE TMP1 (id int) ON COMMIT PRESERVE ROWS;
INSERT INTO TMP1 VALUES (1);
CREATE INDEX IDX ON TMP1 (id);

Proposed fix

Add Oracle copy_to logic to first add index, then insert data