Getting to Know the Three Methods of Connecting to the Database Using Toad for Oracle: TNS, Direct and LDAP

In this article we are going to learn the three ways in which we can connect to our Oracle database using Toad for Oracle.

First of all, in order to work with this tool, we need to have Oracle database installed, whether it be locally or on a remote server.

We will briefly learn what the architecture of Oracle Net Services is.

Toad communicates with the database through Oracle Net Services, also known as the SQLNET. This service essentially functions as a client/server architecture.

Toad’s data access component communicates with the Oracle Call Interface (OCI) to link with Oracle Net Services on the client machine, which is also the same way as when the database links with Oracle Net Services on the server machine. Lastly, Oracle Net Services transfers all data back and forth between the client and database server via TCP/IP. Note that the network packets are not, by default, encrypted, so we are going to need a program capable of encrypting network traffic among clients and databases.

In order to open Toad for Oracle, we simply double-click on the icon located in the desktop. If we want to open the tool as an administrator, we simply right click on the icon and then we can choose the option to run the tool as an administrator.

When Toad is launched, it checks the Windows PATH variable to find Oracle Net Services and then checks the Windows registry for an Oracle Home. If Toad finds either of the two, then everything is fine, as in this case, and the tool is opened and can be used as expected. If neither of them is found, an error will be displayed specifying that the client could not be found.

It’s very important to consider that the bit size for Oracle client must match the bit size of the Toad Installer that you downloaded and run. Remember, prior to version 11.6 Toad was 32-bit only. If your Windows is 32-bit, then your only option is the 32-bit Oracle client and 32-bit Toad. However, if your Windows is 64-bit, you have two options: both Oracle client and Toad 32-bit, or both 64-bit.

When we open Toad, the first thing that appears is the connection window.

We also have the option to open this window from Session Menu > New Connection.

In this window we can see two different areas. The area of the left is simply a grid which will be showing the different connections that we have available. We currently do not have any connections. In the area on the right, we can put the connection information In the boxes depending on the type selected.

When we make a successful connection it will be saved in the grid on the left.

In order for us to make a new connection we need to have the user name of the schema to which we want to enter in the database and its respective password.

Toad allows us to make three different types of connections: Direct, TNS and LDAP

Direct Connection

Let's start working with the direct connection string. In the database I have the HR scheme enabled, so we will enter the following information in the area to the right of the connection window:

– User: HR

– Password: hr

– Select the Tab: Direct

– Host: <name of our server>

– Port: 1521

– Service Name: ORCL

– Connect As: Normal (in the case that we use a user of type Database Administrator, we would use the option SYSDBA)

– Color: Red (We have the option to identify our connection with different types of colors to be able to identify this connection when there are several open connections)

– Connect Using: OraDB11g_home1

In the selection list "Connect Using", all the connections to the different databases that we have available are shown. We can click on the ellipsis and the "Oracle Homes" window opens, which shows us what databases we have installed. In my example, I only have one, the Oracle 11gR2 Enterprise Edition database called ORCL.

Now, let's make the connection by clicking the Connect button.

In this way we can visualize that in the bottom part of the windows we have the red color that allows us to easily identify which type of connection we have open and in the upper part of the windows we can see the name of the connection and the editor in red.

To close the connection, click on the Session Menu > End Connection. If we have more than one connection open we can end all connections selected with Session Menu > End All Connection.

Remember, we also have the option to open this window again from Session Menu > New Connection.

Once we open again our connection window, we can see that the last connection that has been successfully completed is displayed on our grid. Each time we make a new connection we will save the information in the database where we have made the connection. Also, we can see the color, the password, and the type of connection that we have made. In this case, it was a direct connection string.

For each connection we have the option of managing through the grid whether or not we want the password to be saved. If we double click on a connection and we do not have the password saved, we have to enter the password in order to enter the connection.

TNS Connection

For the TNS type connection, the tool will read the TNSNAMES.ORA file from our database configuration.

When we install the database we have in the NETWORK > ADMIN folder some configuration files in which the SQLNET.ORA is the one that allows the connection with the Oracle Net Services and then we have the file TNSNAMES.ORA that has the same information that we enter when we make the direct connection string.

The Oracle TNSNAMES.ORA syntax requires four things: the alias name, the network protocol, the network port, and the database server’s services name for the target database.

When the Toad finds the file, a green check will be displayed indicating that the file has been found. In addition, we have the option of being able to edit the file by clicking the TNSNames Editor button.

We can see the different aliases the file has in a tree type format. These aliases are those that will be displayed in the selection list of the TNS tab in the connection window.

Let's connect using the schema SCOTT (user/schema: SCOTT, password: triger). We can see that in the selection list of the TNS tab are shown the aliases: LISTENER_ORCL, ORACLR_CONNECTION_DATA and ORCL. We identify the connection with the green color.

We click the Connect button.

We can see the new connection made and in case we have more than one connection open we can identify them by the color assigned to each one.

LDAP Connection

In this last option where we have connection types, we can see in the Toad connection window that the LDAP file has not been found. That is why the red cross mark is shown next to the LDAP Editor button and consequently the LDAP selection list is not loaded either.

For this example, I have modified the name of the file extension, which is why the Toad does not find the file.

We modified the name of the file to its original name: ldap.ora.

It is important to note that this file is provided by the administrator since it is not a file that is created by default when installing the Oracle database.

We close the connection window and reopen the connection window so that the Toad can read the file.

We can now see that the LDAP selection list is loaded with the connection information (user/schema: SYSTEM, password: Oracle26) from the ldap.ora file and the tool marks with a green check that the file could be found.

We identify this connection with the purple color and click on the Connect button.

Our connection is open and identified with the purple color.

Now we close all the connections and we can observe in the grid the 3 saved connections identified by the type of connection.

In addition, we have the icon bar at the top of the window with the following accesses:

– Add Login Record: It allows us to create a new connection record.

– Edit Login Record: We can edit an existing record.

– Test Connections: We can test a connection by entering the connection password and it shows us if it is connected or if there is a connection failure.

– TNSPing Server: The tool shows us on the selected connection a message if the TNSPing has been done correctly or not.

– Edit Custom Fields: It allows us to edit custom fields for a connection, for example, if we want to add a Notes field and in the grid that field will be shown.

– Connection Bar Button Order: In this window we can sort the connections that we have available.

– Set View Style: Here we can select different ways of visualizing our connections, either in grid, server, user, or by the connection method.

– Import: This option allows us to import connections from a file.

– Export: This option allows us to export the connections to a file.

– Refresh: This option allows us to refresh the connection window.

Finally, if we have a connection error, a window similar to the following one will be presented, showing us the possible errors and some suggestions for solving them. In the event that we can’t solve the problem(s) with that information, we have the link at the bottom of the window that takes us to the Toad knowledge base where we can register for free and find countless solutions to the problems we have.

Conclusion

In this article we have traced the Toad for Oracle connection window and learned how to connect to the Oracle database using the three connection methods: TNS, Direct, and LDAP.

In future articles we will be learning the use of this great tool for administrators and developers in Oracle databases.