Solved

validate a number entered in oracle forms

Posted on 2011-09-19
24
368 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
ID: 36563038
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
ID: 36563050
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
ID: 36563057
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)
ID: 36563071
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
ID: 36563115
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
ID: 36563134
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)
ID: 36563147
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
ID: 36563153
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)
ID: 36563166
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
ID: 36563381
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
ID: 36563921
>>> 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
ID: 36564196
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
ID: 36564400
>>> 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
ID: 36566292
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
ID: 36566439
>> 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
ID: 36566451
>>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
ID: 36566559
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
ID: 36566583
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
ID: 36567265
thanks
0
 
LVL 6

Author Comment

by:anumoses
ID: 36567503
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)
ID: 36567538
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
ID: 36567723
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
ID: 36567738
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
ID: 36567777
Thanks sdstuber.. that worked.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

930 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

13 Experts available now in Live!

Get 1:1 Help Now