Solved

validate a number entered in oracle forms

Posted on 2011-09-19
24
376 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 77

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 74

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
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 77

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 74

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 77

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 74

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 77

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 74

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
 
LVL 74

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 74

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 74

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 77

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 77

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 74

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle SQL - Query help 7 68
constraint check 2 48
create a nested synonym 4 28
minium over 4 numeric columns for each row in oracle 2 29
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

821 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