Re: automatic conversion to upper case!

A copy of this was sent to Nandakumar <N.Kumar_at_rocketmail.com> (if that email address didn't require changing) On Mon, 08 Nov 1999 16:14:14 GMT, you wrote:

>trigger does not seem to be a better way to enforce upper case data in
>tables. ( as it can be disabled!! )
>

as can check constraints.

>could you give the syntax of "check" to enforce the upper case data?
>

it would look like:

create table t (
...
some_column varchar2(25) check ( some_column = upper(some_column) ), ....
)

>thanks
>Nanda
>
>In article <7vjqoj$p1o$1_at_nnrp1.deja.com>,
> karsten_schmidt_at_my-deja.com wrote:
>> Hi,
>> depends on what you are trying to do.
>>
>> for a one-time conversion just update:
>> update tab set mycol = upper(mycol) where mycol != upper(mycol);
>>
>> if you have a lot of data, you might want to optimise this a little.
>> (e.g. commit every 500 rows)
>>
>> if you want this to happen for each insert, create a trigger:
>>
>> create or replace trigger my_trig
>> before insert or update on tab
>> for each row
>> begin
>> :new.mycol := upper(:new.mycol);
>> end;
>>
>> btw, you might want to add a check constraint, so nobody diables your
>> trigger by mistake and screws you data.
>>
>> Karsten
>>
>> In article <7vdf2k$lao$1_at_nnrp1.deja.com>,
>> Nandakumar <N.Kumar_at_rocketmail.com> wrote:
>> >
>> >
>> > hi
>> >
>> > what would be the easiest way to make oracle convert the varchar2
>> values
>> > into upper case? ie to handle at the data base side.
>> >
>> > would appreciate any information in this regard!
>> > Thanks
>> >
>> > --
>> > Nandakumar
>> >
>> > Sent via Deja.com http://www.deja.com/
>> > Before you buy.
>> >
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Nov 08 1999 - 10:43:09 CST