# Execute a Statement on a Given Database Connection These methods are straight-forward implementations of the corresponding generic functions except for the `execute` method, which is an ROracle specific DBI extension. methods ```r ## 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 `OraConnection` object. - `statement`: A character vector of length 1 with the SQL statement. - `res`: An `OraResult` object. - `data`: A `data.frame` specifying bind data - `prefetch`: A logical value that specifies whether ROracle uses prefetch buffers or an array fetch to retrieve data from the server. If `TRUE`, then ROracle uses OCI prefetch buffers to retrieve additional data from the server thus saving the 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. If `FALSE` (the default), then ROracle uses an array fetch to retrieve the data. - `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 `bulk_write` to a large value results in more memory being allocated based on the number of columns in the insert list and the types of columns. - ``...``: Currently unused. ## Returns - **dbSendQuery**: An `OraResult` object whose class extends `DBIResult`. This object is used to fetch data from a database, using the function `fetch`. ## 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. ## 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. ## References For the Oracle Database documentation see [http://www.oracle.com/technetwork/indexes/documentation/index.html](http://www.oracle.com/technetwork/indexes/documentation/index.html). ## See Also `Oracle`, `dbDriver`, `dbConnect`, `fetch`, `dbCommit`, `dbGetInfo`, `dbReadTable`. ## Examples ```r ## 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) ```

dbSendQuery-methods function