Putting some validation in a form

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.

Getting ready

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:

1. Go to your application.

2. Click on Create page.

3. Select Form.

4. Select Form on a table or view.

5. Select the table/view owner and click on Next.

6. In the table/view name field, enter app_user_profiles. Click on Next.

7. Click again on Next.

8. Select do not use tabs and click on Next.

9. In the Primary Key column 1, select ID. Click on Next.

10. Select Existing sequence. In the Sequence list box, select app_ups_seq. Click on Next.

11. Select all columns and click on Next.

12. Click on Next.

13. 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.

14. 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]

How to do it...

1. In the Page view, go to the Page Processing section and right-click on Validating. Select Create Validation.

2. Select the Validation Level. In our case, we choose Item Level.

3. Select the Birthday item.

4. In the Select a validation method dialog, select the PL/SQL validation method.

5. In the type of PL/SQL validation dialog, select PL/SQL error.

6. 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.

7. 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.

1. Right-click on Validating. Select Create Validation.

2. Select item level validation and click on Next.

3. Select the e-mail item.

4. In the next step, select a regular expression.

5. Check the sequence number and the name of the validation. Click on Next.

6. 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 [e-mail]. 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.

1. In the Error Message textarea, enter the error message: The email address is not valid.

2. 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.

1. Right-click on Validating. Select Create Validation.

2. Select the item level validation.

3. Select the Twitter item.

4. Select the PL/SQL validation method.

5. Select function returning error text.

6. 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.

7. 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.

How it works...

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.

There's more...

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 where exists SQL query, a not exists SQL query or a SQL expression SQL query
PL/SQL	Enter a PL/SQL expression, PL/SQL error (raise application_error) a function returning Boolean or a function returning error text
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 (dd/mm/yyyy) or an IP address (xxx.xxx.xxx.xxx)

See also

• For more information on regular expressions, go to http://psoug.org/reference/regexp.html