ROracle (version 1.3-1)

Oracle: Instantiate an Oracle client from the current R session

Description

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.

Usage

Oracle(interruptible = FALSE, unicode_as_utf8 = TRUE,
         ora.attributes = FALSE)
  Extproc(extproc.ctx = NULL)

Arguments

interruptible

A logical indicating whether to allow user interrupts on long-running queries.

extproc.ctx

An external pointer wrapping extproc context.

unicode_as_utf8

A logical indicating whether to fetch NCHAR, NVARCHAR and NCLOB data encoded in UTF8.

ora.attributes

A logical indicating whether to include the attributes ora.encoding, ora.type, and ora.maxlength in the data frames returned by dbGetQuery and fetch.

Value

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.

Side Effects

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.

Oracle user authentication

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.

Transactions

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.

Details

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.

References

For Oracle Database documentation, see http://docs.oracle.com/en/database/.

See Also

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:

dbCommit dbRollback

On queries and result objects:

fetch dbClearResult dbColumnInfo dbGetStatement dbHasCompleted dbGetRowsAffected dbGetRowCount

On meta-data:

show summary dbGetInfo

Examples

# 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)
  
# }