How to create Oracle stored Functions using TOAD for Oracle

The Oracle PL/SQL language allows you to write functions and procedure to centralize the business logic and store the code in the database. A stored function is similar to a stored procedure with one key difference, a stored function returns a single value. This makes a stored function available in your SQL SELECT statements, unlike stored procedures that you must call within an anonymous PL/SQL block or another stored procedure.

In this post I will show you how to create Oracle stored Functions using TOAD. Connect to Oracle Database using TOAD. Click on Database menu and open Schema Browser.

On the Schema Browser open Function Tab and click on New button to create function

New PL/SQL Object Create Options screen will appear. Select Object Type as Function, enter the New Object Name, leave the other options and click on OK to create the Function.

Basic structure of the Function will create automatically and this will appear in SQL Tab as shown below

In this example I have created a ValidateUser Function which takes two parameters (p_usrname for Username and p_password for Password). These parameters are used in simple SQL statement and returning a String on the bases of SQL result. After completing Function statement, press F5 button to compile the Function.

Now your newly created ValidateUser Function and its structure is showing under the Function Tab in Schema Browser.

Now test out this Function using a SELECT statement, Open SQL Tab, use ValidateUser function in SQL statement, give the required parameters and press Ctrl+Enter button to execute the SQL statement in TOAD. The result will show as per your parameters.