| Data Inserts [message #4375] |
Tue, 03 December 2002 14:38  |
Michele
Messages: 77
Registered: December 2000
|
Member |
|
|
Hi all,
I had this question in the general forum but I received no response so I'd thought I would try it here.
We have a third party application that our users work on to insert data into an Oracle Database. The records that exist now have first and last name beginning characters in uppercase ie: Mickey Mouse.
We want to control consistency and the third application the way it's developed now will allow data to be entered mickey mouse.
Where is the best place to control this? Is their something that can be done on the Oracle side when a record is inserted or should we control the conversion on the application side.
Any thoughts would be appreciated. Thanks
|
|
|
|
| Re: Data Inserts [message #4376 is a reply to message #4375] |
Tue, 03 December 2002 14:52  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You can use a trigger on the table to enforce a particular formatting (all uppercase, all lowercase, proper case).
create or replace trigger t_trg
before insert on t
for each row
begin
:new.first_name := upper(:new.first_name);
:new.last_name := upper(:new.last_name);
end;
/
Keep in mind that the function for proper case (INITCAP) does not always return values as you would expect:
lasalle => Lasalle
mcdonald => Mcdonald
Other folks like to capture the data as entered and format it on retrieval (a view can be used so the formatting happens in one centralized place):
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1231436464997
Note the reference to function-based indexes that can be used to retrieve data quickly regardless of the stored format (case-insensitive search).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
