Oracle Tips by Burleson

Creating Processes

Often when creating application pages, the wizard will be used to quickly create a form region on a page. However, most pages have more than one region on a page, and therefore creating other processes manually to deal with the other regions is needed.  It may be a process that executes in the before region processing point for populating the page, or in the after submit processing point to update the database.

The exercise shown below follows the idea that an additional region on an application page has been created and some page items, to display a persons name and address, were added.  The process to create will be a Before Regions process used to populate the page items.  The same process would be followed for creating a process that would execute in the After Submit process point.

Creating processes is done from the page definition page.

1.       Click on the  in the Processes area under Page Rendering.

2.       On the Process Type page, select the process type and click Next.  This example chose the PL/SQL option.

3.       On the Process Attributes page:

• Enter a Name for the process: Populate

• Enter the Sequence in which it should execute the process point selected.  For this example, it will be sequence ten (10).

• Select the Process Point: On Load - Before Regions.

• Click Next.

4.       On the Process page, enter the SQL code necessary for the process and click Next.  The code used to populate the example page items is shown here.

select
   first_name, last_name, address_id
into
   :P100_FIRST_NAME, :P100_LAST_NAME, :P100_ADDRESS_ID
from
   employee
where
   employee_id = :P100_EMPLOYEE_ID; 

select
   address_line_1, city,
   state, zip
into
   :P100_ADDRESS_LINE_1, :P100_CITY,
   :P100_STATE, :P100_ZIP
from
   employee_address
where
   address_id = :P100_ADDRESS_ID;

5.       On the Messages page:

• Enter a Success Message if desired:  Success messages are not commonly used in page population processes, but they are available.

• Enter a Failure Message if desired:  In the event there is an error, this message will be displayed to the user.  It will be the same message regardless of where in the process the error occurred.  For advanced programming I have seen the failure message not used, and the programmers often use raise_application_error in-line with the SQL code for the process, especially if there are multiple statements written in the process.

   Click Next.

6.       On the Process Conditions page, the conditions under which this process should execute can be entered.  For example, the developer may only want this process to fire if the value of P100_EMPLOYEE_ID is NOT NULL.

7.       Click the Create Process button to complete the wizard.

Creating the other types of processes is similar.  Use the notes in the rest of the chapter to understand each type of process and the various attributes that need to be set for them.

The above book excerpt is from:

Easy HTML-DB Oracle Application Express

Create Dynamic Web Pages with OAE

ISBN 0-9761573-1-4   

Michael Cunningham & Kent Crotty

http://www.rampant-books.com/book_2005_2_html_db.htm