Version: | 1.3-1.1 |
Date: | 2016-10-05 |
Author: | Denis Mukhin, David A. James and Jake Luciani |
Maintainer: | Rajendra S. Pingte <rajendra.pingte@oracle.com> |
Title: | OCI Based Oracle Database Interface for R |
Description: | Oracle Database interface (DBI) driver for R. This is a DBI-compliant Oracle driver based on the OCI. |
SystemRequirements: | Oracle Instant Client or Oracle Database Client |
LazyLoad: | yes |
Depends: | methods, DBI (≥ 0.2-5) |
Imports: | utils |
License: | LGPL-2 | LGPL-2.1 | LGPL-3 [expanded from: LGPL] |
URL: | http://www.oracle.com |
Collate: | dbi.R oci.R zzz.R |
NeedsCompilation: | yes |
Packaged: | 2021-11-10 11:43:18 UTC; hornik |
Repository: | CRAN |
Date/Publication: | 2021-11-10 12:22:19 UTC |
Class ExtDriver
Description
An Oracle extproc driver class implementing the R database interface (DBI) API.
Generators
The main generators are dbDriver
and
Extproc
.
Extends
Class "DBIDriver"
, directly.
Class "DBIObject"
, by class "DBIDriver"
, distance 2.
Methods
- dbConnect
signature(drv = "ExtDriver")
: ...- dbGetInfo
signature(dbObj = "ExtDriver")
: ...- dbListConnections
signature(drv = "ExtDriver")
: ...- dbUnloadDriver
signature(drv = "ExtDriver")
: ...- summary
signature(object = "ExtDriver")
: ...- show
signature(object = "ExtDriver")
See Also
DBI classes:
OraConnection-class
OraResult-class
Examples
## Not run:
con <- dbConnect(Extproc())
## End(Not run)
Class OraConnection
Description
An Oracle connection class implementing the R database interface (DBI) API.
Generators
The method dbConnect
is the main generator.
Extends
Class "DBIConnection"
, directly.
Class "DBIObject"
, by class "DBIConnection"
, distance 2.
Methods
- dbDisconnect
signature(conn = "OraConnection")
: ...- dbSendQuery
signature(conn = "OraConnection", statement = "character", prefetch = FALSE, bulk_read = 1000L, bulk_write = 1000L)
: ...- dbGetQuery
signature(conn = "OraConnection", statement = "character", prefetch = FALSE, bulk_read = 1000L, bulk_write = 1000L)
: ...- dbGetException
signature(conn = "OraConnection")
: ...- dbListResults
signature(conn = "OraConnection")
: ...- dbListTables
signature(conn = "OraConnection")
: ...- dbReadTable
signature(conn = "OraConnection", name = "character")
: ...- dbWriteTable
signature(conn = "OraConnection", name = "character", value = "data.frame")
: ...- dbExistsTable
signature(conn = "OraConnection", name = "character")
: ...- dbRemoveTable
signature(conn = "OraConnection", name = "character")
: ...- dbListFields
signature(conn = "OraConnection", name = "character")
: ...- dbCommit
signature(conn = "OraConnection")
: ...- dbRollback
signature(conn = "OraConnection")
: ...- dbGetInfo
signature(dbObj = "OraConnection")
: ...- summary
signature(object = "OraConnection")
: ...- show
signature(object = "OraConnection")
See Also
DBI classes:
OraDriver-class
OraConnection-class
OraResult-class
Examples
## Not run:
ora <- dbDriver("Oracle")
## connecting without a connect string
con <- dbConnect(ora, "scott", "tiger")
## connecting with a connection string with SID
host <- "myhost"
port <- 1521
sid <- "mysid"
connect.string <- paste(
"(DESCRIPTION=",
"(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
"(CONNECT_DATA=(SID=", sid, ")))", sep = "")
## use username/password authentication
con <- dbConnect(drv, username = "scott", password = "tiger",
dbname = connect.string)
## connecting with a connection string with service name
host <- "myhost"
port <- 1521
svc <- "mydb.example.com"
connect.string <- paste(
"(DESCRIPTION=",
"(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
"(CONNECT_DATA=(SERVICE_NAME=", svc, ")))", sep = "")
## use username/password authentication
con <- dbConnect(drv, username = "scott", password = "tiger",
dbname = connect.string)
## Please refer to "Oracle Database Net Services Administator's Guide", which
## has the topic "Connect Identifier and Connect Descriptor Syntax
## Characteristics"
dbListTables(con)
## End(Not run)
Class OraDriver
Description
An Oracle driver class implementing the R database interface (DBI) API.
Generators
The main generators are dbDriver
and
Oracle
.
Extends
Class "DBIDriver"
, directly.
Class "DBIObject"
, by class "DBIDriver"
, distance 2.
Methods
- dbConnect
signature(drv = "OraDriver")
: ...- dbGetInfo
signature(dbObj = "OraDriver")
: ...- dbListConnections
signature(drv = "OraDriver")
: ...- dbUnloadDriver
signature(drv = "OraDriver")
: ...- summary
signature(object = "OraDriver")
: ...- show
signature(object = "OraDriver")
See Also
DBI classes:
OraConnection-class
OraResult-class
Examples
## Not run:
# first load the library
library("ROracle")
ora <- dbDriver("Oracle")
con <- dbConnect(ora, "scott", "tiger")
## End(Not run)
Class OraResult
Description
An Oracle query results class. This class encapsulates the result of a SQL statement.
Generators
The main generator is dbSendQuery
.
Extends
Class "DBIResult"
, directly.
Class "DBIObject"
, by class "DBIResult"
, distance 2.
Methods
- dbClearResult
signature(res = "OraResult")
: ...- dbColumnInfo
signature(res = "OraResult")
: ...- dbGetInfo
signature(dbObj = "OraResult")
: ...- dbGetStatement
signature(res = "OraResult")
: ...- dbGetRowCount
signature(res = "OraResult")
: ...- dbGetRowsAffected
signature(res = "OraResult")
: ...- dbHasCompleted
signature(res = "OraResult")
: ...- fetch
signature(res = "OraResult", n = "numeric")
: ...- fetch
signature(res = "OraResult", n = "missing")
: ...- execute
signature(res = "OraResult")
: ...- summary
signature(object = "OraResult")
: ...- show
signature(object = "OraResult")
See Also
DBI classes:
OraDriver-class
OraConnection-class
OraResult-class
Examples
## Not run:
ora <- dbDriver("Oracle")
con <- dbConnect(ora, "scott", "tiger")
res <- dbSendQuery(con, "select * from emp")
fetch(res, n = 2)
fetch(res)
dbColumnInfo(res)
dbClearResult(res)
## End(Not run)
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 |
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.
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.
References
For Oracle Database documentation, see http://docs.oracle.com/en/database/.
Author(s)
David A. James and Denis Mukhin
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:
On queries and result objects:
fetch
dbClearResult
dbColumnInfo
dbGetStatement
dbHasCompleted
dbGetRowsAffected
dbGetRowCount
On meta-data:
Examples
## 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)
## End(Not run)
DBMS Transaction Management
Description
Commits or roll backs the current transaction in an Oracle connection
Usage
## S4 method for signature 'OraConnection'
dbCommit(conn, ...)
## S4 method for signature 'OraConnection'
dbRollback(conn, ...)
Arguments
conn |
a |
... |
currently unused. |
Details
- dbCommit
-
implementation saves all changes done on that connection. Changes can not be undone once saved permanently.
- dbRollback
-
implementation undo all chnages done after last savepoint.
Side Effects
- dbCommit
-
saves changes permanently.
- dbRollback
-
undo all changes done after last save point.
References
For the Oracle Database documentaion see http://www.oracle.com/technetwork/indexes/documentation/index.html.
See Also
Oracle
,
dbConnect
,
dbSendQuery
,
dbGetQuery
,
fetch
,
dbCommit
,
dbGetInfo
,
dbReadTable
.
Examples
## Not run:
drv <- dbDriver("Oracle")
con <- dbConnect(drv, "scott", "tiger")
dbReadTable(con, "EMP")
rs <- dbSendQuery(con, "delete from emp where deptno = 10")
dbReadTable(con, "EMP")
if(dbGetInfo(rs, what = "rowsAffected") > 1)
{
warning("dubious deletion -- rolling back transaction")
dbRollback(con)
}
dbReadTable(con, "EMP")
## End(Not run)
Create a Connection Object to an Oracle DBMS
Description
These methods are straightforward implementations of the corresponding generic functions.
Usage
## S4 method for signature 'OraDriver'
dbConnect(drv, username = "", password = "", dbname = "", prefetch = FALSE,
bulk_read = 1000L, bulk_write = 1000L, stmt_cache = 0L,
external_credentials = FALSE, sysdba = FALSE, ...)
## S4 method for signature 'ExtDriver'
dbConnect(drv, prefetch = FALSE, bulk_read = 1000L,
bulk_write = 1000L, stmt_cache = 0L,
external_credentials = FALSE, sysdba = FALSE, ...)
## S4 method for signature 'OraConnection'
dbDisconnect(conn, ...)
Arguments
drv |
An object of class |
conn |
An |
username |
A character string specifying a user name. |
password |
A character string specifying a password. |
dbname |
A character string specifying 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 part of the SQL*Plus connect string that follows the '@' sign. If you are using Oracle Wallet to store username and password, then this string should be the connect string used to create the wallet mapping (for more information, refer to Configuring Clients to Use the External Password Store in Chapter 3 of Oracle Database Security Guide). Connect identifiers for an Oracle TimesTen IMDB instance are supported via the OCI tnsnames or easy connect naming methods. For additional information on TimesTen connections for OCI see chapter 3, TimesTen Support for OCI, of TimesTen In-Memory C Developer's Guide. Examples below show various ways to specify the connect identifier. |
prefetch |
A logical value indicating TRUE or FALSE. When set to TRUE, ROracle will use OCI prefetch buffers to retrieve additional data from the server thus saving memory required in RODBI/ROOCI by allocating a single row buffer to fetch the data from OCI. Using prefetch results in a fetch call for every row. By default, prefetch is FALSE and array fetch is used to retrieve the data from the server. |
bulk_read |
An integer value indicating the number of rows to fetch at a time. The default value is 1000L. When the prefetch option is selected, memory is allocated for prefetch buffers and OCI will fetch that many rows at a time. When prefetch is not used (the default), memory is allocated in RODBI/ROOCI define buffers. Setting this to a large value will result in more memory allocated based on the number of columns in the select list and the types of columns. For a column of type character, define buffers are allocated using the maximum width times the NLS maximum width. Applications should adjust this value based on the query result and a larger value will benefit queries that return a large result. An application can tune this value as needed. |
bulk_write |
An integer value indicating the number of rows to insert, update or delete at a time. The default value is 1000L. When the bulk_write value is given in argument, memory is allocated for buffers and OCI will write that many rows at a time. When bulk_write argument is not given, the default value 1000 is used to allocate memory for the bind buffers. Setting this to a large value will result in more memory allocated based on the number of columns in the insert list and the types of columns. |
stmt_cache |
An integer value indicating the number of statements to cache. It means
that cursors are ready to be used without the need to parse the statements
again. The default value is 0L. If stmt_cache value is greater than 0L then
|
external_credentials |
A logical value indicating TRUE or FALSE. When set to TRUE, ROracle will begin OCI session authenticated with external credentials on the connection. The default value is FALSE. |
sysdba |
A logical value indicating TRUE or FALSE. When set to TRUE, ROracle will begin OCI session with SYSDBA privileges on the connection. The default value is FALSE. |
... |
Currently unused. |
Details
- dbConnect
-
This connection object is used to execute operations on the database.
When
prefetch
is set to TRUE, it allows the use of the OCI prefetch buffer to retrieve additional data from the server.The
bulk_read
argument is used to set an integer value indicating the number of rows to fetch at a time.The
bulk_write
argument is used to set an integer value indicating the number of rows to write at a time.The
stmt_cache
argument is used to enable or disable the statement caching feature. Its value specifies the statement cache size.The
external_credentials
argument is used to begin OCI session authenticated with external credentials on the connection.The
sysdba
argument is used to begin OCI session with SYSDBA privileges on the connection.When establishing a connection with an
ExtDriver
driver, none of the arguments specifying credentials are used. A connection in this mode is a singleton object, that is, all calls todbConnect
return the same connection object. - dbDisconnect
-
This implementation disconnects the connection between R and the database server. It frees all resources used by the connection object. It frees all result sets associated with this connection object.
Value
dbConnect |
An object |
dbDisconnect |
A logical value indicating whether the operation succeeded or not. |
Side Effects
- dbConnect
-
Establishes a connection between R and an Oracle Database server.
- dbDisconnect
-
Frees resources used by the connection object.
References
For the Oracle Database documentation see http://www.oracle.com/technetwork/indexes/documentation/index.html.
See Also
Oracle
,
dbConnect
,
dbSendQuery
,
dbGetQuery
,
fetch
,
dbCommit
,
dbGetInfo
,
dbReadTable
.
Examples
## Not run:
## Create an Oracle Database instance and create one connection on the
## same machine.
drv <- dbDriver("Oracle")
## Use username/password authentication.
con <- dbConnect(drv, username = "scott", password = "tiger")
## Run a SQL statement by creating first a resultSet object.
rs <- dbSendQuery(con, "select * from emp where deptno = 10")
## We now fetch records from the resultSet into a data.frame.
data <- fetch(rs) ## extract all rows
dim(data)
## End(Not run)
## Not run:
## Create an Oracle Database instance and create one connection to a
## remote database using the SID in the connect string.
drv <- dbDriver("Oracle")
## Refer to Oracle Database Net Services Administator's Guide for
## details on connect string specification.
host <- "myhost"
port <- 1521
sid <- "mysid"
connect.string <- paste(
"(DESCRIPTION=",
"(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
"(CONNECT_DATA=(SID=", sid, ")))", sep = "")
## Use username/password authentication.
con <- dbConnect(drv, username = "scott", password = "tiger",
dbname = connect.string)
## Run a SQL statement by creating first a resultSet object.
rs <- dbSendQuery(con, "select * from emp where deptno = 10")
## We now fetch records from the resultSet into a data.frame.
data <- fetch(rs) ## extract all rows
dim(data)
## End(Not run)
## Not run:
## Create an Oracle Database instance and create one connection to a
## remote database using the service name.
drv <- dbDriver("Oracle")
## Refer to Oracle Database Net Services Administator's Guide for
## details on connect string specification.
host <- "myhost"
port <- 1521
svc <- "mydb.example.com"
connect.string <- paste(
"(DESCRIPTION=",
"(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
"(CONNECT_DATA=(SERVICE_NAME=", svc, ")))", sep = "")
## Use username/password authentication.
con <- dbConnect(drv, username = "scott", password = "tiger",
dbname = connect.string)
## Run a SQL statement by creating first a resultSet object.
rs <- dbSendQuery(con, "select * from emp where deptno = 10")
## We now fetch records from the resultSet into a data.frame.
data <- fetch(rs) ## extract all rows
dim(data)
## End(Not run)
## Not run:
## Create an Oracle Database instance and create one connection.
drv <- dbDriver("Oracle")
## Use Oracle Wallet authentication.
con <- dbConnect(drv, username ="", password="",
dbname = "<wallet_connect_string>")
## Run a SQL statement by creating first a resultSet object.
rs <- dbSendQuery(con, "select * from emp where deptno = 10")
## We now fetch records from the resultSet into a data.frame.
data <- fetch(rs) ## extract all rows
dim(data)
## End(Not run)
## Not run:
## Create an Oracle Database instance and create one connection.
drv <- dbDriver("Oracle")
## Connect to a TimesTen IMDB instance using the easy connect
## naming method where SampleDb is a direct driver TimesTen DSN.
con <- dbConnect(drv, username ="scott", password="tiger",
dbname = "localhost/SampleDb:timesten_direct")
## Run a SQL statement by creating first a resultSet object.
rs <- dbSendQuery(con, "select * from dual")
## We now fetch records from the resultSet into a data.frame.
data <- fetch(rs) ## extract all rows
dim(data)
## End(Not run)
## Not run:
## Connect to an extproc (this assumes that the driver has already
## been initialized in the embedded R code by passing an external
## pointer representing the extproc context).
con <- dbConnect(Extproc())
## Run a SQL statement by creating first a resultSet object.
rs <- dbSendQuery(con, "select * from dual")
## We now fetch records from the resultSet into a data.frame.
data <- fetch(rs) ## extract all rows
dim(data)
## End(Not run)
## Not run:
## Create an Oracle Database instance and create one connection.
drv <- dbDriver("Oracle")
## Create connection with SYSDBA privileges.
con <- dbConnect(drv, username ="scott", password="tiger",
sysdba = TRUE)
## Run a SQL statement by creating first a resultSet object.
rs <- dbSendQuery(con, "select * from emp where deptno = 10")
## We now fetch records from the resultSet into a data.frame.
data <- fetch(rs) ## extract all rows
dim(data)
## End(Not run)
## Not run:
## Create an Oracle Database instance and create one connection.
drv <- dbDriver("Oracle")
## Use OS authentication as an example of external authentication
## Make sure that databse user exist to allow an OS authentication
## Create connection authenticated with external credentials.
con <- dbConnect(drv, username ="", password="",
external_credentials = TRUE)
## Above dbConnect() used OS credentials to connect with database.
## Run a SQL statement by creating first a resultSet object.
rs <- dbSendQuery(con, "select * from emp where deptno = 10")
## We now fetch records from the resultSet into a data.frame.
data <- fetch(rs) ## extract all rows
dim(data)
## End(Not run)
Oracle Implementation of the Database Interface (DBI) Classes and Drivers
Description
Oracle driver initialization and closing.
Usage
## S4 method for signature 'OraDriver'
dbUnloadDriver(drv, ...)
## S4 method for signature 'ExtDriver'
dbUnloadDriver(drv, ...)
Arguments
drv |
An object that inherits from |
... |
Any other arguments to pass to the driver |
Details
- dbDriver
-
This object is a singleton, that is, subsequent invocations of
dbDriver
return 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 specified by the NLS_LANG setting. By default,unicode_as_utf8
is set to TRUE.When
ora.attributes
is set to TRUE, the result set fromdbGetQuery
andfetch
contains DBMS-specific attributes likeora.encoding
,ora.type
, andora.maxlength
for the corresponding column. - dbUnloadDriver
-
This implementation removes communication links between the R client and the database. It frees all connections and all result sets associated with those connection objects.
Value
dbDriver |
An object |
dbUnloadDriver |
Free all resources occupied by the driver object. |
Side Effects
- dbDriver
-
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
. - dbUnloadDriver
-
Remove the communication link between the R client and the database.
References
For Oracle Database documentation, see http://www.oracle.com/technetwork/indexes/documentation/index.html.
See Also
Oracle
,
dbConnect
,
dbSendQuery
,
dbGetQuery
,
fetch
,
dbCommit
,
dbGetInfo
,
dbListTables
,
dbReadTable
.
Examples
## Not run:
# first load the library
library("ROracle")
# create an Oracle instance
drv <- dbDriver("Oracle")
con <- dbConnect(drv, "scott", "tiger")
dbListTables(con)
# fetch all results from the emp table
res <- dbGetQuery(con, "select * from emp")
# dbSendQuery can be used to fetch data in chunks
# as well as all of data at once
res <- dbSendQuery(con, "select * from emp")
# fetch all results
fetch(res)
# or a chunk at a time
fetch(res, n = 5)
# describing the schema for the emp table using dbGetInfo
dbGetInfo(res, what = 'fields')
# clear the result
dbClearResult(res)
# disconnect from the database
dbDisconnect(con)
# free the driver object
dbUnloadDriver(drv)
## End(Not run)
Database interface Metadata
Description
These methods are straight-forward implementations of the corresponding generic functions.
Usage
## S4 method for signature 'OraDriver'
dbGetInfo(dbObj, ...)
## S4 method for signature 'ExtDriver'
dbGetInfo(dbObj, ...)
## S4 method for signature 'OraConnection'
dbGetInfo(dbObj, what, ...)
## S4 method for signature 'OraResult'
dbGetInfo(dbObj, what, ...)
## S4 method for signature 'OraResult'
dbGetStatement(res, ...)
## S4 method for signature 'OraResult'
dbGetRowCount(res, ...)
## S4 method for signature 'OraResult'
dbGetRowsAffected(res, ...)
## S4 method for signature 'OraResult'
dbColumnInfo(res, ...)
## S4 method for signature 'OraResult'
dbHasCompleted(res)
Arguments
dbObj |
Any object that implements some functionality in the R interface to databases (a driver, a connection, or a result set). |
what |
A character string specifying an element of the output list. |
res |
An |
... |
Currently unused. |
Details
Table, schema, and column names are case sensitive, for example, table
names ABC
and abc
are not the same. All database schema
object names should not include double quotes as they are enclosed in
double quotes when the corresponding SQL statement is generated.
The ROracle method dbGetInfo
provides following details about
the driver object:
-
\$driverName The name of the driver, "Oracle (OCI)"
-
\$driverVersion The version of the ROracle driver used
-
\$clientVersion The version of the Oracle Client library used
-
\$conTotal The number of connections instantiated by the driver
-
\$conOpen The number of connections open currently
-
\$interruptible TRUE when a long-running query can be interrupted
-
\$unicode_as_utf8 TRUE when character data is to be fetched in UTF8 encoded format
-
\$ora_attributes TRUE when each column in a result set data frame has corresponding Oracle DBMS attributes
-
\$connections Information about each connection currently open, see
dbGetInfo
of connection object for details
The ROracle method dbGetInfo
provides following the details
about the connection object:
-
\$username The name of the user on this connection
-
\$dbname The connect alias or the connect string used
-
\$serverVersion The version of the Oracle Database server
-
\$serverType The value "Oracle RDBMS"
-
\$resTotal The number of result sets on this connection
-
\$resOpen The number of result sets open on this connection
-
\$prefetch TRUE when prefetch mechanism is used to fetch data
-
\$bulk_read The number of rows to fetch at a time from DBMS
-
\$bulk_write The number of rows to write at a time to DBMS
-
\$stmt_cache TRUE when the statement cache is used
-
\$results Information about each result set currently open, see
dbGetInfo
of result set for details
The ROracle method dbGetInfo
provides the following details
about the result set object:
-
\$statement SQL statement used to produce the result set object
-
\$isSelect TRUE when a select statement is specified
-
\$rowsAffected The number of rows affected by DML statment
-
\$rowCount The number of rows in result set currently
-
\$completed TRUE if there are no more rows in the result set
-
\$prefetch TRUE when the prefetch mechanism used to fetch data
-
\$bulk_read The number of rows to fetch at a time from DBMS
-
\$bulk_write The number of rows to write at a time to DBMS
-
\$fields Information about each column in the the result set, see
dbColumnInfo
for details
The ROracle method dbColumnInfo
provides following details
about each column in the result set:
-
\$name The name of the column
-
\$Sclass The R type of the object containing the data returned by the Oracle RDBMS
-
\$type The type of column as created in Oracle RDBMS
-
\$len Length of VARCHAR, CHAR and RAW column type in Oracle RDBMS. All other columns will have NA.
-
\$precision The precision of number column
-
\$scale The scale of number column
-
\$nullOK TRUE when a NULL value can be present in the column
The example below shows the driver, connection, result set, and column information for a table containing:
create table foo( a number(21), b number, c char(20), d varchar(300), e binary_double, f binary_float, g clob, h blob, i bfile, j date, m timestamp, n timestamp with time zone, o timestamp with local time zone, r interval day to second, s raw(234) );
library(ROracle) Loading required package: DBI > # instantiate ROracle driver object > drv <- Oracle() > con <- dbConnect(drv, "scott", "tiger") > rs <- dbSendQuery(con, "select * from foo") > dbGetInfo(drv) $driverName [1] "Oracle (OCI)" $driverVersion [1] "1.1-12" $clientVersion [1] "11.2.0.4.0" $conTotal [1] 1 $conOpen [1] 1 $interruptible [1] FALSE $unicode_as_utf8 [1] TRUE $ora_attributes [1] TRUE $connections $connections[[1]] User name: scott Connect string: Server version: 11.2.0.4.0 Server type: Oracle RDBMS Results processed: 1 OCI prefetch: FALSE Bulk read: 1000 Bulk write: 1000 Statement cache size: 0 Open results: 1 > dbGetInfo(con) $username [1] "scott" $dbname [1] "" $serverVersion [1] "11.2.0.4.0" $serverType [1] "Oracle RDBMS" $resTotal [1] 1 $resOpen [1] 1 $prefetch [1] FALSE $bulk_read [1] 1000 $bulk_write [1] 1000 $stmt_cache [1] 0 $results $results[[1]] Statement: select * from foo Rows affected: 0 Row count: 0 Select statement: TRUE Statement completed: FALSE OCI prefetch: FALSE Bulk read: 1000 Bulk write: 1000 > dbGetInfo(rs) $statement [1] "select * from foo" $isSelect [1] TRUE $rowsAffected [1] 0 $rowCount [1] 0 $completed [1] FALSE $prefetch [1] FALSE $bulk_read [1] 1000 $bulk_write [1] 1000 $fields name Sclass type len precision scale nullOK 1 A numeric NUMBER NA 21 0 TRUE 2 B numeric NUMBER NA 0 -127 TRUE 3 C character CHAR 20 0 0 TRUE 4 D character VARCHAR2 300 0 0 TRUE 5 E numeric BINARY_DOUBLE NA 0 0 TRUE 6 F numeric BINARY_FLOAT NA 0 0 TRUE 7 G character CLOB NA 0 0 TRUE 8 H raw BLOB NA 0 0 TRUE 9 I raw BFILE NA 0 0 TRUE 10 J POSIXct DATE NA 0 0 TRUE 11 M POSIXct TIMESTAMP NA 0 6 TRUE 12 N POSIXct TIMESTAMP WITH TIME ZONE NA 0 6 TRUE 13 O POSIXct TIMESTAMP WITH LOCAL TIME ZONE NA 0 6 TRUE 14 R difftime INTERVAL DAY TO SECOND NA 2 6 TRUE 15 S raw RAW 234 0 0 TRUE
Value
Information about driver, connection or a result set object.
References
For the Oracle Database documentaion see http://www.oracle.com/technetwork/indexes/documentation/index.html.
See Also
Oracle
,
dbDriver
,
dbConnect
,
dbSendQuery
,
dbGetQuery
,
fetch
,
dbCommit
,
dbGetInfo
,
dbListTables
,
dbReadTable
.
Examples
## Not run:
drv <- dbDriver("Oracle")
con <- dbConnect(drv, "scott", "tiger")
rs <- dbSendQuery(con, "select * from emp")
# Get the SQL statement for the result set object rs
dbGetStatement(rs)
# Are there any more rows in result set?
dbHasCompleted(rs)
# Information about columns in result set rs object
dbColumnInfo(rs)
# DBIDriver info
names(dbGetInfo(drv))
# DBIConnection info
names(dbGetInfo(con))
# DBIResult info
names(dbGetInfo(rs))
## End(Not run)
List items from Oracle objects
Description
These methods are straight-forward implementations of the corresponding generic functions.
Usage
## S4 method for signature 'OraDriver'
dbListConnections(drv, ...)
## S4 method for signature 'ExtDriver'
dbListConnections(drv, ...)
## S4 method for signature 'OraConnection'
dbListResults(conn, ...)
Arguments
drv |
an |
conn |
an |
... |
currently unused. |
Details
- dbListConnections
-
implementation return a list of all associated connections. It shows information about all associated connections.
- dbListResults
-
implementation return a list of all associated result sets. It shows information about all associated result sets.
Value
dbListConnections |
A list of all connections associated with driver. |
dbListResults |
A list of all result sets associated with connection. |
References
For the Oracle Database documentaion see http://www.oracle.com/technetwork/indexes/documentation/index.html.
See Also
Oracle
,
dbGetInfo
,
dbColumnInfo
,
dbDriver
,
dbConnect
,
dbSendQuery
Examples
## Not run:
drv <- dbDriver("Oracle")
con1 <- dbConnect(drv, "scott", "tiger")
res1 <- dbSendQuery(con1, "select * from emp where deptno = 10")
res2 <- dbSendQuery(con1, "select * from emp where deptno = 20")
con2 <- dbConnect(drv, "scott", "tiger")
res3 <- dbSendQuery(con2, "select * from dept")
## get all active statements
for(con in dbListConnections(drv))
for (res in dbListResults(con))
print(dbGetStatement(res))
## End(Not run)
Convenience Functions for Manipulating DBMS Tables
Description
These functions mimic their R counterparts except that they generate
code that gets remotely executed in a database engine:
get
,
assign
,
exists
,
remove
,
objects
, and
names
.
Usage
## S4 method for signature 'OraConnection,character'
dbReadTable(conn, name, schema = NULL, row.names = NULL, ...)
## S4 method for signature 'OraConnection,character,data.frame'
dbWriteTable(conn, name, value, row.names = FALSE, overwrite = FALSE,
append = FALSE, ora.number = TRUE, schema = NULL, date = FALSE, ...)
## S4 method for signature 'OraConnection,character'
dbExistsTable(conn, name, schema = NULL, ...)
## S4 method for signature 'OraConnection,character'
dbRemoveTable(conn, name, purge = FALSE, schema = NULL, ...)
## S4 method for signature 'OraConnection'
dbListTables(conn, schema = NULL, all = FALSE, full = FALSE, ...)
## S4 method for signature 'OraConnection,character'
dbListFields(conn, name, schema = NULL, ...)
Arguments
conn |
An |
name |
A case-sensitive character string specifying a table name. |
schema |
A case-sensitive character string specifying a schema name (or a
vector of character strings for |
date |
A boolean flag to indicate whether to use date or DateTimep. By default, DateTime will be used instead of timestamp. |
row.names |
In the case of In the case of |
value |
A |
overwrite |
A logical value specifying whether to overwrite an existing table
or not. The default is |
append |
A logical value specifying whether to append to an existing table
in the DBMS. The default is |
ora.number |
A logical value specifying whether to create a table with Oracle
|
purge |
A logical value specifying whether to add the |
all |
A logical value specifying whether to look at all schemas. |
full |
A logical value specifying whether to generate schema names. When argument
|
... |
currently unused. |
Details
Table, schema, and column names are case sensitive, e.g., table
names ABC
and abc
are not the same. All database schema
object names should not include double quotes as they are enclosed in
double quotes when the corresponding SQL statement is generated.
The following attributes are used for mapping BLOB, CLOB, NCLOB, NCHAR, VARCHAR2,
NVARCHAR2, CHAR, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE
columns correctly in dbWriteTable
:
1) Attribute Name: ora.type
This attribute indicates the type of the underlying column and can be
"CLOB", "BLOB", "CHAR", "VARCHAR2", or "RAW". The user can specify
TIMESTAMP, DATE, TIMESTAMP WITH TIME ZONE or any other column types
supported by Oracle Database. ROacle does not parse the value; it is
validated by the database. The user can provide one of the following
values for ora.type
: CLOB, BLOB, CHAR, TIMESTAMP WITH TIME
ZONE, TIMESTAMP WITH LOCAL TIME ZONE and fractional_seconds_precision.
2) Attribute Name: ora.encoding
When UTF-8 is specified, the database uses NCLOB, NCHAR or NVARCHAR based on
ora.type
.
3) Attribute Name: ora.maxlength
One can specify the maximum length of CHAR, VARCHAR, NCHAR, NVARCHAR2, or
RAW columns. For other data types, ora.maxlength
does not
apply and is ignored. The following default values are used for
certain data types when ora.maxlength
is not specified.
CHAR 2000
NCHAR 1000
VARCHAR2 4000
NVARCHAR2 2000
RAW 2000
4) Attribute Name: ora.fractional_seconds_precision
One can specify the fractional part of the SECOND datetime field of
TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE
columns. It can be a number in the range 0 to 9. The default value is 6.
ROracle methods such as dbReadTable
, dbGetQuery
,
fetch
, and dbWriteTable
use the following mapping
between R and Oracle data types:
-
logical
andinteger
map to OracleINTEGER
-
numeric
maps to OracleNUMBER
if argumentora.number
isTRUE
or OracleBINARY_DOUBLE
ifFALSE
-
character
maps to OracleCLOB
if attributeora.type
is"CLOB"
or OracleNCLOB
if attributeora.type
is"CLOB"
andora.encoding
is"UTF-8"
or OracleCHAR(ora.maxlength)
if attributeora.type
is"CHAR"
or OracleNCHAR(ora.maxlength)
if attributeora.type
is"CHAR"
andora.encoding
is"UTF-8"
or OracleNVARCHAR2(ora.maxlength)
if attributeora.encoding
is"UTF-8"
or OracleVARCHAR2(ora.maxlength)
-
Date
andPOSIXct
map to OracleDATE
ROracle - the ROracle package R - the R application -
POSIXct
maps to OracleTIMESTAMP WITH TIME ZONE(ora.fractional_seconds_precision)
if attributeora.type
is"TIMESTAMP WITH TIME ZONE"
or OracleTIMESTAMP WITH LOCAL TIME ZONE(ora.fractional_seconds_precision)
if attributeora.type
is"TIMESTAMP WITH LOCAL TIME ZONE"
or OracleTIMESTAMP(ora.fractional_seconds_precision)
and ifDate
isFALSE
-
difftime
maps to OracleINTERVAL DAY TO SECOND
-
list
ofraw
vectors map to OracleBLOB
if attributeora.type
is"BLOB"
or OracleRAW(ora.maxlength)
other R types such as
factor
are converted tocharacter
ROracle returns values from database columns that are of data type: date, time stamp, time stamp with time zone and time stamp with local time zone data types in R's POSIXct format. POSIXct refers to a time that is internally stored as the number of seconds since the start of 1970 in UTC. Number of seconds are exchanged from R and ROracle driver in floating point double format. In POSIXct representation R uses the TZ environment variable or maps the OS time zone environment variable to its own, therefore the date will be displayed in this time zone format.
One can insert data into columns of the four data types listed above using a string with the correct format or POSIXct representation. String data is passed to the database directly and ROracle relies on databse to convert it to date time representation. ROracle driver converts the POSIXct representation to a string representation using the format "%Y-%m-%d %H:%M:%OS6" in a data frame that is used for DML operations. Data in this format corresponds to NLS_TIMESTAMP_TZ_FORMAT "YYYY-MM-DD HH24:MI:SSXFF" and is converted to SQLT_TIMESTAMP_LTZ to be bound to the Oracle database. An intermediate class "datetime" is created that represents character data to the ROracle driver internally.
Columns having a date and time stamp data type are fetched by ROracle using the SQLT_TIMESTAMP data type. Columns having a time stamp with time zone or a time stamp with local time zone data types are fetched using SQLT_TIMESTAMP_LTZ data type. Columns of data type time stamp with local time zone undergo conversion to the session time zone that the R application runs in, therefore setting the time zone environment TZ in R will affect the data values in this column. ROracle driver maps the TZ environment variable to the session time zone and issues an alter DDL to set the session time zone when the connection is made to the database.
To fetch data from columns with a timestamp with time zone or a timestamp with local time zone, the client and server must have the same time zone data file else an error will be reported.
When these data types are selected from the database, they are converted to string representation using the NLS_TIMESTAMP_TZ_FORMAT "YYYY-MM-DD HH24:MI:SSXFF" that corresponds to "%Y-%m-%d %H:%M:%OS6" in R. An intermediate class "datetime" is created to represent this character data in ROracle driver. ROracle driver then converts it to POSIXct using the as.POSIXct() function. An R application sees the data in POSIXct form in the data frame.
R session time zone: R has the concept of a time zone in which the R engine operates. The time zone can be set to a string such as 'PST8PDT', 'America/Los_Angeles' and so on. These strings are self-explanatory and specify the time zone in which the session is operating. The R session time zone can be set in one of two ways: 1. By entering the following on the Linux or Unix command line before starting R: setenv TZ = America/Los_Angeles on Linux/UNIX NOTE: Do not use this option on Windows as it does not allow one to set Oracle compatible timezone names for the environment variable TZ. 2. By entering the following at the R prompt: Sys.setenv(TZ = "PST8PDT") We recommend using the option 2 as the R script works without any porting issues on Linux/Unix as well as Windows. Option 2 also allows you to specify Oracle compatible timezone names even on Windows. The R session time zone determines the time zone for all POSIXct time zone unqualified date-time types. It is also the time zone to which all qualified date-time types are converted when they are displayed by R. The following example demonstrates this. Sys.setenv(TZ = "PST8PDT") dt <- c(as.POSIXct("2010/3/13", tz = "PST8PDT"), as.POSIXct("2010/3/13 3:47:30.123456", tz = "PST8PDT"), as.POSIXct("2010/3/22", tz = "PST8PDT"), as.POSIXct("2010/3/22 7:02:30", tz = "PST8PDT"), as.POSIXct("2010/3/13"), as.POSIXct("2010/3/13 3:47:30.123456"), as.POSIXct("2010/3/22"), as.POSIXct("2010/3/22 7:02:30")) dt [1] "2010-03-13 00:00:00.000000 PST" "2010-03-13 03:47:30.123456 PST" [3] "2010-03-22 00:00:00.000000 PDT" "2010-03-22 07:02:30.000000 PDT" [5] "2010-03-13 00:00:00.000000 PST" "2010-03-13 03:47:30.123456 PST" [7] "2010-03-22 00:00:00.000000 PDT" "2010-03-22 07:02:30.000000 PDT" Note that the unqualified timestamps are also assumed to be in the R's session time zone when they are displayed by R. Of course, R is also smart enough to make the determination of whether the time falls into PST or PDT based on when US Daylight savings begins, and displays PST or PDT accordingly. The following example makes this more obvious. > Sys.setenv(TZ = "EST5EDT") > dt <- c(as.POSIXct("2010/3/13", tz = "PST8PDT"), + as.POSIXct("2010/3/13 3:47:30.123456", tz = "PST8PDT"), + as.POSIXct("2010/3/22", tz = "PST8PDT"), + as.POSIXct("2010/3/22 7:02:30", tz = "PST8PDT"), + as.POSIXct("2010/3/13"), + as.POSIXct("2010/3/13 3:47:30.123456"), + as.POSIXct("2010/3/22"), + as.POSIXct("2010/3/22 7:02:30")) > dt [1] "2010-03-13 03:00:00.000000 EST" "2010-03-13 06:47:30.123456 EST" [3] "2010-03-22 03:00:00.000000 EDT" "2010-03-22 10:02:30.000000 EDT" [5] "2010-03-13 00:00:00.000000 EST" "2010-03-13 03:47:30.123456 EST" [7] "2010-03-22 00:00:00.000000 EDT" "2010-03-22 07:02:30.000000 EDT" Note that all the time zone unqualified timestamps are assumed to be in the session time zone. However, even the time zone qualified timestamps are converted to session time zone and displayed. Note that all the values are displayed by R in the R session's time zone (with the timezone name also modified to EST or EDT to account for daylight savings as applicable). Refer to Date-Time Classes at http://stat.ethz.ch/R-manual/R-devel/library/base/html/DateTimeClasses.html and timezones at: http://stat.ethz.ch/R-manual/R-devel/library/base/html/timezones.html for details on how R handles dates and times and time zones) Let's take an example where we use a longer time zone name (often referred to as an 'Olson Name') as opposed to an abbreviation. > Sys.setenv(TZ = "America/Los_Angeles") > dt <- c(as.POSIXct("2010/3/13", tz = "PST8PDT"), + as.POSIXct("2010/3/13 3:47:30.123456", tz = "PST8PDT"), + as.POSIXct("2010/3/22", tz = "PST8PDT"), + as.POSIXct("2010/3/22 7:02:30", tz = "PST8PDT"), + as.POSIXct("2010/3/13"), + as.POSIXct("2010/3/13 3:47:30.123456"), + as.POSIXct("2010/3/22"), + as.POSIXct("2010/3/22 7:02:30")) > dt [1] "2010-03-13 00:00:00.000000 PST" "2010-03-13 03:47:30.123456 PST" [3] "2010-03-22 00:00:00.000000 PDT" "2010-03-22 07:02:30.000000 PDT" [5] "2010-03-13 00:00:00.000000 PST" "2010-03-13 03:47:30.123456 PST" [7] "2010-03-22 00:00:00.000000 PDT" "2010-03-22 07:02:30.000000 PDT" Note that in such a case, R doesn't use the long name when the values are displayed, but instead still displays the values using the abbreviations "PST" and "PDT". This is significant because Oracle doesn't necessarily like these abbreviations. For example, an Oracle databse doesn't recognize "PDT" as a valid time zone. See "R Time zone and Oracle session time zone" for details on valid time zones.
The example below shows the effect of changing the time zone in R environment:
R> Sys.timezone() [1] "PST8PDT" # Selecting data and displaying it res <- dbGetQuery(con, selStr) R> res[,1] [1] 1 2 3 4 5 6 R> res[,2] [1] "2012-06-05 00:00:00 PDT" "2012-01-05 07:15:02 PST" "2012-01-05 00:00:00 PST" "2011-01-05 00:00:00 PST" [5] "2013-01-05 00:00:00 PST" "2020-01-05 00:00:00 PST" R> res[,3] [1] "2012-06-05 00:00:00 PDT" "2012-01-05 07:15:03 PST" "2012-01-05 00:00:00 PST" "2011-01-05 00:00:00 PST" [5] "2013-01-05 00:00:00 PST" "2020-01-05 00:00:00 PST" R> res[,4] [1] "2012-06-05 00:00:00 PDT" "2012-01-05 07:15:03 PST" "2012-01-05 00:00:00 PST" "2011-01-05 00:00:00 PST" [5] "2013-01-05 00:00:00 PST" "2020-01-05 00:00:00 PST" R> res[,5] [1] "2012-06-05 00:00:00 PDT" "2012-01-05 07:15:03 PST" "2012-01-05 00:00:00 PST" "2011-01-05 00:00:00 PST" [5] "2013-01-05 00:00:00 PST" "2020-01-05 00:00:00 PST" R> Sys.setenv(TZ='EST5EDT') R> res[,1] [1] 1 2 3 4 5 6 R> res[,2] [1] "2012-06-05 03:00:00 EDT" "2012-01-05 10:15:02 EST" "2012-01-05 03:00:00 EST" "2011-01-05 03:00:00 EST" [5] "2013-01-05 03:00:00 EST" "2020-01-05 03:00:00 EST" R> res[,3] [1] "2012-06-05 03:00:00 EDT" "2012-01-05 10:15:03 EST" "2012-01-05 03:00:00 EST" "2011-01-05 03:00:00 EST" [5] "2013-01-05 03:00:00 EST" "2020-01-05 03:00:00 EST" R> res[,4] [1] "2012-06-05 03:00:00 EDT" "2012-01-05 10:15:03 EST" "2012-01-05 03:00:00 EST" "2011-01-05 03:00:00 EST" [5] "2013-01-05 03:00:00 EST" "2020-01-05 03:00:00 EST" R> res[,5] [1] "2012-06-05 03:00:00 EDT" "2012-01-05 10:15:03 EST" "2012-01-05 03:00:00 EST" "2011-01-05 03:00:00 EST" [5] "2013-01-05 03:00:00 EST" "2020-01-05 03:00:00 EST"
Also dbWriteTable
always auto commits a current transaction as
well as the data it inserts, i.e. it acts as a DDL statement even if
appends rows to an already existing table.
Value
A data.frame
in the case of dbReadTable
;
a vector
in the case of dbListTables
and
dbListFields
;
a logical
in the case of dbExistsTable
indicating
whether the table exists;
otherwise TRUE
when the operation was successful or an
exception.
References
For the Oracle Database documentation see http://www.oracle.com/technetwork/indexes/documentation/index.html. For Datetime Data Types and Time Zone Support in Oracle see http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm.
See Also
Oracle
,
dbDriver
,
dbConnect
,
dbSendQuery
,
dbGetQuery
,
fetch
,
dbCommit
,
dbGetInfo
.
Examples
## Not run:
con <- dbConnect(Oracle(), "scott", "tiger")
if (dbExistsTable(con, "FOO", "SCOTT"))
dbRemoveTable(con, "FOO")
foo <- dbReadTable(con, "EMP")
row.names(foo) <- foo$EMPNO
foo <- foo[,-1]
dbWriteTable(con, "FOO", foo, row.names = TRUE)
dbWriteTable(con, "FOO", foo, row.names = TRUE, overwrite = TRUE)
dbReadTable(con, "FOO", row.names = 1)
dbGetQuery(con, "delete from foo")
dbWriteTable(con, "FOO", foo, row.names = TRUE, append = TRUE)
dbReadTable(con, "FOO", row.names = 1)
dbRemoveTable(con, "FOO")
dbListTables(con)
dbListFields(con, "EMP")
if (dbExistsTable(con, "RORACLE_TEST", "SCOTT"))
dbRemoveTable(con, "RORACLE_TEST")
# Example of POSIXct usage.
# A table is created using:
createTab <- "create table RORACLE_TEST(row_num number, id1 date,
id2 timestamp, id3 timestamp with time zone,
id4 timestamp with local time zone )"
dbGetQuery(con, createTab)
# Insert statement.
insStr <- "insert into RORACLE_TEST values(:1, :2, :3, :4, :5)";
# Select statement.
selStr <- "select * from RORACLE_TEST";
# Insert time stamp without time values in POSIXct form.
x <- 1;
y <- "2012-06-05";
y <- as.POSIXct(y);
dbGetQuery(con, insStr, data.frame(x, y, y, y, y));
# Insert date & times stamp with time values in POSIXct form.
x <- 2;
y <- "2012-01-05 07:15:02";
y <- as.POSIXct(y);
z <- "2012-01-05 07:15:03.123";
z <- as.POSIXct(z);
dbGetQuery(con, insStr, data.frame(x, y, z, z, z));
# Insert list of date objects in POSIXct form.
x <- c(3, 4, 5, 6);
y <- c('2012-01-05', '2011-01-05', '2013-01-05', '2020-01-05');
y <- as.POSIXct(y);
dbGetQuery(con, insStr, data.frame(x, y, y, y, y));
dbCommit (con)
# Selecting data and displaying it.
res <- dbGetQuery(con, selStr)
res[,1]
res[,2]
res[,3]
res[,4]
res[,5]
# insert data in Date format
a<-as.Date("2014-01-01")
dbWriteTable(con, 'TEMP', data.frame(a), date = TRUE)
# using attribute to map NCHAR, CLOB, BLOB, NCLOB columns correctly in
# dbWriteTable
str1 <- paste(letters, collapse="")
lstr1 <- paste(rep(str1, 200), collapse="")
raw.lst <- vector("list",1)
lraw.lst <- vector("list",1)
raw.lst[[1L]] <- charToRaw(str1)
lraw.lst[[1L]] <- rep(charToRaw(str1), 200)
a <- as.POSIXct("2014-01-01 14:12:09.0194733")
b <- as.POSIXct("2014-01-01 14:12:09.01947")
test.df <- data.frame(char=str1, nchar=str1, varchar=str1, clob=lstr1,
nclob=lstr1, stringsAsFactors=FALSE)
test.df$raw.typ <- raw.lst
test.df$blob <- lraw.lst
test.df$char_max <- str1
test.df$raw_max.typ <- raw.lst
test.df$nvchar <- str1
test.df$nvchar_max <- str1
test.df$date_tz <- a
test.df$date_ltz <- b
# adding attributes
attr(test.df$clob, "ora.type") <- "CLOB"
attr(test.df$blob, "ora.type") <- "BLOB"
attr(test.df$nclob, "ora.type") <- "CLOB"
attr(test.df$nclob, "ora.encoding") <- "UTF-8"
attr(test.df$char_max, "ora.maxlength") <- 3000
attr(test.df$raw_max.typ, "ora.maxlength") <- 1000
attr(test.df$nvchar, "ora.encoding") <- "UTF-8"
attr(test.df$nvchar_max, "ora.encoding") <- "UTF-8"
attr(test.df$nvchar_max, "ora.maxlength") <- 1500
attr(test.df$char, "ora.type") <- "CHAR"
attr(test.df$date_tz, "ora.type") <- "timestamp with time zone"
attr(test.df$date_ltz, "ora.type") <- "timestamp with local time zone"
attr(test.df$nchar, "ora.type") <- "CHAR"
attr(test.df$nchar, "ora.encoding") <- "UTF-8"
attr(test.df$date_tz, "ora.fractional_seconds_precision") <- 9
R> # displaying the data frame
R> test.df
char nchar
1 abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz
varchar
1 abcdefghijklmnopqrstuvwxyz
clob
1 abcdefghijklmnopqrstuvwxyz...
nclob
1 abcdefghijklmnopqrstuvwxyz...
raw.typ
1 61, 62, 63, 64, 65, 66, 67, 68, 69, 6a, 6b, 6c, 6d, 6e, 6f, 70, 71, 72, 73,
74, 75, 76, 77, 78, 79, 7a
blob
1 61, 62, 63, 64, 65, 66, 67, 68, 69, 6a, 6b, 6c, 6d, 6e, 6f, 70, 71, 72, 73,
74, 75, 76, 77, 78, 79, 7a,...
char_max
1 abcdefghijklmnopqrstuvwxyz
raw_max.typ
1 61, 62, 63, 64, 65, 66, 67, 68, 69, 6a, 6b, 6c, 6d, 6e, 6f, 70, 71, 72, 73,
74, 75, 76, 77, 78, 79, 7a
nvchar nvchar_max
1 abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz
date_tz date_ltz
1 2014-01-01 14:12:09.019473 2014-01-01 14:12:09.01946
dbWriteTable(con, name="TEST_TAB", value=test.df)
res <- dbReadTable(con, name="TEST_TAB")
R> res
char
1 abcdefghijklmnopqrstuvwxyz
nchar
1 abcdefghijklmnopqrstuvwxyz
varchar
1 abcdefghijklmnopqrstuvwxyz
clob
1 abcdefghijklmnopqrstuvwxyz...
nclob
1 abcdefghijklmnopqrstuvwxyz...
raw.typ
1 61, 62, 63, 64, 65, 66, 67, 68, 69, 6a, 6b, 6c, 6d, 6e, 6f, 70, 71, 72, 73,
74, 75, 76, 77, 78, 79, 7a
blob
1 61, 62, 63, 64, 65, 66, 67, 68, 69, 6a, 6b, 6c, 6d, 6e, 6f, 70, 71, 72, 73,
74, 75, 76, 77, 78, 79, 7a,...
char_max
1 abcdefghijklmnopqrstuvwxyz
raw_max.typ
1 61, 62, 63, 64, 65, 66, 67, 68, 69, 6a, 6b, 6c, 6d, 6e, 6f, 70, 71, 72, 73,
74, 75, 76, 77, 78, 79, 7a
nvchar nvchar_max
1 abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz
date_tz date_ltz
1 2014-01-01 14:12:09.019473 2014-01-01 14:12:09.01946
## End(Not run)
## Not run:
df <- data.frame(A=c(0,1,NaN,4), B=c(NA, 2,3,NaN))
con <- dbConnect(Oracle(), "scott", "tiger")
dbWriteTable(con,"TEST", df, row.names = FALSE, ora.number=FALSE)
## End(Not run)
Execute a Statement on a Given Database Connection
Description
These methods are straight-forward implementations of the corresponding
generic functions except for the execute
method, which is an
ROracle specific DBI extension.
Usage
## S4 method for signature 'OraConnection,character'
dbSendQuery(conn, statement, data = NULL,
prefetch = FALSE, bulk_read = 1000L, bulk_write = 1000L, ...)
## S4 method for signature 'OraConnection,character'
dbGetQuery(conn, statement, data = NULL,
prefetch = FALSE, bulk_read = 1000L, bulk_write = 1000L, ...)
## S4 method for signature 'OraConnection,character'
oracleProc(conn, statement, data = NULL,
prefetch = FALSE, bulk_read = 1000L, bulk_write = 1000L, ...)
## S4 method for signature 'OraResult'
dbClearResult(res, ...)
## S4 method for signature 'OraConnection'
dbGetException(conn, ...)
execute(res, ...)
## S4 method for signature 'OraResult'
execute(res, data = NULL, ...)
Arguments
conn |
An |
statement |
A character vector of length 1 with the SQL statement. |
res |
An |
data |
A |
prefetch |
A logical value that specifies whether ROracle uses prefetch buffers or an
array fetch to retrieve data from the server. If |
bulk_read |
An integer value indicating the number of rows to fetch at a time. The default value is 1000L. When the prefetch option is selected, memory is allocated for prefetch buffers and OCI fetches the specified number of rows at a time. When prefetch is not used, which is the default, memory is allocated in RODBI/ROOCI define buffers. Setting this to a large value results in more memory being allocated based on the number of columns in the select list and the types of columns. For a column of type character, define buffers are allocated using the maximum width times the NLS maximum width. An application should adjust this value based on the query result. A larger value benefits queries that return a large result. The application can tune this value as needed. |
bulk_write |
An integer value indicating the number of rows to write at a time. The
default value is 1000L. When a bulk_write value is specified, memory is
allocated for buffers and OCI writes that many rows at a time. If the
bulk_write argument is not used, then the default value is used to
allocate memory for the bind buffers. Setting |
... |
Currently unused. |
Details
- dbGetQuery
-
This function executes a query statement and fetches the result data from the database. It should not be used for calling PL/SQL queries.
- dbSendQuery
-
This function executes a query statement and returns a result set to the application. The application can then perform operations on the result set. It should not be used for calling PL/SQL queries.
- oracleProc
-
This function executes a PL/SQL stored procedure or function query statement and returns the result.
- dbClearResult
-
This function frees resources used by result set.
- dbGetException
-
This function retrieves error information.
- execute
-
This function executes the specified query statement.
Value
dbSendQuery |
An |
Side Effects
- dbGetQuery
-
Query statement is executed and data is fetched from database.
- dbSendQuery
-
Query statement is executed, but data needs to be fetched through calls to
fetch
. - oracleProc
-
PL/SQL stored procedure or function query statement is executed and result is returned.
- dbClearResult
-
Resources acquired by the result set are freed.
- dbGetException
-
Error information is retrieved and then cleaned from the driver.
- execute
-
Query statement is executed.
References
For the Oracle Database documentation see http://www.oracle.com/technetwork/indexes/documentation/index.html.
See Also
Oracle
,
dbDriver
,
dbConnect
,
fetch
,
dbCommit
,
dbGetInfo
,
dbReadTable
.
Examples
## Not run:
drv <- dbDriver("Oracle")
con <- dbConnect(drv, "scott", "tiger")
res <- dbSendQuery(con, "select * from emp where deptno = :1",
data = data.frame(deptno = 10))
data <- fetch(res, n = -1)
res2 <- dbSendQuery(con, "select * from emp where deptno = :1",
data1 <- data.frame(deptno = 10), prefetch=TRUE,
bulk_read=2L)
data1 <- fetch(res2, n = -1)
data1
res3 <- dbSendQuery(con, "select * from emp where deptno = :1",
data2 <- data.frame(deptno = 10), bulk_read=10L)
data2 <- fetch(res3, n = -1)
data2
res4 <- dbSendQuery(con, "select * from emp where deptno = :1",
data3 <- data.frame(deptno = 10), bulk_write=10L)
data3 <- fetch(res4, n = -1)
data3
res5 <- dbSendQuery(con, "select * from emp where ename = :1",
data4 <- data.frame(ename = 'SMITH'))
data4 <- fetch(res5, n = -1)
data4
## End(Not run)
Fetch records from a previously executed query
Description
This method is a straight-forward implementation of the corresponding generic function.
Usage
## S4 method for signature 'OraResult'
fetch(res, n = -1, ...)
Arguments
res |
an |
n |
maximum number of records to retrieve per fetch.
Use |
... |
currently unused. |
Details
The ROracle
implementations retrieves only n
records,
and if n
is missing it returns all records.
Value
number of records fetched from database.
References
For the Oracle Database documentaion see http://www.oracle.com/technetwork/indexes/documentation/index.html.
See Also
Oracle
,
dbConnect
,
dbSendQuery
,
dbGetQuery
,
dbClearResult
,
dbCommit
,
dbGetInfo
,
dbReadTable
.
Examples
## Not run:
drv <- dbDriver("Oracle")
con <- dbConnect(drv, "scott", "tiger")
res <- dbSendQuery(con, "select * from emp")
# we now fetch the first 10 records from the resultSet into a data.frame
data1 <- fetch(res, n = 10)
dim(data1)
dbHasCompleted(res)
# let's get all remaining records
data2 <- fetch(res, n = -1)
## End(Not run)
Summarize an Oracle object
Description
These methods are straight-forward implementations of the corresponding generic functions.
Usage
## S4 method for signature 'OraDriver'
summary(object, ...)
## S4 method for signature 'ExtDriver'
summary(object, ...)
## S4 method for signature 'OraConnection'
summary(object, ...)
## S4 method for signature 'OraResult'
summary(object, ...)
Arguments
object |
a driver, connection or result set object. |
... |
currently unused. |
Value
description of object.
References
For the Oracle Database documentaion see http://www.oracle.com/technetwork/indexes/documentation/index.html.
See Also
Oracle
,
dbConnect
,
dbSendQuery
,
dbGetQuery
,
dbClearResult
,
dbCommit
,
dbGetInfo
,
dbGetInfo
.
Examples
## Not run:
drv <- dbDriver("Oracle")
con <- dbConnect(drv, "scott", "tiger")
res <- dbSendQuery(con, "select * from emp")
summary(drv)
summary(con)
summary(res)
show(drv)
show(con)
show(res)
## End(Not run)