ora (version 2.0-1)

sql: Import Data from Oracle

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.

Value

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

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").

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)