Re: automatic conversion to upper case!

Hi,

 I'd go for a trigger plus check constraint.  True, both can be disabled, but you cannot re-enable the constraint if  there is data that violates it.
 There are ways to get it enabled anyway, but by default you will get  errors.

 you need the trigger to get the automatic conversion done.  if you go with the constraint only, your applications will get oracle  errors whenever they try to insert lowercase data.  That would require to get your apps fixed. (good idea anyway..)

Karsten

In article <b=0mOKGNKHmSg=NZ5JQnnqnViQyS_at_4ax.com>,   tkyte_at_us.oracle.com wrote:
> 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
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 10 1999 - 05:53:26 CST