We had an issue raised where a user wasn’t happy that the validation was happening on submit, requiring them to spend time filling out several more details before being told they made a mistake. Specifically the fields being submitted were all number fields, and as such we only want valid numbers in there.

After some Googling, I thought I’d combine what I’ve learnt on my blog.

First I created the function that allows me to check whether a string is a number or not.

create or replace function IS_NUMBER_FNC (p_string in varchar2)
return number
is
v_num number;
begin
v_num := to_number(p_string);
return 1;
exception
when VALUE_ERROR then
return 0;
end IS_NUMBER_FNC;

 
This is a fairly simple function. We pass a string through (p_string) into a variable, we try to format that variable into a number and if it succeeds we return a 1, and if it fails we return a 0.

Now we create a process on the page. Let’s say I create an item called “P10000_NUMBER_SEARCH”, we would create a PL/SQL process called “VALIDATE_NUMBER”, set the execution point to “Ajax Callback” and the PL/SQL would look like:

declare
    is_valid    number(1);
begin
    select
        IS_NUMBER_FNC(:P10000_NUMBER_SEARCH)
    into
        is_valid
    from
        dual;
    
    htp.prn(is_valid);
    
end;

In here I'm returning the returned value of IS_NUMBER_FNC into a variable. I am then printing this variable using htp.prn.

The final step is to create a Dynamic Action that fires on Lose Focus (this can change to whenever you need it to fire). I'm also going to be selecting which item that affects, which is also where I want the inline error to appear. So my details look like:

Dynamic Action:
Name: VALIDATE_NUMBER_ERROR
Event: Lose Focus
Selection Type: Item(s)
Item(s): P10000_NUMBER_SEARCH

I created a TRUE action which executes this Javascript code:

apex.message.clearErrors();

apex.server.process('VALIDATE_NUMBER',
    {
        pageItems: '#P10000_NUMBER_SEARCH'
    },
    {
    dataType: 'text', success: function(data)
        {
            if(data != 1)apex.message.showErrors([
                  {
                    type: apex.message.TYPE.ERROR,
                    location: ["inline"],
                    pageItem: "P10000_NUMBER_SEARCH",
                    message: "This is not a valid number.",
                    unsafe: false
                  }
            ]);
        }
    }
);

The first part (other than making sure any previous errors have cleared) is firing the VALIDATE_NUMBER process on P10000_NUMBER_SEARCH. The second part of the JSON string is firing an APEX error message on the same page item. I state what message appears and where, in this case inline.

The result of VALIDATE_NUMBER is what triggers "if(data !=1)". If we have a 1, the error message doesn't fire. If if returns 0 (or heaven forbid "null") then it throws the inline error message.

If you want more information about what else you can do with apex.server.process there's a link - here - .

There's also more information about apex.message click - here - . There's more information about success messages and page level alerts you can fire in a similar method.

Thanks to posts from Scott Wesley (Oracle Forums) and Himanshujaggi (Stack Overflow) for helping me piece this together.