dbSendQuery (ROracle): define, set, alter session result in console errors

[jhills]

I'm moving some SQL code from SQL Developer into an RMD. ROracle connects to the DB and runs simple select-from-where statements without issue. Any command that does not begin with SELECT seems to fail though.

I'm particular I'm trying:
alter session set global_names = false
set escape on
undefine DATE
define DATE = to_date('01-JAN-21', 'DD-MON-YY')

These run without issue in SQL Developer where they are necessary for the main SQL script to execute.

Are these functions not supported in ROracle? Any ideas or workarounds?

[andresrcs]

@jhills

Are these functions not supported in ROracle?

I doubt the problem is related to the R package since it only works as an interface to send the sql statement to the server trough the underlying driver.

To help us better understand your problem, can you show what exact command you are using and the complete console output you get?

[jhills]

Thanks for the reply. I hope this helps illustrate the issue.

This statement confirms connection is correct and can access DB. No issues here.

```{sql connection="conn"}
select sysdate from dual
```

There is an '&' in the data inside a case statement so I need to set escape on so it doesn't parse this. In SQL Dev this is necessary for my script to run otherwise there's a prompt to insert a bind variable. Output in SQL Dev for this statement is "session altered". This setting holds until the connection is closed.

This does not run in RMD.

```{sql connection="conn"}
set escape on
```

I get this error:
Error in .oci.SendQuery(conn, statement, data = data, prefetch = prefetch, :
ORA-00922: missing or invalid option
Failed to execute SQL chunk

I get the same error if I run it this way instead

```{r}
dbSendQuery(conn, "set escape on")
```