Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support Oracle SQL DBs (via odbc or ROracle) #462

Open
2 tasks done
gitgud5000 opened this issue Jan 31, 2023 · 10 comments
Open
2 tasks done

Support Oracle SQL DBs (via odbc or ROracle) #462

gitgud5000 opened this issue Jan 31, 2023 · 10 comments
Assignees
Milestone

Comments

@gitgud5000
Copy link

gitgud5000 commented Jan 31, 2023

Prework

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
Copy link
Collaborator

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

@gitgud5000
Copy link
Author

gitgud5000 commented Jan 31, 2023

@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>**

@gitgud5000 gitgud5000 changed the title Support Oracle SQL DBs (via odbc) Support Oracle SQL DBs (via odbc or ROracle) Jan 31, 2023
@pachadotdev
Copy link
Collaborator

@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
Copy link
Collaborator

@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
Copy link
Author

gitgud5000 commented Feb 13, 2023

@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
Copy link
Collaborator

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

@gitgud5000
Copy link
Author

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

@pachadotdev
Copy link
Collaborator

@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 pachadotdev self-assigned this Feb 28, 2023
@pachadotdev
Copy link
Collaborator

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

@rich-iannone rich-iannone added this to the FUTURE milestone Feb 20, 2024
@lodderig
Copy link

lodderig commented Aug 1, 2024

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

4 participants