APEX web form based on procedure using out parameter

Written by Paulo Vale on Friday, June 20, 2008

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.



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

Related Posts by Categories



Widget by Hoctro | Jack Book
  1. 3 comments: Responses to “ APEX web form based on procedure using out parameter ”

  2. By User101 on October 22, 2009 at 8:57 PM

    I would think the HTML tags would be read appropriately, but they are not. How can you get around that?

  3. By Anonymous on July 26, 2010 at 10:46 AM

    Hi Paulo,

    Liked this post. Quite impressive. Any other example on forms on stored procedure. If you can share will be a huge favor

    Regards

  4. By Unknown on July 5, 2013 at 9:31 AM

    Hi Paulo ,

    I have created a procedure where the output is a reference cursor . I want the o/p of the cursor to be displayed on the front end .