| how to avoid wild card being entered in a number data type [message #636683] |
Thu, 30 April 2015 04:14  |
|
|
Hi,
I have a field as number datatype to enter the mobile numbers of pensioners. By oversight wildcard like %, $, # are being entered in this field leading to data errors while sending SMS alert to pensioners.
Is there any way to restrict entering these wild card while data entry.
If so any code that should be done in the field may be suggested please.
thanks in advance
|
|
|
|
| Re: how to avoid wild card being entered in a number data type [message #636691 is a reply to message #636683] |
Thu, 30 April 2015 04:45  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
lacchhii wrote on Thu, 30 April 2015 14:44
I have a field as number datatype to enter the mobile numbers of pensioners. By oversight wildcard like %, $, # are being entered in this field
How is the data loaded into the table? Are they plain inserts? Because the number data type won't allow to enter anything other than NUMBER.
SQL> CREATE TABLE t(A NUMBER);
Table created.
SQL>
SQL> INSERT INTO t SELECT '%' FROM dual;
INSERT INTO t SELECT '%' FROM dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> INSERT INTO t SELECT '$' FROM dual;
INSERT INTO t SELECT '$' FROM dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> INSERT INTO t SELECT '#' FROM dual;
INSERT INTO t SELECT '#' FROM dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> |
|
|
|
|
|
| Re: how to avoid wild card being entered in a number data type [message #637109 is a reply to message #636705] |
Sun, 10 May 2015 22:23 |
|
|
sorry everyone for the confusion i created.
the datatype of the mobile field happens to be varchar2 and not number as previously mentioned by me.
I tried to change the datatype to number the software did not open. It seems to have an impact on other fields also. Hence reverted back to the original character datatype itself
I came to know about isnumaric function to solve my problem
I created a function in the form level as mentioned below
FUNCTION ISNUMARIC(P_VAL VARCHAR2) RETURN CHAR IS
VTEST NUMBER;
BEGIN
VTEST:=P_VAL;
RETURN 'NO';
EXCEPTION
WHEN OTHERS THEN
RETURN 'CHAR';
END;
and in the particular field added a when validate item trigger
if isnumaric(:APEN.APEN_BR_MOBILE_NO) = 'char' then
message('please check the Phone no..');
message('please check the Phone no..');
raise form_trigger_failure;
end if;
This is based on some of the suggestion i got while google search. But its not stopping the wild card being entered. Its not giving the message as per the code.
Could some one please correct my code to suit the requirement.
Thanks in advance
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to avoid wild card being entered in a number data type [message #637154 is a reply to message #637139] |
Mon, 11 May 2015 20:10 |
|
|
Craig
Thanks for your suggestion. AS you said i am using oracle forms for data entry. I have another problem now.
Any corrections to the oracle forms is done in windows in client system . We use oracle 10g and linux os in server.
Once the function isnumeric is done and compiled in linux the form as well as the coding done is working fine.
But now when i try to open the same form in windows to put format mask instead of the function it crashes. The form is not opening and i will not be able to make any more changes unless i revert back to the original form without the isnumeric function.
Is there any reason why the form crashes in windows 7 64 bit OS while all other forms except this form is opening. This form happens to be the main form with all functions / procedures written.
Also as per the format mask help it says use 9999 to enter only number.
01) Can i use this for a varchar data type also. I was using it only for number datatype
02) It also says oracle leaves a space in the front to accommodate + or - . Then should i have to write 9999999999 (10 times 9) to accommodate 10 digit mobile no. Mobile no field size is kept as 10
Any suggestion to solve this will be of great help.
|
|
|
|
|
|
| Re: how to avoid wild card being entered in a number data type [message #637209 is a reply to message #637187] |
Tue, 12 May 2015 22:47 |
|
|
Quote:
There are numerous reasons that could cause this, but we need to know your Forms Version in order to start offering suggested fixes. Some versions of Oracle Forms are not compatible with Windows 7 as well as 64-bit OSes.
01) Sorry for pasting the whole details about my oracle forms. Didnt know what all was required.
Forms [32 Bit] Version 10.1.2.0.2 (Production)
Oracle Toolkit Version 10.1.2.0.2 (Production)
PL/SQL Version 10.1.0.4.2 (Production)
Oracle Procedure Builder V10.1.2.0.2 - Production
PL/SQL Editor (c) WinMain Software (www.winmain.com), v1.0 (Production)
Oracle Query Builder 10.1.2.0.2 - Production
Oracle Virtual Graphics System Version 10.1.2.0.2 (Production)
Oracle Tools GUI Utilities Version 10.1.2.0.2 (Production)
Oracle Multimedia Version 10.1.2.0.2 (Production)
Oracle Tools Integration Version 10.1.2.0.2 (Production)
Oracle Tools Common Area Version 10.1.2.0.2
Oracle CORE 10.1.0.4.0 Production
02) I removed the isnumeric function written in the form and followed the suggestion to change the format mask.
I encountered the following problems
a) When i entered 9 only 1 time i.e only 9, then i was unable to enter more than 1 digit.
b) When i entered 9 10 times then i was able to enter the 10 digit mobile number and the required error message came if wild card was entered.
This solved the problem, but there are cases where mobile numbers are not furnished or wrongly furnished with only 9 digits or so. The field does not allow null values and we have to enter 0 if mobile nos are not given or whatever the mobile number was furnished even if its 8 or 9 digits.
This format mask does not allow if numbers less than 10 digits are entered.
How to go about this problem.
Thanks a lot for all the suggestion offered.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
with a semicolon (