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

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?

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?

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

dbSendQuery(conn, "set escape on")

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.