Solved

validate a number entered in oracle forms

Posted on 2011-09-19
24
362 Views
Last Modified: 2012-05-12
 select to_char('12.0',99.90) as WBC from dual

WBC
12.0
In the form, the column is char. I do not want to give a format mask and then convet rt it automatically, but when the user enters, 12 then I should give an error message to enter the data in the format 12.0 . How can I write the code in the when validate trigger?
block.field name is :qc_tasks_poch.result_result

0
Comment
Question by:anumoses
  • 8
  • 8
  • 6
  • +1
24 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 167 total points
Comment Utility
From this and all your previous questions:  I'm curious why all of your columns appear to be varchar2.

I assume this is also in the 9.2 database?

you can add an if statement using the number entered and the results of to_char(number,99.90)

something like:

if number_entered != to_char(number_entered,99.90) then
    -- invalid format
end if;

0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
Some of the tasks are entered as char and some are number. So the forms has a non database filed that is defined as char.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
your mask should be a string too

99.90  is just 99.9

it  should be

'99.90'
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
OK, I'm not a forms person but as long as it makes sense to you.

Will the if statement work (even with my typo missing the quotes)?
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
Columns in the database.
 
WB_HIGH        NUMBER,
  WB_LOW         NUMBER,
  RBC_HIGH       NUMBER,
  RBC_LOW        NUMBER,

If I have to save the data entered in the forms as 12.4, or 12.0, it has to be saved as entered. So do I have to alter the table? If so is  this the correct way?
WB_HIGH        NUMBER(2,1),
  WB_LOW         NUMBER(2,1),
  RBC_HIGH       NUMBER(2.2),
  RBC_LOW        NUMBER (2,2),
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
if you need 3 digits total  with one of them to the right of the decimal point...

NUMBER(3,1)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Just like your previous questions, store it one way, force the layout when you select it.

If you have the columns as number in the database it will store 12, 12.1, etc...

then when you go to display it, use to_char.

If you change the column definition, you need to account for entered values that fall outside the layout.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
NUMBER(2,1)  means  you can store values like this   1.2
NUMBER(2.2)  is a syntax error
NUMER(2,2)  means you can store values like this .12  (or 0.12  the leading 0 is optional)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
To put into words what sdstuber is saying:  when you define a number with params syntax is(max length, precision)

So, 2,1 says maximum of two numbers and one of these is for the decimal places.

Therefore inserting 12 will fail.
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
It is not the maximum length that is defined in the precision, it is the number of significant digits.

There is a good example of this in the documentation:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements001.htm#sthref120

If you define your column as number(2,2) putting 0.2345 is not an error.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> If you define your column as number(2,2) putting 0.2345 is not an error.

yes no error,  but the stored value  is only 0.23

0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Correct.  Just wanted to point out that it really isn't max length and precision.  It is significant digits and decimal places.

Theoretically, you can store a number with more than 38 digits, it just drops significant digits and add an exponent.  For that matter you can store a number larger than the field with exponents as well.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> significant digits and decimal places.

actually that's not quite correct either

it's precision (significant digits) and scale (which is basically the negative of the exponent for exponential notation)


NUMBER(2,-2)  is correct syntax , the only legal values are 100, 200, 300,..., 9900
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
So If I define the column in the database as Number(2,1) then storing either
16.0 or 16.2 should be ok right?
In case of the value 16.0, I want that to be saved in the database as 16.0

  WB_HIGH        NUMBER(2,1) value may be 16.0 or 16.2
  WB_LOW         NUMBER(2,1), value may be 10.0 or 11.2
   RBC_HIGH       NUMBER(2,2), value may be 2.34 or 2.30
   RBC_LOW        NUMBER (2,2) valuer may be 1.10 or 1.12

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>> I want that to be saved in the database as 16.0

no

numbers don't have trailing 0's.   as mentioned above,  that's a formatting issue when you display it
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 83 total points
Comment Utility
>>NUMBER(2,2), value may be 2.34 or 2.30
>>> NUMBER (2,2) valuer may be 1.10 or 1.12

no  as already explained above... http:#36563153

2,2 means 2 digits total,  2 to the right of the decimal     0.10 or 0.12



>>> NUMBER(2,1) value may be 16.0 or 16.2
>>> NUMBER(2,1), value may be 10.0 or 11.2

no, as already explained above ....  http:#36563153

2,1 means 2 digits total,  1 to the right of the decimal



as stated above, (http:#36563134 )  if you want 16.2  that's 3 digits total, 1 to the right of the decimal  so NUMBER(3,1)

0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
Number(3,1) - value that can be store is 16.2
Number(4,2) - value that can be stored is 16.45
 Is this correct?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 167 total points
Comment Utility
Correct.

The docs have the detail on numbers and how they are stored.

This is the 11g link but numbers haven't changed since 9i in dealing with scale and precision:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements001.htm#SQLRF00222
0
 
LVL 6

Author Closing Comment

by:anumoses
Comment Utility
thanks
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
if :qc_tasks_poch.task_result != to_char(:qc_tasks_poch.task_result,'99.0') then
msgbox.show('WBC Count', 'Invalid Format'||chr(10)||'Format allowed is 99.0', 'OK');
      go_item('qc_tasks_poch.task_result');
      :qc_tasks_poch.task_result := null;
   raise form_trigger_failure;
end if;  

Failing at all levels
pic-1.JPG
pic-2.JPG
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Would you like this question to be unaccepted?

What data type is task_result?

As a quick test try:
if to_char(:qc_tasks_poch.task_result) != to_char(:qc_tasks_poch.task_result,'99.0') then
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
char
I tried
if to_char(:qc_tasks_poch.task_result) != to_char(:qc_tasks_poch.task_result,'99.0') then


Still get the same error
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
sorry, when I first pointed out the mistake in your format,  I didn't give the complete string


change  '99.0'  to  'fm99.0'  

0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
Thanks sdstuber.. that worked.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now