Home » Developer & Programmer » Forms » validation (Forms6i)
validation [message #599016] Mon, 21 October 2013 07:42 Go to next message
m.abdulhaq
Messages: 106
Registered: April 2013
Location: Ajman
Senior Member
I have a character field in forms, where user will enter data in time form 24HH:MI format , but sometimes he is making mistakes instead of typing in 09:30 he will enter 09::30 or he may enter it 02:0 , system should not allow this, how can i set up this.
Re: validation [message #599020 is a reply to message #599016] Mon, 21 October 2013 07:44 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use DATETIME item type with appropriate format mask and store values into a DATE datatype column.

One of the most frequent mistakes is storing date/time values into VARCHAR2 columns. WHY would anyone want to do that?
Re: validation [message #599060 is a reply to message #599020] Tue, 22 October 2013 00:33 Go to previous messageGo to next message
m.abdulhaq
Messages: 106
Registered: April 2013
Location: Ajman
Senior Member
The problem is i have only one field of type char to store this information and i cannot add a new field in the erp according to vendor.
Re: validation [message #599063 is a reply to message #599060] Tue, 22 October 2013 00:59 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One option is to create a function which will check whether a value is valid or not. Here's an example that uses regular expressions (I thought that this is rather simple; you may rewrite it using INSTR and SUBSTR, if you want). I don't have Forms 6i, but I suppose that it doesn't support regular expressions so you'd have to put that code into a database function and call it from the WHEN-VALIDATE-ITEM trigger.

SQL> with
  2  test as
  3    -- sample data
  4    (select '09:30' col from dual union
  5     select '09::30'    from dual union
  6     select 'ab:25'     from dual union
  7     select 'xx/yy'     from dual union
  8     select '55:88'     from dual union
  9     select '22:15'     from dual
 10    ),
 11  time_alike as
 12    -- values that have 2 digits followed by colon (:) followed by 2 digits
 13    (select col
 14     from test
 15     where regexp_like(col, '\d+{2}:\d+{2}')
 16    )
 17  select
 18    -- check whether hours and minutes have sense
 19    col
 20  from time_alike
 21  where to_number(substr(col, 1, 2)) between 0 and 23   -- hours
 22    and to_number(substr(col, -2))   between 0 and 59   -- minutes
 23  /

COL
------
09:30
22:15

SQL>
Re: validation [message #599196 is a reply to message #599063] Tue, 22 October 2013 22:58 Go to previous messageGo to next message
m.abdulhaq
Messages: 106
Registered: April 2013
Location: Ajman
Senior Member
thank you so much littlefoot,i will write a function by passing the value in when-validate-item and throw error message if the time is not sensible.can you refer me to some good articles or links related to regular expressions.
Re: validation [message #599203 is a reply to message #599196] Wed, 23 October 2013 00:08 Go to previous message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I found Regular-expressions.info very useful. That site explains them well. There are certain differences between Oracle and "other" regular expressions; I usually consult Oracle documentation, Using Regular Expressions in Oracle Database.
Previous Topic: enter arabic in english text field
Next Topic: form trigger regarding mouse click.
Goto Forum:
  


Current Time: Thu Aug 21 13:09:20 CDT 2014

Total time taken to generate the page: 0.20052 seconds