In neemtreeparrot/deebee: Database connectivity made simple

deebee provides set of functions that allows to execute SQL queries in:

1. Oracle using RJDBC drivers and
2. other databases using ODBC drivers

Functions are relatively easy to use after the package is installed. Some of the code result in the following chunks can be listed with errors as this was executed in a development environment.

Oracle

The most basic command is to execute a SQL query using the OraRun_ function. This functions takes the SQL along with the connection information to execute a query and returns the resultset in a tibble.

OraRun_("SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM ALL_TABLES", host_name = "localhost", port = "1521", sid = "xe", user_name = "sc", pwd = "tiger")

To avoid passing repetitive connection information, another function OraRun can be used. The above command can be executed as:

#create a connection string with prefix as "ora_connstr_"
ora_connstr_stg <- list(host_name = "localhost", port = "1521", sid = "xe", user_name = "sc", pwd = "tiger")

#call OraRun once the connection string is defined
OraRun("SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM ALL_TABLES", "stg")

ODBC

ODBCRun is implemented to use a DSN to establish a connection to execute a query.

ODBCRun("SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM ALL_TABLES", "LOCAL_XE")

Comparing Query results

CompareOraDataset helps in comparing resultsets from two environments.

#create a connection string with prefix as "ora_connstr_"  
ora_connstr_stg1 <- list(host_name = "localhost", port = "1521", sid = "xe", user_name = "sc", pwd = "tiger")  
ora_connstr_stg2 <- list(host_name = "localhost", port = "1521", sid = "xe", user_name = "sc", pwd = "tiger")  
ReconResults("SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM ALL_TABLES", "stg1", "stg2")  

---

neemtreeparrot/deebee documentation built on May 6, 2019, 12:05 a.m.