When you fill in a form, the entered data must be validated, just to make sure that it is correct. Date fields, number fields where the number should not exceed some defined limit, items with a certain format like a telephone number or an e-mail address, all this data has to be validated. In APEX you can use validations to check the user input.
For this recipe we will use a user profiles form where the user can enter some personal information. Make sure you have access to the app_user_profiles table and the app_ups_seq sequence. You can create a user profiles form using these steps:
Go to your application.
Click on Create page.
Select Form.
Select Form on a table or view.
Select the table/view owner and click on Next.
In the table/view name field, enter
app_user_profiles. Click on Next.Click again on Next.
Select do not use tabs and click on Next.
In the Primary Key column 1, select ID. Click on Next.
Select Existing sequence. In the Sequence list box, select app_ups_seq. Click on Next.
Select all columns and click on Next.
Click on Next.
Enter the page numbers APEX should navigate to if the form is submitted or cancelled. You can use the page number of the home page, mostly 1, for both. But you can also use the same page number as this page. Click on Next.
Click on Finish.
Let's put some validation on the items. We are going to put validation on birthday, e-mail, and a Twitter account. For the check of the Twitter account you must first create the following procedure:
create or replace procedure app_search_user (p_search in varchar2
,p_result out varchar2)
is
l_request utl_http.req;
l_response utl_http.resp;
l_tweet_url varchar2(255) := 'http://api.twitter.com/1/users/lookup.xml';
l_content varchar2(255) := 'screen_name='||p_search;
l_line varchar2(1024);
l_result varchar2(100) := 'no user';
l_user varchar2(100) := 'your user name';
l_password varchar2(100) := 'your password';
begin
-- build the request statement
l_request := utl_http.begin_request(url => l_tweet_url
,method => 'POST');
-- set header
utl_http.set_header(r => l_request
,name => 'Content-Length'
,value => length(l_content));
-- authenticate the user
utl_http.set_authentication(r => l_request
,username => l_user
,password => l_password);
-- write the content
utl_http.write_text(r => l_request
,data => l_content);
-- get the response
l_response := utl_http.get_response(r => l_request);
begin
loop
utl_http.read_line(r => l_response
,data => l_line
,remove_crlf => true);
if instr(l_line,'<screen_name>') > 0
then
l_result := 'user found';
end if;
end loop;
exception
when utl_http.end_of_body
then
null;
end;
utl_http.end_response(r => l_response);
p_result := l_result;
exception
when others then
utl_http.end_response(r => l_response);
p_result := 'request failed';
raise;
end app_search_user;
/
[9672_01_16.txt]This procedure makes a call to the Twitter API and searches for the Twitter username which was passed through. The request sent looks like the following URL:
http://api.twitter.com/1/users/lookup.xml?screen_name=<twittername>
Here, <twittername> is the Twitter username you are checking. The result is an XML or JSONresponse. In this case, if the Twitter username exists, the procedure gets an XML response with a tag <screen_name>, which holds the username. If the Twitter username does not exist, the procedure gets an XML response with an error tag. The procedure makes use of the utl_http package so the database user must be granted execute rights to this package. Also, it is important to define the Access Control List (ACL) if your database version is 11g. To grant access, log in as SYS user and execute the following procedure:
begin dbms_network_acl_admin.create_acl ( acl => 'utl_http.xml', description => 'HTTP Access', principal => '<oracle username>', is_grant => TRUE, privilege => 'connect', start_date => null, end_date => null ); dbms_network_acl_admin.add_privilege ( acl => 'utl_http.xml', principal => '<oracle username>', is_grant => TRUE, privilege => 'resolve', start_date => null, end_date => null ); dbms_network_acl_admin.assign_acl ( acl => 'utl_http.xml' host => 'api.twitter.com' lower_port => 80 upper_port => 80 ); commit; end; / [9672_01_17.txt]
In the Page view, go to the Page Processing section and right-click on Validating. Select Create Validation.
Select the Validation Level. In our case, we choose Item Level.
Select the Birthday item.
In the Select a validation method dialog, select the PL/SQL validation method.
In the type of PL/SQL validation dialog, select PL/SQL error.
The sequence number has already been issued but you can change it to your own comfort. You can also enter a name for the validation. These two fields are mandatory. In the third field, the display location, you can select where the error message should appear. Click on Next.
In the Validation Text area, enter the following code:
[9672_01_18.txt]
if :Pxx_BIRTHDAY > (sysdate – numtoyminterval(13,'YEAR')) then raise_application_error (-20001,'You must be at least 13 years old to register.'); end if;
xx is the page number. This code checks if the entered date is greater than the current system date minus 13 years. If so, the person is younger than 13 years and is not allowed to register. In that case an error message should be issued. You can enter the error message in the Error Message textarea. In the next step, optionally you can specify the conditions when the validation should take place.
The first validation is ready now. The next validation is the e-mail.
Right-click on Validating. Select Create Validation.
Select item level validation and click on Next.
Select the e-mail item.
In the next step, select a regular expression.
Check the sequence number and the name of the validation. Click on Next.
In the Regular Expression field, enter the following:
([[:alnum:]]+\.?){2}@([[:alnum:]]+\.?){3,4}/?
With regular expressions you can force a user to conform to a certain format when entering data. You can, for example, check on the format of telephone numbers, URLs, dates and, in this case, correct e-mail addresses. E-mail addresses should at least have the at sign (@) and a dot (.), such as [email protected]. But an e-mail address can have more dots, and numbers are also allowed. [[:alnum:]] indicates that characters and numbers are accepted. The + sign means that it can match 1 or more times. The dot followed by the question mark indicates that a dot can match 0 or more times. The {2} indicates that it must match at least two times. Behind the at sign again, numbers, characters, and dots are allowed.
In the Error Message textarea, enter the error message:
The email address is not valid.Skip the condition and click on the Create button.
The second validation has now been created. Now let's go to the validation of the Twitter account.
Right-click on Validating. Select Create Validation.
Select the item level validation.
Select the Twitter item.
Select the PL/SQL validation method.
Select function returning error text.
Enter the sequence number and a name for the validation and select where the error message should appear. Make sure that the sequence number is higher than the sequence from the previous validations. Validations are processed in the order of these sequence numbers; lowest sequence numbers are processed first.
In the next step, in the validation text area, enter the following code:
declare l_result varchar2(100); begin app_search_user(:P15_TWITTER,l_result); if l_result = 'user found' then return null; else return 'no valid user'; end if; end; [9672_01_19.txt]
This PL/SQL code calls the stored procedure with the Twitter username as a parameter and gets a result back. If the Twitter username exists, "user found" is returned, otherwise "no valid user" is returned. In the latter case, an error message should be issued. You can enter the error message in the Error Message textarea.
In the Conditions dialog, leave the options as they are and click on the Create button.
On submitting the form, APEX validates the items. In the case of the birthday, it executes the PL/SQL code where the entered birthday is checked. In the case of the e-mail address, the item containing the e-mail address is checked against the regular expression.
You can also validate multiple rows of an item in a tabular form. If one or more rows fail validation, APEX indicates this by showing the concerned items in red with an error message in the Notification area. Also, you can validate at page level.
There are different validation methods. See the following table:
|
Validation method |
Meaning |
|---|---|
|
SQL |
Enter a |
|
PL/SQL |
Enter a PL/SQL expression, PL/SQL error ( |
|
Item not null |
Item should not be empty |
|
Item string comparison |
Compare the value of the item with a predefined string |
|
Regular expression |
Item value should meet a certain format, like a date format ( |
For more information on regular expressions, go to http://psoug.org/reference/regexp.html