Introduction
There’s no doubt the use of data nowadays is much bigger than just a little while ago. The trend is that this growth will increase more and more. And in any area where there’s this need for data usage, there will also be a form of storage and control thereof, such as large databases.
In the Telecom and IT area for example, numerous elements, alarms, meters and gauges, complaints and service calls, customer data and various other variables need to be stored properly so they can later be processed to extract results as analyses and reports.
It is natural that in a niche so defined and with so much expectation of growth there’s a lot of competition, and consequently several (good) options. As an example of good databases we can quote ‘Oracle’, ‘SQL Server’, ‘IBM DB2’, ‘MySQL’, ‘SYBASE’, among others. The databases, among other features, can be classified as relational and Non-relational …
Well, let’s keep it simple: this is much more complex, and is handled by specialized professionals. And this kind of professional, known as DBA or ‘DataBase Administrator’ is responsible, as its name suggests, for the entire administration of the database.
Among other things, the DBA can provide access (connection data, user, and password) to the database under its administration. And it is from there that enters our tutorial today.
Goal
Learn about a free tool (Toad Freeware) that allows us to work with large databases. As an example, we’ll show you how to access data from an ‘Oracle’ database. In addition, we will see how to configure the Oracle Instant Client, avoiding the local ‘installation’ of an Oracle database.
Note: This is not a tutorial geared towards DBA’s, although if you are one, feel free to join us.
All databases can export their data and reports through specific applications - usually a Web interface or a specific/proprietary application. But there are cases in which ‘direct’ access to the original data stored are much more desirable or even necessary.
This can occur for example when the ‘Client’ applications does not have all the necessary data for the analysis (e.g. for a deeper analysis where we need more fields then the fields shown in the standard tables/reports).
The access to these raw data is also useful in cases where the information serves as input for other analyses and reports, and getting the same manually would be an unnecessary limit.
Note: it is common for the DBA’s to offer a certain ‘resistance’ to allow access to these stored data - which is understandable - they want you to access only through the interfaces that they suggest/build. However, exposing the need, you should get at least a ‘read-only’ access to data. So, you don’t run the risk of making any ‘crap’, and can do your job.
In other words we can ‘bring’ the data from large databases for a more ‘friendly’ format, or a more controlled environment for not so advanced users. And then work with it using programs which we’re most familiar with, such as Microsoft Access or Excel. From that point, we can work on the data as we want.
Note: it is not the intention here to discuss the advantages and disadvantages of a most popular database like Access, or Excel spreadsheets (which in a way can also be considered/used as a ‘database’). We’re just focused on bringing the data that we need a database of more ‘complex’ to one that we are more used to work.
At this point, we now have another problem: what is the best tool to accomplish the access to this database?
That is one of our goals today: get the best solution for such access.
And then, what are the best alternatives to database tools?
This really is a controversial question, because we have ‘diehard’ defenders of several good options.
The list of options is pretty big, and we can list some of the key:
- Tora
- DbVisualizer
- SQuirreL SQL
- Oracle SQL Developer
- PL/SQL Developer
- Orbada
- SQLPal
- Toad
- Data Visualization
- SQL Maestro
We could continue with the list, or start to detail the characteristics of each of these existing tools, to which database apply, whether they are free or paid, etc. But this also is not our goal, and if you want more details on these and other tools, we suggest reading the comparison of database on the website of Wikipedia.
Okay, but what database tool should I use?
After knowing a little of the subject, we can return to our main goal: simply to get data stored in large databases (Oracle, SQL Server, etc.) for use in our work with simpler programs like Access or Excel.
By the title of the tutorial, you should already know that our choice is ‘Toad’.
For that choice, and our goal, we take into account some features, and one of them is whether the tool is free or paid. Toad, as most of these tools has paid (complete) version, but also has a free version (‘Freeware’), where you can use the program with some limitations. If you like it, and find that the full version worth it, you can then decide to buy.
Also take into account the amount of users - the community of Toad is pretty big, that is, it is very likely that you get help if you need. And also: the site of Toad have a large amount of educational material available, simply search for what you want and download.
h3 class=“AlignJustify”> The Program’s Website
Now that we’ve established what we’ll do (get data from a database into local files that can be processed by Access or Excel macros) let’s see how to do this using Toad.
But there is another important detail to consider: depending on the type of database (Oracle, SQL Server, etc.) have different types of access to the same tools.
In other words, we need to know which database we’ll get our information. And then download the specific program.
Our choice of example is accessing an Oracle database.
Then visit the site:
https://www.toadworld.com/toad-for-oracle-freeware
And download the version that suits to you: Toad for Oracle Freeware 64 or 32 bits.
To download the ‘Freeware’, it is necessary to create a free ‘login’ (if you don’t have one already).
The registration is quick and simple, and in a few seconds you will be able to download the desired file.
After default installation, we are ready to use Toad to access data from an Oracle database.
Everything was going well. But when you click OK, an error appears stating that did not found any Oracle Client installed.
It is expected: If you are installing the Toad to access an Oracle database, probably you have nothing from Oracle installed/configured on your computer!
Installing (configuring) the Oracle Instant Client - Step by Step
But for you (Toad) to be able to access an Oracle database, it needs to have information such as drivers, Oracle files and settings. To do this, you have two options:
- Install an Oracle Database Client;
- Or ‘install’ an Oracle Instant Client.
This last option (Instant Client) is much recommended in our case, since we can run our applications (in this case Toad) without having to install the standard Oracle Client, which consumes much more space.
See that in the second option - which is what we use - we have put the word ‘install’ in quotation marks. That’s because it’s not really a standard installation, with a file like ‘Setup.exe’.
The Oracle Instant Client ‘installation’ actually consists of copying some files and configure two Windows Environment Variables. May seem complicated, but it’s pretty simple, and we’ll see next.
First, visit the Oracle Web site and do a search for ‘Instant Client’, or go directly to the Downloads area.
Choose the Instant Client suitable for you, download, and proceed with the default installation. (In the same way that Toad, Oracle also requests a brief registration to download the data, but don’t worry, it is also free and fast).
As predicted, you downloaded a ZIP file, with multiple compressed files.
The second action to be taken (after downloading the file) is to create a local directory on your computer, where the Oracle information will be stored. In this example we have created the directory: 'C:\OracleInstantClient'. Then extract the files from the ZIP file that you downloaded to this directory.
Now, let’s go to the third and last action to ‘install’ or actually configure the Oracle Instant Client: Configure environment variables, so that Windows ‘believe’ that has Oracle installed - even if in say, ‘alternative’ way.
So, we need to set two environment variables: PATH and TNS_ADMIN. Note: whereas we are using Windows, because in UNIX the PATH variable is LD_LIBRARY_PATH.
To configure these variables, we need to access Windows environment variables. To do this go to the Control Panel-> System and Security-> System-> Advanced System Configuration.
Click environment variables (1).
Now appears the screen where we configure the environment variables in Windows, IE, where we insert or modify our variables.
PATH variable : this is an operating system variable, and probably already exists in your case. Is used to locate a few executables that Windows needs. If this variable does not exist, simply click the ‘New’ button (1), the Group of environment variables.
In our case this variable already existed with some Windows instructions, so what we’re going to do is edit it by inserting a ‘semicolon’ and our new instruction at the end. In this case, let’s enter in the PATH variable the path or location of our Instant Client: 'C:\OracleInstantClient'. Then, select the PATH variable (2) and click on the ‘Edit’ button (3).
Next, we have the initial content (value) of this variable, and how it’s going to be (remember to include the '; ’ separator).
Just with this variable set, the Windows already ‘understand’ that we have an Oracle ‘installed’.
So much that if you reopen Toad, we won’t have the error: ‘No valid Oracle clients found. You need at least one 64-bit client properly configured.’. Note: this error is specific to our version (Windows 64-bit 8), in your case may be different.
The client is already ‘installed’ (1)- it appears in the list. But we still don’t have TNSNames_Editor enabled (2).
This means that, although it is possible for the Toad to find Oracle Client, it still is not completely configured. That’s because the Oracle works reading a file of type ‘tnsnames.ora’, a simple text file containing the Oracle’s instructions .
To resolve this problem, let’s insert another environment variable: TNS_ADMIN. Following the same procedure that you did to add (or edit) the PATH variable, assign the following values to the variable TNS_ADMIN.
TNS_ADMIN Variable : this variable is responsible for informing where the ‘tnsnames.ora’ file is. As we have seen, this file contains the instructions for accessing the database.
Below, an example of an access to an telecomhall Oracle server.
So, after inserting the TNS_ADMIN variable, and create a ‘tnsnames.ora’ file where this variable indicates, we open Toad again. See it now but note that we have a Client installed (1), Toad identifies the file ‘tnsname.ora’ (2), and already carry your data (3).
By clicking the ‘TNSNames Editor’ (4) in the window above, it opens a ‘tnsnames.ora’ file editor, and we can make edits on it, including adding new Oracle Servers to be accessed (all are available in the database DropDown list).
Done. We just set up, and now just reopen the Toad, and access our Oracle database!
Get a username and password with the administrator (DBA) of Oracle, and fill in the corresponding locations (1). Tip: select the option ‘Save password’ (2) in case only you use this computer. So next time you will not have to enter anything else!
By clicking on the ‘Connect’ button (3) on the previous screen, you have access to the Toad main screen, with access to a large number of database functionality. A really complete graphical interface.
At first you may even find a little confusing, but let’s show the walkthrough of a simple task: fetching the data from one or more tables through an SQL query and export them to a text file on your computer.
If you have SQL concepts, and know about the Oracle database structure that is accessing, you can create your own SQL query. Otherwise, we suggest you ask the DBA to send a file with SQL syntax required to gain access to your data.
He will send the SQL code, you can save a plain text file using Windows Notepad. Then rename this file to ‘.TXT’ to ‘.SQL’.
Ok, on the Toad main interface, click the ‘open file’ icon (1) and locate your file with the SQL code. As we said, if you prefer you can write SQL directly in the editor area (2).
Your query will be opened (1). Locate the ‘run’ button and click it (2).
Done! Your data are now available on the grid at the bottom of the Toad main screen (1).
Click with the right mouse button on this grid (1), and choose ‘Export Data’ (2).
In the export screen, you can make final adjustments to your output file. You can assign a name to it (1), and even choose whether the result should be compressed - ZIP format (2). If you need, you can make data substitution (3), among several other tuning options of the output data.
A point of attention here is for the delimiter (4) - choose what you will use, according to the application where you will import this data.
And don’t worry - the settings made here remain the next export.
So: your data were obtained from the Oracle database, and are now available on your computer, ready to be imported into your usual programs (such as Microsoft Access and Excel) and generate your reports and analyses!
Full Version (Paid)
If you use this type of tool a lot, you should probably buy a more complete version, with more access to all resources.
Note: even though it’s a free version, the interface contains all the commands of the full version, including the advanced, even so you can know that these commands are available in a paid version. Of course, when you access these advanced commands, the tool reports that it’s part of the full version only. Or, they simply appear disabled (in gray).
In this example we show the whole process for using Toad for Oracle, but you can purchase a full version for all major types of existing database.
Conclusion
We saw today a full tutorial on the best tools for direct access to the main existing database. For demonstration of these tools we choose Toad Freeware.
And as database, we choose Oracle, demonstrating also the entire procedure to be able to access an Oracle database on a network, without the need to install a local database or any new services - to do this, simply configure Oracle Instant Client.
Of course it wasn’t our goal cover all aspects related to databases and tools, but at least a good starting point for you that has an interest in this area, but don’t know where to start.
telecomHall Community
Telecom Made Easy! 
(Share to your friends 
)
