Connecting R Software With Oracle DB

Introduction

R is an open source programming language-cum-software environment where the user can work for statistical computing, data manipulation, and graphical display.This software helps for data manipulation especially on matrices and is widely used by statisticians, data miners, and data scientists who work on bulk data for effective data handling. Nowadays, it is also frequently used in the academic field by research scholars for data analytics to generate a graphical report.

That was a small and known introduction of R. Here I will share how to connect the Oracle DB with R so that the data could be migrated from one environment to analysis environment and could be worked as user needs.

Procedure

Let's follow the below-given steps to get the application working.

Step 1

First, let us discuss the ingredients that would be required to complete our procedure. We need three softwares on the line. They are

• R version: R-3.3.2-Win
• RStudio - 1.0.136
• Oracle 11g

Step 2

The above requirements should have been installed on your computer by the guidelines provided by each software during the installation procedure. After installation, all the three would be listed in Programs of Start Menu in your computer. 

Step 3

Make sure the above requirements are available for two sets of operating systems (either 32-bit OS or 64-bit OS). My computer runs on 64-bit so my software downloads are based on 64-bit and run on the same.

Step 4

To start the connection, first, we have to manually connect the string and to do that, go to Start menu and select Control Panel and get inside the selection window.

 

Step 5

After getting inside the Control Panel area select Administrative Tools and click on it once. 



Step 6

Inside Administrative Tools, you will be displayed a list of optional services available where you have to select the ODBC data sources.

 
Step 7

Upon clicking the datasource, a new window opens where you are supposed to add the datasource in User DSN.

 

Step 8

After clicking the Add button, it takes you into another window where you have to select the Oracle in XE service. The below picture will help you to understand. After selecting the desired name, click on Finish.

 

Step 9

Once the Finish button is clicked, ODBC driver configuration window opens up where you have to fill certain credentials for creating the connectivity. 

 

Step 10

The fields to be filled are displayed in the below-given picture. Here, User ID is specifically given as HR and this is only for sample purpose since the HR user is already available in Oracle and I am going to use it along with its data.

 

Step 11

After filling the fields in the above pic manner, click on Test Connection to check whether the database is connected to its service or not. 

 

Step 12

The connection window pops up and asks the user for the password field. Here I have given the password as HR same as username so that I could easily remember. After giving the password click OK. 

 

Step 13

If all went well, the below picture is the result you will get prompting you with successful connection. 

 

Step 14

The above procedure was configuring the connection with ODBC and now we have to do certain commands in SQL Command Line. This is completely different from our usual cmd prompt. SQL command line will appear automatically on start menu as it comes with ORACLE installation. Go to Start Menu and select Run SQL Command Line.

 

Step 15

SQL command line looks something like the below picture. There you can type the commands for execution. For starting purpose, I have typed the command show user which will list me the common user. Here it shows the user with double quotes. 

 

Step 16

Now it's time to connect the user. By default, it is a system and while installation of ORACLE, I have given the password as oracle. The below command shows how to connect with default user with password.

 

Step 17

Now to list out the number of usernames available by default inside our database we type the following below code in the SQL command line and find the list and its account status. 

 

Step 18

As we have already discussed in the above steps we would be using the user HR as sample purpose and you can see its status as OPEN. 

 

Step 19

Suppose an unexpected scenario, like if the HR username has the status EXPIRED & LOCKED, then I would type the below-given command (alter user HR identified by HR account unlock;) and press ENTER. The command will change the status of the username. I have typed the command and displayed the result in the below picture.

 

After altering the username, now we have to connect using the command conn HR/HR. 

Step 20

Since the username is connected, now we will see the default available tables inside the database which is supposedly filled with data where the user can work on. To do that, type the command displayed in the picture given below. Here, we are going to select the table EMPLOYEES which has some data. 

 

Step 21

The above steps will give you the proof that there are inbuilt tables with some names and lots of data to be analyzed. Now we again get out of command typing mode and enter into GUI selection mode. It's time to get inside the real software that is R. This is available from Start Menu select RStudio. 

 

Step 22

Inside RStudio, you will get workspace called the Console where you have to again type the commands of R to execute your logic. The below picture shows the R environment. The greater than (>) symbol shows the starting place of typing the command. 

 
Step 23

The first command that you have to type is initial package installation inside the R environment and to do this type the below command. Once the package is installed you will be prompted with the message of successful unpacking of package.

 
Step 24

To list out the data inside the EMPLOYEES table type the below-given command. 

 

Before typing the above command in the console, first, you have to test the connection string is really working, but this time from R environment. And to do this type the following command given in the picture below.

 

Conclusion

R is a data manipulation environment where you can analyze bulks and bulks of data available in any of the mode. Usually, it is supposingly to be stored in excel file format. To analyze those data R is the suggested environment.