sql: Import Data from Oracle

In ora: Convenient Tools for Working with Oracle Databases

Description

Run SQL query returning an R data frame.

Usage

sql(query, tolower=TRUE, dots=TRUE, encoding="unknown", useBytes=TRUE,
    stringsAsFactors=FALSE, warn=-1, debug=FALSE, ...)

Arguments

query	string containing SQL query or the name of a file containing a query.
tolower	whether column names should be lowercased.
dots	whether underscores in column names should be replaced with dots, converting col_name to col.name.
encoding	passed to readLines.
useBytes	passed to gsub.
stringsAsFactors	whether to convert string columns to factors.
warn	sets the handling of warning messages, e.g. when Oracle columns are of type LONG.
debug	whether to return the finalized SQL query string, instead of submitting it to Oracle.
...	passed to dbConnect.

Details

The query is not required to end with a semicolon. In fact, semicolons are removed internally before submitting the query to Oracle.

The arguments encoding and useBytes enable the user to solve character encoding problems within the SQL query. If the query contains non-ASCII characters, readLines and gsub (called by sql) may convert the query to a different encoding than the Oracle database expects.

The arguments stringsAsFactors and warn correspond to options with the same names, but the session options are not used as default values. Therefore, it is necessary to pass stringsAsFactors=TRUE directly to sql in order to import string columns as factor. This option-overriding is designed to make results more predictable and facilitate collaboration between database users.

debug=TRUE is helpful for solving problems, and also to save complex queries (possibly to a file) for later use.

The ... argument can be used to set username, password, and/or dbname (see dbConnect). Abbrevations like user and pass are allowed. The default database name is determined by the environment variable ORACLE_SID, which can be redefined within an R session using Sys.setenv(ORACLE_SID="foo").

Value

Data frame containing the imported data, or a simple string if debug=TRUE.

See Also

sql is to Oracle tables as read.table is to text files.

ora gives an overview of the package.

Examples

## Not run: 


## 1 Basic queries

# Pass query as a simple string
sql("SELECT username,created FROM all_users WHERE rownum<=10")

# Pass query as a multiline string
sql("SELECT extract(year from created) AS year,
            count(username) AS users
       FROM all_users
   GROUP BY extract(year from created)")

# Pass query as a file
write(c("SELECT username, created",
        "FROM all_users",
        "WHERE rownum <= 10;"), "query.sql")
sql("query.sql")


## 2 Review query string, before sending it to Oracle

sql(paste0("SELECT username,created FROM all_users WHERE rownum<=",5+5),
    debug=TRUE)


## End(Not run)

ora documentation built on May 2, 2019, 3:22 a.m.