Home » SQL & PL/SQL » SQL & PL/SQL » keyboard input related
keyboard input related [message #212182] Thu, 04 January 2007 03:44 Go to next message
brihaspatirai
Messages: 24
Registered: November 2006
Location: pune
Junior Member
Hi
to all


i want to restrict the char value in a numeric field like if i try to enter char than at instance it prompt that u can not enter char in it
Re: keyboard input related [message #212202 is a reply to message #212182] Thu, 04 January 2007 04:21 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Forms?

MHE
Re: keyboard input related [message #212204 is a reply to message #212182] Thu, 04 January 2007 04:23 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oracle will do it without your intervention.
SQL> create table test (col number);

Table created.

SQL> insert into test (col) values ('A');
insert into test (col) values ('A')
                               *
ERROR at line 1:
ORA-01722: invalid number


SQL>
But, I somehow think that this isn't what you had in mind ...
Re: keyboard input related [message #212470 is a reply to message #212182] Fri, 05 January 2007 08:31 Go to previous message
Giraffe
Messages: 10
Registered: January 2007
Location: Suffolk, UK
Junior Member
I think this is perhaps more what you're after - a character column which only allows numeric characters - that is, if you want a database level solution. The CHECK constraint forces any string inserted to be composed only of numeric characters. It does this by first replacing all the numeric characters with the 0 character, and then replacing all the 0 characters with the empty string. Therefore, if there are any characters left in the string (string IS NOT NULL), they are non-numeric and the test fails.

SQL> CREATE TABLE example ( exfield VARCHAR2 ( 10 ) );

Table created

SQL> ALTER TABLE example
  2  ADD CONSTRAINT chk_exfield_numeric
  3  CHECK ( REPLACE (TRANSLATE(exfield, '123456789.', '0000000000'), '0', '') IS NULL );

Table altered

SQL> INSERT INTO example ( exfield ) VALUES ( '012345' );

1 row inserted

SQL> INSERT INTO example ( exfield ) VALUES ( 'ABCDEF' );

INSERT INTO example ( exfield ) VALUES ( 'ABCDEF' )

ORA-02290: check constraint (PLAY.CHK_EXFIELD_NUMERIC) violated
Previous Topic: PLS-00201:identifier 'PROJECT_FROM_TEMPLATE' must be declared.
Next Topic: Display ResultSet
Goto Forum:
  


Current Time: Tue Dec 06 08:08:58 CST 2016

Total time taken to generate the page: 0.05385 seconds