How to Use Functions in Oracle

The Oracle database supports dozens of functions. The majority of the functions are "scalar", or column, functions as opposed to "aggregate", or group functions such as count. In this topic I'll introduce you to a couple of useful scalar functions.

To learn how to use the functions in Oracle, follow these steps:

  1. You'll need to download and install Oracle 12c. The instructions for the setup can be found in How to use sample schemas. Follow steps 1 through 4 in this topic before proceeding to the next step.
  2. Let's presuppose we are required to combine the first name and last name of employees stored in the Employees table. First, we will issue an SQL*Plus statement to limit the column size on output for readability:
    Format out with col statement
  3. We will combine first name and last name (with a white space separator) using the concatenation operator (||) for department ID = 60:
    Display first name concatenated with last name
  4. Imagine we are now directed to display the first character only of the first name:
    Display first character of first name concatenated with last name
    Notice we have used the substr (substring) function to display the substring beginning in position 1 of the first name for a length of 1.
Author: Stephen Withrow

Stephen has over 30 years of experience in training, development, and consulting in a variety of technology areas including Python, Java, C, C++, XML, JavaScript, Tomcat, JBoss, Oracle, and DB2. His background includes design and implementation of business solutions on client/server, Web, and enterprise platforms. Stephen has a degree in Computer Science and Physics from Florida State University.

About Webucator

Webucator provides instructor-led training to students throughout the US and Canada. We have trained over 90,000 students from over 16,000 organizations on technologies such as Microsoft ASP.NET, Microsoft Office, Azure, Windows, Java, Adobe, Python, SQL, JavaScript, Angular and much more. Check out our complete course catalog.