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 email 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 create page.
- Select Form.
- Select Form on a table or view.
- Select the table/view owner and click next.
- In the table/view name field, enter app_user_profiles. Click Next.
- Click Next.
- Select do not use tabs and click Next.
- In the primary key column 1, select ID. Click Next.
- Select Existing sequence. In the sequence list box, select
app_ups_seq. Click Next. - Select all columns and click Next.
- Click 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 Next.
- Click Finish.
Let's put some validation on the items. We are going to put validation on birthday, e-mail, and 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; form validationsstarting withend app_search_user; /
[1346_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 form validationsstarting withdbms_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; /
[1346_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 Next.
- In the Validation Text area, enter the following code:
if :Pxx_BIRTHDAY > (sysdate numtoyminterval(13,'YEAR')) then raise_application_error (-20001,'You must be at least 13 years old to register.'), end if;
[1346_01_18.txt]
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 text area. 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.
([[: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 (.) , like [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 text area, enter the error message: The email address is not valid.
- Skip the condition and click 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;
[1346_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 text area.
In the conditions dialog, leave the options as they are and click 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:
For more information on regular expressions, go to http://psoug.org/reference/regexp.html