APEX: Creating a form based on a procedure

Introduction
Within ApEx for a while it has been possible to create a page (more specifically, a form) based on a procedure. A procedure enables you to build in more logic than a simple DML statement or otherwise. My example is relatively simple in that when I want to book an appointment with somebody, I do not want this appointment to overlap with somebody else’s. Maybe I even don’t want my appointment to be directly after or before another one, and want a 15 minute break.

Scenario

Build the procedure
This kind of scenario is not easy to realise if you don’t have a plan. The plan is therefore to build an INSERT statement (into a procedure, not a function!) that is only actually executed when there is enough room for my new appointment – in terms of time – in my agenda. Building this procedure is done in the Object Browser area of Apex (Home –> SQL Workshop –> Object Browser; press Create).
To check that there are actually no overlaps you need to know when the appointment starts and how long it will take. (Loads more additional features could be added, I am trying to keep the example simple for now). This justifies at least 2 IN-parameters for a procedure to be built, starting moment and duration of the appointment. Another parameter will be used to return the answer. This answer must be of a textual character (!), saying something along the lines of “Your appointment was successfully entered into your agenda”, or “Your planned appointment is overlapping with another”. Allow for at least enough characters to hold this message, as an OUT-parameter in your procedure. The procedure could look something like this…

Note the use of the parameters at the top and what purpose they serve. The OUT parameter is populated with an informative message that will appear in the form that we still have to build.

Build the page
The next step would be to build the page. It is obvious that you need to test in some way that the procedure works. Therefore relevant tables and reference data must be available.

• Move to the relevant application and create your page. Therefore press the yellow Create page button.
• Then select Form and press Next.
• Then select Form on a procedure and press Next.
• You are prompted to select the schema owner of the procedure to be used. Press Next.
• The next screen enables you to select the relevant procedure from a list of values. What is slightly misleading is that the list of values also displays functions, which in this context are of no use, sadly. Select the required procedure and press Next.
• Then a page displays with 7 entries, of which the compulsory entries are depicted with an asterisk:
• A default page number is displayed. You can modify it, if you have thought up your own page numbering system. It is a compulsory entry.
  Define a page name, equally compulsory.
• Define a region template. You can leave this as is for now.
• The next entry, region name, is also compulsory. Make this an explanatory piece of text, as it will appear on top of the region.
• The next two entries enable you to enter Submit and Cancel button labels. You may prefer to display labels in another language.
• The final entry enables you to choose to have a breadcrumb displayed at the top of your page. Leave this value as is.
• Press Next.
• You will be presented with a screen that enables you to select a Tab (existent or now) or no tab at all. For now leave this to the default setting, and press Next.
• Next you will see a screen quoting an optional invoking button. This is an extra button that will be created somewhere else in your collection of pages within the application, that enables you to move to the page that we are currently making. The page where you want this button to be created can be defined in the first entry, for which a list of valid values is available.
  In the second entry you can simply type the label for this button. Should you leave these 2 entries empty then no such button will be created.
  For now we will not do any such thing yet.
• Press Next.
• The next screen enables you to define which pages to move to when you either press the Submit or Cancel buttons. Please define two pages, making use of the offered list of values. You may even decide to use the same page for the two different buttons. Press Next.
• Now you are presented with a screen that makes use of the parameters that were defined for the procedure involved.

• Here you can see quoted the 3 names of the parameters, for which you can modify the labels (the names that appear in the form yet to be generated). Names in the example start with ‘D’, like the names of the parameters themselves. The 3 settings Include should be left set to Yes. In the Default entry you can set default values. The values to be entered depend on the technical functionality of the procedure. If the procedure takes care of default values then the application does not need to do so, and vice versa. Note that in this scenario dates of data type Date are involved, being D_START_DATUM. For all 3 items the Display Type may be kept to Text.
• Please note that the D_BERICHT parameter according to the procedure will contain a message that can be used in the application.
  Press Next.
• You will be presented with a Form on Procedure confirmation. Press Finish.
• You will be given the options of Running or Editing the page. We are so anxious now, so press Run Page.

Something like this will be presented to you:

A few more points need to be addressed before the form will actually work properly:

• This is hardly a form that you can present to a user. To at least make it a bit more presentable give the user a calendar display type (this would have to include the time, not just the date) when dates need to be entered.
• A default value for the minutes (this is the number of minutes that an appointment would take) could be set at 60 or 90.
• The D_MESSAGE can be left empty. The procedure should use this to fill in an appropriate message.
• The previous warning in this text saying that we are dealing with data type DATE (namely D_START_DATUM) we will hit upon now. A change in one of the processes is mandatory for this form to work.
• Finally the names of the fields and the forms need to be modified.

To address these issues we need to do the following:

Press

Edit Page xx

, in my particular case it was page 13. A screen very similar to this will appear:

• In the Page Rendering box: Right click on P13_START_DATUM.
• Press Edit.
• In the Name box, set Display as to Date Picker.
• In the Label box, adjust the Label, by removing the trailing D.
• In the Settings box, set Format Mask to DD-MON-YYYY HH24:MI (use the list of values).
• Press Apply changes in the top right of the screen.
• Similar steps for the Minutes entry can be executed to check that there is a default value defined.
• When you are back in the screen where Page Rendering is at the top, right click on Run Stored Procedure underneath the Page Processing header and select Edit.
• Modify the code as follows, noting that it is a call to the previously defined procedure, which needs to be adjusted so that the data (namely data of type DATE)is dealt with properly:

#OWNER#.P_ENTER_PLANDATE(
D_START_DATUM => TO_DATE(:P13_START_DATUM, 'DD-MON-YYYY HH24:MI'),
D_MINUTEN => :P13_MINUTEN,
D_BERICHT => :P13_BERICHT);

• Press Apply Changes.
• Press Run Page, and check that the functionality is correct.

Conclusion
We can conclude that there are many things in Apex that can be realised, but sometimes Apex needs some help from us. The modification in the code that Apex made for us was not sufficient for it to work properly. When building code you will find more often that you are reminded of the fact that you are actually trying to build an application for the internet, and that browsers don’t really take data types of type date (and probably others also) into consideration.

The text in this blog is by no means complete. It is merely trying to inspire people to use – in this case – forms for a procedure. Please do not hesitate to leave any comments, feedback or additions to the text offered to you. Thank you for your attention.