Oracle Apex Validation: PL/SQL Function Returning Error Text

In Oracle Apex, there are several types of validation that you can use to validate an item. But if the validation requires to check multiple conditions and need to display the relevant error messages, then you should choose the PL/SQL Function (returning error text) type of validation. Because using this type, you can show the relevant error messages easily.

PL/SQL Function (returning error text) Example

This validation type aborts the page submit if the PL/SQL function returning text message and shows that text as an error message, and if it is not returning any text, then it means the validation is successful.

The following PL/SQL function example will validate the student's roll number, that it should start with the letter 'S' and should have seven-character length.

If any of the conditions will not match, then it will give the appropriate error message.

Begin
    If substr(:p11_roll_no, 1, 1) != 'S' Then
        return('Roll number must start with S.');
    Elsif length(:p11_roll_no) < 7 Then
        return('Roll number must be 7 character long.');
    End If;
End;

Oracle Apex validation failed output.

Related Tutorials:

Vinish Kapoor
Vinish Kapoor

Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

5 Comments

  1. Good morning,

    I have a trivial question as it's the first time, I'll use validation.
    There are several way to validate user's data but this is the one I like.

    My question is:
    Where do you place that PL/SQL function in the page?
    On P11_roll_no?
    On Create/Page submit?

    I ckecked both and I cannot figure out where to add that validation.

    Thanks a lot.

    Marc

    • Do the right click on your page item then select create validation option. Then add the code here.

  2. Good Afternoon,

    my question: I have one field like phone number, the field should required 9 digits how to create validation for this.

    • you can try this:
      begin
      if (length(regexp_replace('234*5232226', '[^0-9]')) length('234*5232226')) or --There are non numeric values, such as*#
      (length(regexp_replace('234*5232226', '[^0-9]')) 9) or --Non 9 digit numbers
      (length(regexp_replace('234*5232226', '[^0-9]')) is null) then --No numbers entered
      return('The input content is a non 9 digit phone number') ;
      end if;
      end;

Comments are closed.