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

odbc/R/driver-oracle.R

Lines 30 to 32 in bf7cd40

	"CREATE ", if (temporary) " GLOBAL TEMPORARY ", "TABLE ", table, " (\n",
	" ", paste(fields, collapse = ",\n "), "\n)\n",
	if (temporary) " ON COMMIT PRESERVE ROWS"

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

[detule]

Hi there:

I am not sure if this issue belongs in package:odbc. In particular your fix feels like it belongs in package:dbplyr, where that logic (likely) resides.

Let me know if I missed something.