Compute() with Oracle - missing keyword GLOBAL

[SamuelAllain]

Hi,

First of all, thank you for your work. I think there's a bug with the command compute() when used with Oracle connection.

library(ROracle)
library(dplyr)
drv <- dbDriver("Oracle")
conn <- dbConnect(drv, dbname = "DATABASE")
tbl(conn, "table") %>% compute

Returns this error

Error in .oci.SendQuery(conn, statement, data = data, prefetch = prefetch,  : 
ORA-14459: missing GLOBAL keyword

It's exactly the same that happens if you do dbExecute(conn, 'CREATE TEMPORARY TABLE table_name(id NUMBER)') instead of dbExecute(conn, 'CREATE GLOBAL TEMPORARY TABLE table_name(id NUMBER)') which works fine. See other people concerned.

[nilescbn]

This is issue is still happening for me using dbplyr v 2.1.1. Apologies if this is not the prefered place to raise this.

Using dplyr/dbplr with this Oracle database is fantastic. So thank you. I've been long hoping to use compute() but do just fine most often without it. I'm just really wondering why it's not working for me.

In terms of a reproducible example, this is a confidential database. So I hope this is descriptive enough to show that the error is still happening. I followed SamuelAllain's code and connect the same way using ROracle.

Modifying his example some, this works for me.

dbExecute(con, 'CREATE GLOBAL TEMPORARY TABLE testing123(id INT,
    description VARCHAR2(100))')

And this does not.

trip |> filter(AGID == "W") |> compute() )

I get the same error.

Error in .oci.SendQuery(conn, statement, data = data, prefetch = prefetch, :
ORA-14459: missing GLOBAL keyword

The trip object is a tbl_OraConnection to one of the tables I work with.

I imagine the issue is on my end but any tips would be very much appreciated. I may also post in here. I posted a comment back in 2019 when the copy_to() suggestion didn't work for me.

Thank you again for all your work on this package.

[mgirlich]

Ah, I think we only added sql_query_save() also for the class Oracle but not for OraConnection. Can you try after executing this code

sql_query_save.OraConnection <- dbplyr::sql_query_save.Oracle

[nilescbn]

Thank you for taking a look, mgirlich.

I'm probably not doing it correctly, yet in trying to execute that code I get this error:
Error: 'sql_query_save.Oracle' is not an exported object from 'namespace:dbplyr'.

I upgraded to dbplyr v. 2.1.1.9000 to see if that would help. It didn't.

Searching the repository, I found that function and copied this code and ran it.

sql_query_save.Oracle <- function(con, sql, name, temporary = TRUE, ...) { build_sql( "CREATE ", if (temporary) sql("GLOBAL TEMPORARY "), "TABLE \n", as.sql(name, con), " AS\n", sql, con = con )}

With the function in my Global Environment, I ran the sql_query_save.OraConnection <- dbplyr::sql_query_save.Oracle code (ommitting the dbplyr:: part), and then tried/ compute(). I didn't get the GLOBAL KEYWORD error. Instead it returns an empty table.

I didn't expect that to work but perhaps it provides some clues.

Thanks again, I appreciate it.

[rgriffier]

Hi,

I dig up an old thread about temporary tables in Oracle. I'm coming to the same point as @nilescbn : I can create a temporary table with dplyr::compute() but the table remains permanently empty (the tbl_dbi send back has no row).

DBMS side, the generated table is of type GLOBAL TEMPORARY TABLE with the creation attribute ON COMMIT DELETE ROW (default attribute). The creation of the temporary table via dbplyr goes through a commit explaining the empty table.
It is possible to specify an attribute at the creation of the table to keep the data despite the validation of the ON COMMIT PRESERVE ROW transaction (documentation here).

CREATE GLOBAL TEMPORARY TABLE TEMP_TABLE
AS SELECT * FROM DUAL;

SELECT * FROM TEMP_TABLE;

The select query return 0 row. The table is type ON COMMIT DELETE ROW (default parameter) as those one generated by dplyr::compute().

CREATE GLOBAL TEMPORARY TABLE TEMP_TABLE
ON COMMIT PRESERVE ROWS
AS SELECT * FROM DUAL;

SELECT * FROM TEMP_TABLE;

The select query return 1 row as expected.

Adding the ON COMMIT PRESERVE ROWS parameter in the sql_query_save.Oracle function should result in the creation of a non-empty temporary table accessible on the client side. However, the GLOBAL TEMPORARY TABLE of oracle are particular and persist after the end of the session (the data are truncated but the structure of the temporary table is preserved, as discussed here and here)

Since version 18c, Oracle has implemented a second type of temporary table called PRIVATE TEMPORARY TABLE. These temporary tables are destroyed at the end of the session, as in the other DBMS. In the same way as for GLOBAL TEMPORARY, PRIVATE TEMPORARY TABLE delete the data at the end of each transaction (at commit). It is possible to keep the data at the end of the transaction by adding the creation attribute COMMIT PRESERVE DEFINITION.

All PRIVATE TEMPORARY TABLE must be prefixed depending on the PRIVATE_TEMP_TABLE_PREFIX (default to ORA$PTT_, ex : ORA$PTT_DBPLYR_001). By default, Oracle is case insensitive on object names. To be case sensitive in the name of the temporary table to be created, the whole name must be surrounded by double quotes (e.g. "ORA$PTT_dbplyr_001").

To summarize, in order to allow the creation of temporary tables in Oralce, it would be necessary to :

• Change the method of creating temporary tables to a PRIVATE TEMPORARY TABLE with the attribute ON COMMIT PRESERVE DEFINITION to have access to the data on the client side data after the temporary table creation.
• Take into account the mandatory namespace in Oracle for the private temporary table with a default parameter of ORA$PTT_ (but which can be different depending on the implementation).

CREATE PRIVATE TEMPORARY TABLE "ORA$PTT_dbplyr_001"
ON COMMIT PRESERVE DEFINITION
AS (
  SELECT * FROM DUAL
);

From what I understand from the code of dbplyr, the change to be made concerns sql_query_save.Oracle:
A beginning of a proposal (I don't know what would be the best way to manage the table name with the namespace to respect):

sql_query_save.Oracle <- function(con, sql, name, temporary = TRUE, ...) {
  build_sql(
    "CREATE ", if (temporary) sql("PRIVATE TEMPORARY "), "TABLE \n",
    as.sql(name, con), if (temporary) sql(" ON COMMIT PRESERVE DEFINITION \n"), " AS\n", sql,
    con = con
  )
}

I hope it will help!
Thanks

Environement:

• Oracle: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
• R: R version 4.2.0 (2022-04-22)
• dplyr: 1.0.10
• dbplyr: 2.2.1