This function creates and initializes an Oracle client from the current R session. It returns an object that allows you to connect to one or more Oracle servers.
Oracle(interruptible = FALSE, unicode_as_utf8 = TRUE,
ora.attributes = FALSE)
Extproc(extproc.ctx = NULL)A logical indicating whether to allow user interrupts on long-running queries.
An external pointer wrapping extproc context.
A logical indicating whether to fetch NCHAR, NVARCHAR and NCLOB data encoded in UTF8.
A logical indicating whether to include the attributes ora.encoding,
ora.type, and ora.maxlength in the data frames returned by
dbGetQuery and fetch.
An object of class OraDriver for Oracle or
ExtDriver for Extproc whose class extends DBIDriver.
This object is used to create connections, using the function
dbConnect, to one or more Oracle database engines.
The R client part of the database communication is initialized,
but note that connecting to the database engine needs to be done through
calls to dbConnect.
In order to establish a connection to an Oracle server users need to provide a user name, a password, and possibly a connect identifier (for more information refer to chapter 8 (Configuring Naming Methods) of Oracle Database Net Services Administrator's Guide). This is the same as the part of the SQL*Plus connect string that follows the '@' sign.
Connections to an Oracle TimesTen IMDB instance are established using the OCI tnsnames or easy connect naming methods. For additional information on TimesTen connections for OCI see chapter 3 (TimesTen Support for Oracle Call Interface) of the Oracle TimesTen In-Memory C Developer's Guide.
The current implementation directly supports transaction
commits and rollbacks on a connection-wide basis through calls
to dbCommit and dbRollback.
Save points are not yet directly implemented, but you may be able
to define them and rollback to them through calls to dynamic SQL
with dbGetQuery.
Notice that Oracle (and ANSI/ISO compliant DBMS) transactions are
implicitly started when data definition SQL statements are executed (create
table, etc.), which helper functions like dbWriteTable
may execute behind the scenes. You may want or need to commit
or roll back your work before issuing any of these helper functions.
This object is a singleton, that is, on subsequent invocations it returns the same initialized object.
This implementation allows you to connect to multiple host servers and run multiple connections on each server simultaneously.
When interruptible is set to TRUE, it allows for interrupting
long-running queries on the server by executing the query in a thread. Main
thread checks for Ctrl-C and issues OCIBreak/OCIReset to cancel the operation
on the server. By default interruptible is FALSE.
When unicode_as_utf8 is set to FALSE, NCHAR, NVARCHAR and NCLOB data
is fetched using the character set using the NLS_LANG setting. By default
unicode_as_utf8 is set to TRUE.
When ora.attributes is set to TRUE attributes ora.encoding,
ora.type and ora.maxlength are added in result data frame
returned from dbGetQuery and fetch. It should be used with dbWriteTable to
create the same data types as in the Oracle DBMS as fetched from the source
table.
For Oracle Database documentation, see http://docs.oracle.com/en/database/.
On database managers:
dbDriver
dbUnloadDriver
dbListConnections
On connections:
dbConnect
dbDisconnect
dbSendQuery
dbGetQuery
dbGetException
dbListResults
Convenience methods:
dbListTables
dbReadTable
dbWriteTable
dbExistsTable
dbRemoveTable
dbListFields
On transaction management:
On queries and result objects:
fetch
dbClearResult
dbColumnInfo
dbGetStatement
dbHasCompleted
dbGetRowsAffected
dbGetRowCount
On meta-data:
# NOT RUN {
# }
# NOT RUN {
## create a Oracle instance and create one connection.
ora <- Oracle() ## or dbDriver("Oracle")
con <- dbConnect(ora, username = "scott", password = "tiger",
dbname = "inst1")
## if you are connecting to a local database
con <- dbConnect(ora, username = "scott", password = "tiger")
## execute a statement and fetch its output in chunks of no more
## than 5000 rows at a time
rs <- dbSendQuery(con, "select * from emp where deptno = 10")
while (!dbHasCompleted(rs)) {
df <- fetch(rs, n = 5000)
## process df
}
dbClearResult(rs) ## done with this query
## execute and fetch a statement with bind data
df <- dbGetQuery(con, "select * from emp where deptno = :1",
data = data.frame(depno = 10))
## create a copy of emp table
dbGetQuery(con, "create table foo as select * from emp")
## execute and bind an INSERT statement
my.data = data.frame(empno = c(8001, 8002), ename = c('MUKHIN', 'ABOYOUN'))
more.data = data.frame(empno = c(8003), ename = c('JAMES'))
rs <- dbSendQuery(con, "insert into foo (empno, ename) values (:1, :2)",
data = my.data)
## execute with more data
execute(rs, data = more.data)
dbClearResult(rs) ## done with this query
## ok, everything looks fine
dbCommit(con)
## a concise description of the driver
summary(ora)
## done with this connection
dbDisconnect(con)
# }
Run the code above in your browser using DataCamp Workspace