Forum Stats

  • 3,875,374 Users
  • 2,266,908 Discussions
  • 7,912,186 Comments

Discussions

Validation in Page Item | Oracle APEX 21.1

Omar Baig
Omar Baig Member Posts: 191 Blue Ribbon

I have 2 page items (a, b) and their "Type" is "Number". I create validation on item "a" and validation "Type" is "Function Body (returning Boolean)" and PL/SQL code is

BEGIN

IF NVL(:a, 0) <= NVL(:b, 0) THEN

return true;

ELSE

return false;

END IF;

END;

furthermore i use "Expression" as well where "SQL Expression" is

NVL(:P8_PORD_VALUE, 0) <= NVL(:P8_UBALANCE, 0)


but these are not working properly.

Best Answer

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,476 Red Diamond
    edited Nov 22, 2022 11:09AM Answer ✓

    Recall that all APEX session state values are stored as character strings using varchar2 or clob data types depending on length.

    Read the nvl documentation, which includes this:

    The arguments expr1 and expr2 can have any data type. If their data types are different, then Oracle Database implicitly converts one to the other. If they cannot be converted implicitly, then the database returns an error. The implicit conversion is implemented as follows:

    If expr1 is character data, then Oracle Database converts expr2 to the data type of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.

    In your expression

    NVL(:P8_PORD_VALUE, 0) <= NVL(:P8_UBALANCE, 0)
    

    the first nvl arguments are varchar2, and so the second arguments are implicitly converted to varchar2. This results in the expression being evaluated using character comparison semantics, where for example '1000' is less than '20'.

    When evaluating number or date expressions in APEX, session state values must always be explicitly converted to the required number or temporal type. Also note that due to short-circuit evaluation, coalesce is more efficient than nvl, and its conversion behaviour is more favourable to numeric data:

    If all occurrences of expr are numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.

    However, it's better not rely on implicit conversions:

    coalesce(nv('P8_PORD_VALUE'), 0) <= coalesce(nv('P8_UBALANCE'), 0)
    


Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,476 Red Diamond
    edited Nov 22, 2022 11:09AM Answer ✓

    Recall that all APEX session state values are stored as character strings using varchar2 or clob data types depending on length.

    Read the nvl documentation, which includes this:

    The arguments expr1 and expr2 can have any data type. If their data types are different, then Oracle Database implicitly converts one to the other. If they cannot be converted implicitly, then the database returns an error. The implicit conversion is implemented as follows:

    If expr1 is character data, then Oracle Database converts expr2 to the data type of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.

    In your expression

    NVL(:P8_PORD_VALUE, 0) <= NVL(:P8_UBALANCE, 0)
    

    the first nvl arguments are varchar2, and so the second arguments are implicitly converted to varchar2. This results in the expression being evaluated using character comparison semantics, where for example '1000' is less than '20'.

    When evaluating number or date expressions in APEX, session state values must always be explicitly converted to the required number or temporal type. Also note that due to short-circuit evaluation, coalesce is more efficient than nvl, and its conversion behaviour is more favourable to numeric data:

    If all occurrences of expr are numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.

    However, it's better not rely on implicit conversions:

    coalesce(nv('P8_PORD_VALUE'), 0) <= coalesce(nv('P8_UBALANCE'), 0)
    


  • Omar Baig
    Omar Baig Member Posts: 191 Blue Ribbon

    Solve while running this Expression

    COALESCE(:a, 0) <= COALESCE(:b, 0)