How to Create and Call a Procedure in Oracle Forms

In Oracle Forms, procedures play an important role in organizing application logic. A procedure is a named block of PL/SQL code that performs a specific task. Instead of writing the same code repeatedly, you can place it in a procedure and call it whenever needed. This not only makes your code clean and reusable but also improves maintainability. In this tutorial, you will learn how to create a procedure in Oracle Forms and how to call it from triggers, menu items, or other program units.

Understanding Procedures in Oracle Forms

A procedure in Oracle Forms is a subprogram written in PL/SQL. Unlike a function, a procedure does not return a value directly but can perform actions such as inserting data, validating input, or showing messages.

Key Features:

• Written in the Program Units node of Oracle Forms.
• Can accept parameters (IN, OUT, IN OUT).
• Can be called from triggers, menus, or other program units.
• Helps reduce code duplication.

Steps to Create a Procedure

Follow these steps inside Oracle Forms Builder:

1. Open the Form in Oracle Forms Builder.
2. Expand the Program Units node in the Object Navigator.
3. Right-click Program Units → Select Create.
4. Choose Procedure and give it a name.
5. Write your PL/SQL block inside the editor.
6. Save and compile the procedure.

Example 1: Simple Procedure

Let’s create a procedure that displays a message to the user.

PROCEDURE show_welcome IS
BEGIN
   MESSAGE('Welcome to Oracle Forms!');
   MESSAGE(' ');
END;

• This procedure is stored under Program Units.
• It does not take any parameters.
• It can be called from anywhere in the form.

Calling a Procedure

You can call a procedure in Oracle Forms from:

• Triggers (e.g., WHEN-BUTTON-PRESSED, WHEN-NEW-FORM-INSTANCE).
• Other Program Units (calling one procedure from another).
• Menu Items or custom libraries.

Example: Calling from a Button

BEGIN
   show_welcome;
END;

If this code is placed in a WHEN-BUTTON-PRESSED trigger, clicking the button will run the procedure.

Example 2: Procedure with Parameters

Procedures can accept input values to perform dynamic tasks.

PROCEDURE greet_user (p_name VARCHAR2) IS
BEGIN
   MESSAGE('Hello, ' || p_name || '! Welcome.');
   MESSAGE(' ');
END;

Calling the Procedure with a Parameter:

BEGIN
   greet_user('Vinish');
END;

• When executed, it shows: Hello, Vinish! Welcome.
• Parameters make procedures flexible and reusable.

Example 3: Procedure for Database Operations

A procedure can also handle database transactions like insert, update, or delete.

PROCEDURE insert_employee (p_id NUMBER, p_name VARCHAR2, p_salary NUMBER) IS
BEGIN
   INSERT INTO employees (emp_id, emp_name, salary) 
   VALUES (p_id, p_name, p_salary);

   COMMIT;
   MESSAGE('Employee record inserted successfully.');
   MESSAGE(' ');
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      MESSAGE('Error inserting employee record.');
      MESSAGE(' ');
END;

Calling the Procedure:

BEGIN
   insert_employee(101, 'John', 5000);
END;

• This procedure inserts a new employee record into the table.
• It commits the transaction after successful insertion.
• If an error occurs, it rolls back the changes.

Best Practices for Using Procedures in Oracle Forms

1. Use Meaningful Names
   • Choose clear and descriptive names like validate_salary or calculate_discount.
2. Keep Procedures Modular
   • Write small procedures that perform a single task instead of large blocks of mixed logic.
3. Use Parameters Wisely
   • Pass values through parameters rather than hardcoding them.
4. Handle Exceptions Properly
   • Always use EXCEPTION blocks to catch errors and avoid unexpected failures.
5. Reuse Code Across Forms
   • Store frequently used procedures in PL/SQL libraries (PLL) so they can be shared across multiple forms.

Common Mistakes to Avoid

• Forgetting to Compile: Always compile the procedure after writing it.
• Ignoring Parameters: Not using parameters makes procedures less reusable.
• Hardcoding Values: Instead of hardcoding, pass values dynamically.
• Not Handling Errors: Without exception handling, the form may crash on failure.

Benefits of Using Procedures in Oracle Forms

• Reusability: Write once, use many times.
• Maintainability: Easier to update logic in one place.
• Modularity: Organize code into logical blocks.
• Error Handling: Centralize exception management.
• Performance: Reduces duplicate PL/SQL in multiple triggers.

Conclusion

Creating and calling procedures in Oracle Forms is an essential practice for building efficient and professional applications. By defining procedures in the Program Units section, you can centralize business logic, make your forms cleaner, and avoid repetitive coding. Whether it’s a simple message display, passing parameters, or executing database operations, procedures give you the flexibility and control needed to maintain high-quality Oracle Forms applications.