ROracle (version 0.5-12)

Oracle: Instantiate an Oracle client from the current Rsession

Description

This function creates and initializes an Oracle client from the current Rsession. It returns an object that allows you to connect to one or several Oracle servers.

Usage

Oracle(max.con = 10, fetch.default.rec = 500, force.reload = FALSE)

Arguments

max.con

maximum number of connections that we intend to have open. This can be up to 10, a limit hard-coded in the current implementation.

fetch.default.rec

number of records to fetch at one time from the database. (The fetch method uses this number as a default.)

force.reload

should we reload (reinitialize) the client code? Setting this to TRUE allows you to change default settings. Notice that all connections should be closed before re-loading.

Value

• An object OraDriver whose class extends DBIDriver and the mixin (helper) class dbObjectId. This object is used to create connections, using the function dbConnect, to one or several Oracle database engines.

Side Effects

The Rclient 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 an ``Oracle SID'' (i.e., a database name); by default the Oracle SID is taken from the environment variable $ORACLE_SID. The function dbConnect allows authentication strings similar to the Oracle monitor SQL*Plus, namely, a string of any of the following forms:

1. "user/passsword"
2. "user/password@dbname"
3. "/"(provided the Oracle server is set up to use the underlying operating system users and passwords);

Prepared statements and data.frame bindings

As of version 0.5-0, ROracle implements Rdata binding to prepared SQL statements. This is done in two stages with the functions dbPrepareStatement and dbExecStatement.

In the first stage of preparing a statement column numbers are embedded inside the SQL statement, e.g., "insert into my_table (id, name, val) VALUES (:1, :3, :2)" and the S class of those columns are specified in the bind= argument to dbPrepareStatement

In the second stage dbExecStatement binds the pre-specified columns from a supplied data= data frame to the SQL statement and the SQL statement is executed once for each row in the input data frame. This step can be repeated with new data as many times as needed.

It is very important to note that typically a prepared statement implicitly will define a new transaction which needs to be explicitly committed with dbCommit or rolled back with dbRollback.

The current implementation allows only primitive types c("numeric", "integer", "logical", "character") for binding.

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

References

For more details on the Rdatabase interface see the PDF file DBI.pdf under the doc directory of this package, http://stat.bell-labs/RS-DBI, and the Omega Project for Statistical Computing at http://www.omegahat.org.

See the documentation at the Oracle Web site http://www.oracle.com for details.

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.

See Also

On database managers:

dbDriver Oracle dbUnloadDriver

On connections:

dbConnect dbDisconnect

On queries, prepared statements, and result objects:

dbSendQuery fetch dbGetQuery dbClearResult dbPrepareStatementdbExecStatement On transaction management:

dbCommit dbRollback

On meta-data:

dbGetInfo summary dbListTables dbListFields dbListConnections dbListResults dbGetException dbGetStatement dbHasCompleted dbGetRowCount dbGetRowsAffected

Examples

## create a Oracle instance and create one connection.
ora <- Oracle()     ## or dbDriver("Oracle")
con <- dbConnect(ora, user = "opto", password="pure-light", db="oras")

## you can also use Oracle's user/password@dbname convention
con2 <- dbConnect(ora, user = "opto/pure-light@oras")

## or if you have defined the ORACLE_SID shell variable
con3 <- dbConnect(ora, user = "opto", password = "pure-light")

## clone an existing connection
w <- dbConnect(con)

## execute a statement and fetch its output in chunks of no more
## than 5000 rows at a time

rs <- dbSendQuery(con, "select * from HTTP_ACCESS where IP_ADDRESS='127.0.0.1'")

while(!dbHasCompleted(rs)){
   df <- fetch(rs, n = 5000)
   process(df)
}
dbHasCompleted(rs)
[1] TRUE
dbClearResult(rs)      ## done with this query
[1] TRUE

## prepare and bind columns 2, 3, and 7 to the Oracle table 
## fields "cell", "erlangs", "blocking"
ps <- dbPrepareStatement(con, 
         "INSERT into my_table (cell, erlangs, blocking) VALUE (:2,:3,:7)",
         bind = my.data.frame)

## execute one sql INSERT per row using columns 2, 3 and 7 
ps <- dbExecStatement(ps, my.data.frame)
ps <- dbExecStatement(ps, more.data)

dbCommit(con)  ## ok, everything looks fine

## a concise description of the driver 
summary(ora)

<OraDriver:(24694)> 
  Driver name:  Oracle (ProC/C++) 
  Max  connections: 10 
  Conn. processed: 9 
  Default records per fetch: 500 
  Open connections: 2 

## a full description of the ora connection
summary(con, verbose = T)

<OraConnection:(25272,0)> 
  User: opto 
  Dbname: oras 
  Oracle Server version: 
    Oracle8 Enterprise Edition Release 8.0.4.0.0 - Production 
    PL/SQL Release 8.0.4.0.0 - Production 
    CORE Version 4.0.4.0.0 - Production 
    TNS for Solaris: Version 8.0.4.0.0 - Production 
    NLSRTL Version 3.3.1.0.0 - Production 

dbDisconnect(con)     ## done with this connection
[1] TRUE