R/get_oracle_tables.R

In andybeet/dbutils: Utility package for db connectivity and functionality

#' Extract ALL oracle tables in database
#'
#' Pulls a list of all of the tables and owners found within a database
#'
#'
#' @param channel DBI Object. Inherited from \link[DBI]{DBIConnection-class}. This object is used to connect
#' to communicate with the database engine. (see \code{\link[dbutils]{connect_to_database}})
#' @param owner character string. The name of the tables owner. Used to filter tables by a specific owner 
#'
#' @return Data frame (n x 2)
#'
#'   \item{column 1}{Nam of the Database}
#'
#'   \item{column 2}{Name of the table owner}
#'
#'The default sql statement "\code{"SELECT table_name,owner FROM all_tables ORDER by owner"}" is used
#'
#'@section Reference:
#'Use the data dictionary for an html list
#'
#'
#' @seealso \code{\link[dbutils]{connect_to_database}}
#'
#' @examples
#' \dontrun{
#' # extracts ALL table names by owner from the database
#' channel <- connect_to_database(server="name_of_server",uid="individuals_username")
#' get_oracle_tables(channel)
#'
#'
#'#' # extracts ALL table names by owner = VTR from the database
#' channel <- connect_to_database(server="name_of_server",uid="individuals_username")
#' get_oracle_tables(channel,"VTR")
#'}
#'
#' @export
#'
#

get_oracle_tables <- function(channel,owner=NULL){
  
  # creates the sql based on user input
  if(is.null(owner)) {
    sqlStatement <- "SELECT table_name,owner FROM all_tables ORDER by owner"
  } else {
    sqlStatement <- paste0("SELECT table_name,owner FROM all_tables WHERE owner = '",owner,"'")
  }
  
  query <- DBI::dbGetQuery(channel,sqlStatement)
  
  return (list(data=dplyr::as_tibble(query),sql=sqlStatement))
  
}