Support Oracle SQL DBs (via odbc or ROracle)

[gitgud5000]

Prework

• [x] Read and abide by pointblank's code of conduct and contributing guidelines.
• [x] Search for duplicates among the existing issues (both open and closed).

Proposal

For those that are interested in using pointblank for remote oracle databases.

When trying to create an agent with a table of the class tbl_OraConnection, tbl_dbi, tbl_sql,tbl_lazy, tbl I get the following error:

  ORA-00933: SQL command not properly ended 

[pachadotdev]

hi @NZambranoc I am not the main author, but can you please post redacted information of an Oracle connection?

[gitgud5000]

@pachadotdev, as in a connection string?

if so:

(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=XXXX)(host=XXX.XXX.XXX.XXX))(connect_data=(service_name=XXXXX_sandbox_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=XXXXX.oraclecloud.com, OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))?TNS_ADMIN=**<wallet_dir>**

[pachadotdev]

@pachadotdev, as in a connection string?

if so:

(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=XXXX)(host=XXX.XXX.XXX.XXX))(connect_data=(service_name=XXXXX_sandbox_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=XXXXX.oraclecloud.com, OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))?TNS_ADMIN=**<wallet_dir>**

I see we can treat it similar to SQL Server via ODBC

[pachadotdev]

@NZambranoc I think I got it

can you please send me this but with your connection, OracleDB is a paid product so I am working with my imagination

library(dplyr)
library(RPostgres)

# replace this with your connection to Oracle
con <- dbConnect(
  Postgres(),
  user = Sys.getenv("dbedu_usr"),
  password = Sys.getenv("dbedu_pwd"),
  dbname = "tradestatistics",
  host = "databases.pacha.dev"
)

dbListTables(con)

class(con)

d <- tbl(con, "sections") # replace with your own table

tbl_src_info <- utils::capture.output(d %>% unclass() %>% .$src)

# send me the output of this line
# PLEASE redact any IP or URL
tbl_src_info[grepl("^src:", tbl_src_info)] %>% gsub("src:\\s*", "", .)

dbDisconnect(con)

[gitgud5000]

@pachadotdev Here's the output:

"Oracle XX.XX.0000[<USER>@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=<PORT>)(host=<HOST_IP>))(connect_data=(service_name=XXXX_sandbox_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn=\"CN=XXXXX.oraclecloud.com, OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US\")))/]" 

[pachadotdev]

@NZambranoc do you know of any public access oracle database? I reached a point where I need to test against a db

[gitgud5000]

@pachadotdev This, perhaps? https://www.oracle.com/cloud/free/

[pachadotdev]

@pachadotdev This, perhaps? https://www.oracle.com/cloud/free/

it's in browser... I shall install oracle locally when I have some bandwidth by the end of april

[pachadotdev]

@rich-iannone I have some spare time. Any advice about this? The in-browser DB won't make it. Perhaps we should promote PostgreSQL

[lodderig]

https://www.oracle.com/autonomous-database/free-trial/#free-container-image might provide an environment to test ?