Press "Enter" to skip to content

How to Create a PL/SQL Code Process in Oracle Apex?

This tutorial shows you how to create a PL/SQL code Process in Oracle Apex.

I have a form for the customer in Oracle Apex, which I created using the wizard and there are two processes already created by the Apex. Which are as follows:

  1. Process form Customer – Type: Automatic Row Processing DML
  2. Close Dialog – Type: Close Dialog

I have a database procedure named “Prc_Customer_Credit“, which I want to execute before the process “Process form Customer” and if it executes successfully then it should allow the other processes to execute else it if it fails (gives an error), then it should stop right there. The following are the steps to perform this task:

Create a PL/SQL Code Process in Oracle Apex

1. In Oracle Apex page designer, click on the Processing tab and do the right-click on the Processes node and select Create Process.

2. When you will create a new process in Oracle Apex, it will place at the bottom of all processes that already exist, so drag it to the top because we want to execute it before every other process. Then provide a name to the process, select the type as PL/SQL Code and add your PL/SQL code in it as shown below:

Begin
   Prc_Customer_Credit(:P20_CUSTOMER_ID);
   
   Exception
     When Others Then
       Raise;
End;

Below is the screenshot for your reference:

Oracle Apex process settings.

Note: You can place your new process after any process if you want to execute it after that. But do not place after Close Dialog process, because it will not execute.

If you want to stop the execution of processes on error, then you should add the EXCEPTION section in your PL/SQL code as shown above. So that Oracle Apex can stop the execution at this point. You can also add a custom error message for the process and can define that this process should execute when a particular button is pressed. Below is the screenshot:

Oracle Apex error message and server side condition settings.

Now save the changes and run the page to test.

Related tutorials: