Before learning how to find a database alias using Toad for Oracle, it is essential to understand what an alias is in SQL.

What is a SQL alias?

SQL database aliases are used to give a table, or a column of a table, a temporary name. And why do we want to give it a temporary name?

MediaBanner-ToadWorld-600x100-IM-JY-63709.psd-1-Jan-18-2022-05-54-52-11-PM

We use aliases in SQL queries to make column names much more readable. Often, the original names of the columns can be unreadable, which is why SQL aliases are used to simplify and better understand the reading of the SQL query.

These database aliases only last as long as the SQL query exists.

We use the AS keyword to create the column aliases. On the other hand, adding the AS keyword for table aliases is unnecessary. Instead, aliases are added after the table’s name inside the FROM clause.

Alias column syntax

SELECT column_name AS alias_name
FROM table_name;

Alias table syntax

SELECT column_name(s)
FROM table_name alias_name;

References:

column_name: The original name of the column.

table_name:The original name of the table.

alias_name: The temporary name to assign.

It is important to note that if the alias_name contains spaces, we must enclose it in double-quotes.

Spaces are accepted when creating an alias for a column name. However, it is generally not good to use white space when creating an alias for a table name.

Column Aliases

We generally use column aliases to easily read the result of the SQL query by clearly identifying the headers of each column.

Example 1:

SELECT employee_id, first_name || ‘ ‘ || last_name AS “NAME”

FROM employees

WHERE last_name = ‘OConnell’;

Figure 1: Example of using the column sql alias without blanks

Figure 1: Example of using the column alias without blanks

In this example, we have assigned the database alias NAME to the second column consisting of the concatenation of the first_name and last_name columns.

This NAME alias will be displayed as the second column header when the result set of the SQL query is returned. Since our alias_name doesn’t include any spaces, we don’t have to enclose the alias_name in double quotes, but if we do, it’s valid, and the SQL query will execute without a problem, returning the same result.

On the other hand, if our SQL alias is made up of more than one word, we would have to enclose the alias in double quotes.

Example 2:

SELECT employee_id, first_name || ‘ ‘ || last_name AS “EMPLOYEE NAME”

FROM employees

WHERE last_name = ‘OConnell’;

 

Figure 2: Example of using column aliases with white space

 

Figure 2: Example of using column aliases with white space

We have aliased the second column concatenated with first_name and last_name, such as “EMPLOYEE NAME”, in this second example. Since there are spaces in this alias_name, “EMPLOYEE NAME” must be enclosed in double quotes.

Table Aliases

When we create an alias in a table, it is generally for two reasons. First, we plan to include the same table within the FROM clause and, therefore, we need to differentiate it, or second, because we want to shorten the name of the table so that the SQL statement is shorter and easier to read.

Example 1:

SELECT e.first_name || ‘ ‘ || e.last_name “Employee Name”,

       m.first_name || ‘ ‘ || m.last_name “Manager Name”

FROM Employees e, Employees m

WHERE e.employee_id = m.manager_id

 

In this example, we have created an alias for the employee’s table called e to display the employee’s name. We have also created another alias for the same table called m to show the manager’s name. Within this SQL statement, we can refer to the employee table as e and as m.

Figure 3: Example of using database sql aliases for tables

Figure 3: Example of using database aliases for tables

Example 2:

SELECT e.first_name || ‘ ‘ || e.last_name “Employee Name”,

       m.first_name || ‘ ‘ || m.last_name “Manager Name”,

      d.department_name  

FROM Employees e, Employees m, Departments d

WHERE e.employee_id = m.manager_id

AND d.department_id = e.department_id

 

In this example, we have added the department’s table with the alias d to the previous query to display the department’s name. Therefore, we can refer to the department’s table within this SQL statement as d.

Database Connections

Now that we understand how and when to use SQL aliases on database columns and tables, we can move on further.

Oracle provides two ways to identify the database we want to connect via aliases or directly using the connection string.

When we start Toad for Oracle, we have three ways to connect to the Oracle database: TNS, Direct, or LDAP.

When we select the connection option through TNSnames, a list type field is displayed in the right sector of the connection window, loaded with all the aliases defined in the TNSNames.ora file.

The TNSNames.ora file is simply a text file that can be modified with any text editor, such as Notepad. The only thing to consider is that Oracle requires that a specific syntax be met, and if it is not met, the aliases may not be displayed in the list type field.

Figure 4: SQL Alias - Select List

Figure 4: SQL Database Aliases – Select List

As many users are not experts in this type of syntax, Toad for Oracle provides access to the file through a modal window accessed through the TNSNames Editor button. As we can see next to the button, a green check is shown, which means that the file exists. Otherwise, if a red cross is shown, it does not exist.

Figure 5: TNSNames Editor Button

Figure 5: TNSNames Editor Button

This window has three views: the left pane shows a tree view with all available aliases, the right pane shows the syntax of the TNSNames.ora file, and the bottom pane shows the messages.

Figure 6: TNSNames Editor window

Figure 6: TNSNames Editor window

As we can see in figure 6, the TNSNames.ora file shows the six aliases that are loaded in the selection list field in the Toad for Oracle connection window.

Finally, Toad for Oracle also gives us the ability to assign an alias to our connections.

For example, in the Edit Login Record window, which is to edit the connection’s properties, we can enter in the Alias field a name that is easy to read. This option is beneficial when we have many connections to different databases.

Figure 7: Edit Login Record Window

Figure 7: Edit Login Record Window

Try Toad free for 30 days. 

Free 30-day Trial

Already in a trial? Talk to sales or buy now online.

Already a loyal fan of Toad for Oracle? Renew now.

Conclusion

In this article, we have learned what SQL Aliases are and how and when to use them. In addition, we learned the different types of aliases that we have and how to find the alias of an Oracle database using the Toad for Oracle TNSNames Editor.

Blog: Tips for Using Toad for SQL Query Generation 

Blog: How to Write a Cross-Connection Query Correctly 

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

Have questions, comments? 

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post.

About the Author

Clarisa Maman Orfali

Clarisa is a System Engineer with more than 24 years of experience as a developer, teacher, and consultant in Information Technology. She was a Founder and CEO between 2013 to 2020 at ClarTech Solutions, Inc., a consulting firm specializing in Oracle APEX and Open Source technologies. Clarisa entered the world of Oracle technologies in 2009. After a few years, she discovered the power of application development with Oracle Application Express (APEX) and specialized in that area. Clarisa is from Argentina, and she is living and working in Irvine, California, in the United States. She's also a Co-Founder of the Argentina Oracle User Group (AROUG). In addition, she had actively participated in large and popular events such as the OTN Tour Argentina and Oracle APEX Tour Latin America after called Oracle Developer Tour. She's also authored the first three Spanish books about Oracle Application Express and recorded and published several Online Video Courses. She has a great passion for Oracle technologies and wants to expand that passion and influence, transferring her knowledge and experience to the entire Latin American and worldwide Oracle communities. You can always contact her through her Twitter account (@Clari707). Clarisa loves spending time with her family by enjoying outdoor activities with her two adored children, Melanie and Nicolas, as well as hanging out with her friends. Also, she loves traveling with her husband Julio worldwide and to takes new challenges at every moment of her life.

Start the discussion at forums.toadworld.com