Oracle Apex Notebook

APEX web form based on procedure using out parameter

When creating a form region on APEX, there are several options to base the form such as a table, a view, a webservice or... a stored procedure.

Creating a form based on a stored procedure can be very useful to execute database manipulation operations in a simple way. In fact, it is even possible to obtain responses from the database directly to an APEX page using OUT parameters.

To illustrate this, here is a simple example. Let's say you want to build a form where a user is asked to guess a number. The page should also inform the user if the guessing is right or wrong.

First of all let's create the procedure. Note that there are two parameters on this procedure. p_guess (IN parameter) is used to send the value and p_result (OUT parameter) is used to receive the procedure response.

<br /><br />CREATE OR REPLACE PROCEDURE guess_number (<br /> p_guess IN NUMBER,<br /> p_result OUT VARCHAR2<br />)<br />IS<br /> v_number NUMBER (5, 0) := 22;<br />BEGIN<br /> IF v_number > p_guess<br /> THEN<br /> p_result := 'The secret number is higher than ' || p_guess;<br /> ELSIF v_number < p_guess<br /> THEN<br /> p_result := 'The secret number is lower than ' || p_guess;<br /> ELSE<br /> p_result := 'Congratulations. The secret number was ' || v_number;<br /> END IF;<br />END guess_number;<br />

Now to create the form, create a new page or region. Next choose "Form", "Form on a Procedure", identify the database schema and enter the stored procedure name. Follow the form wizard creation till the end entering adequate values.


After you run the page you will be presented with two fields. Enter a value into "P_Guess" and press submit button to get a response into "P_Result".


Now to beautify the form you can hidde the result field and integrate the response message into the notification template. If you go to page edit mode you will see the auto-generated "Run Stored Procedure" after submit process. Edit this process, click "Messages" and put the reference to the item "Process Success Message" like this: &P6_RESULT.


Follow the link to run this example: http://apex.oracle.com/pls/otn/f?p=25110:6

3 comments | Posted in » Forms, Oracle Apex, Stored Procedures