getQuery: executes a query to an oracle database

In cabralhenrique/QPVutils: Set of useful packages

Description

executes a query to an oracle database

Usage

getQuery(query_string, ora_file = NULL, access_file = NULL,
  network_alias = NULL, sample_table = 3, quiet = FALSE, ...)

Arguments

query_string	character. can be either a sql query, the path to an sql file or the name of a table (the latter one detected by the presence of a single word in the string)
ora_file	character. Location to the tnsnames.ora file. If NULL (default), script will look for it in "C:\OracleClient\tnsnames.ora" or in "~/.tnsnames.ora" different environments should be seperated by a blank line. Ex: "ENVIRONMENT = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = XXX)(PORT = XXX))(CONNECT_DATA =(SERVER = XXX)(SERVICE_NAME = NETWORK_ALIAS))) ENVIRONMENT2 = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = XXX)(PORT = XXX))(CONNECT_DATA =(SERVER = XXX)(SERVICE_NAME = NETWORK_ALIAS2)))"
access_file	list or character. Either a list with fields username and password, under an ENVIRONMENT field (ex: list(ENVIRONMENT = list(username = USERNAME, password = PASSWORD))), either a character string indicating the location of the file. If NULL (default) the script will look for the file .access_db.yaml in the home directory. A minimal yaml file should contain: ENVIRONMENT: username: 'USERNAME' password: 'PASSWORD'
network_alias	character. If tnsnames.ora file has more than one connection, which network_alias should it use? Default is EDWPDEV
sample_table	integer. Whether to query a sample of a table. Default is 3
quiet	logical. Be silent? Defauls is FALSE

Details

You can also insert data into a table by passing an insert query and a data structure to your input files (see example below)

Value

the output of the query as a data.table object

Author(s)

Henrique Cabral

Examples

 getQuery("select * from MYTABLE")
 getQuery("sql/my_query.sql")
 getQuery("insert into TABLE_NAME values(:1, :2)", data = ds)

cabralhenrique/QPVutils documentation built on May 13, 2019, 10:37 a.m.