Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Validation of Item (Oracle 10g,Win 7 32 bit,Apex 4.0)
Validation of Item [message #625961] Fri, 17 October 2014 02:32 Go to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Hello ,

I have two field "Type" (list item varchar2,Number) and "Value" (datatype is varchar2) by default its varchar2 so user insert varchar2 data but if user select number in Type list then user should only insert numeric data.

how can i create validation for restrict the user to enter numeric data.



Thanks,
Xandot
Re: Validation of Item [message #626014 is a reply to message #625961] Sun, 19 October 2014 04:57 Go to previous messageGo to next message
Littlefoot
Messages: 21578
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Regular expressions validation type might help. You'd use REGEXP_LIKE, such as
regexp_like(:PAGE_ITEM, '^\d+$')
will allow only numbers (while everything else is a considered to be a string).
Re: Validation of Item [message #626045 is a reply to message #626014] Mon, 20 October 2014 03:17 Go to previous messageGo to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
I have one condition also like if the "Type=Number" then only check the validation so how can i apply condition in validation ..i have already try this

------Validation Expression 1-------
if Type ='N' then
  regexp_like(:PAGE_ITEM, '^\d+$');
end if;

But Its not working ..Please help me

Thanks
Re: Validation of Item [message #626051 is a reply to message #626045] Mon, 20 October 2014 03:36 Go to previous messageGo to next message
Littlefoot
Messages: 21578
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I prefer functions that return Boolean, so you'd just
return regexp_like(...);
Re: Validation of Item [message #626122 is a reply to message #626051] Tue, 21 October 2014 01:41 Go to previous messageGo to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
When i implement the validation with Boolean return then some error is coming like :--

--Error ERR-1021 Unable to run "function body returning boolean" validation.
if Type='N' then
return regexp_like(:Page_item, '^\d+$');
end if;



Thanks,
Xandot
Re: Validation of Item [message #626128 is a reply to message #626122] Tue, 21 October 2014 02:13 Go to previous messageGo to next message
Littlefoot
Messages: 21578
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is not a function body that returns Boolean; a function body begins with a BEGIN and ends with an END. Besides, you can't reference "type" item that way - it requires a colon sign.

I created a simple page with two items: one is P7_TYPE (select list with two values: "NUMBER" and "VARCHAR2") and another one is P7_VALUE (text item).

Then I created validation on P7_VALUE item. Its type is PL/SQL, a function that returns Boolean:
return case when :P7_TYPE = 'NUMBER' then 
            regexp_like(:P7_VALUE, '^\d+$')
       end;
Error message saying "Wrong value", "Always execute" property set to "Yes".

Sample execution:

/forum/fa/12234/0/

Note that the above simple regular expression catches only integers; for decimal numbers, modify it to
regexp_like(col, '^-?[[:digit:],.]*$')


There are other methods (more accurate, that would catch exponents and stuff) that distinguish numbers from strings - search the Internet, you'll find many examples. However, if you are satisfied with the above suggested regular expression, that should be OK.
Re: Validation of Item [message #626831 is a reply to message #626128] Tue, 04 November 2014 00:54 Go to previous message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Thanks Littlefoot ...Smile
Previous Topic: ORA-06502: PL/SQL: numeric or value error
Next Topic: Highlight validating Item
Goto Forum:
  


Current Time: Wed Aug 05 12:23:39 CDT 2020