Solved

validate a number entered in oracle forms

Posted on 2011-09-19
24
379 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 35

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 35

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

738 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