Re: Force UPPERCASE for data placed in DB

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Wed, 14 Jul 1999 17:49:21 GMT
Message-ID: <378ccc2c.9796837@inet16.us.oracle.com>

On Wed, 14 Jul 1999 09:56:08 -0400, sjohnson <sjohnson_at_loudoun.com> wrote:

>Is there a method to have Oracle convert all text stored in tables to
>uppercase? I thought about triggers but the number of triggers for each
>table and column would be difficult and cause a load on the system.
>The front end has already been built and would require significant
>changes to force all entry fields to upper case.

You only need a single trigger per table.

eg.

SQL> create table t(

  2  column1 varchar2(10),
  3  column2 varchar2(10),
  4  column3 varchar2(10),
  5  column4 varchar2(10) );

Table created.

SQL> l
  1 create or replace
  2 trigger update_T_biufer
  3 before insert or update
  4 on T
  5 for each row
  6 begin

  7    :new.column1 := upper( :new.column1 );
  8    :new.column2 := upper( :new.column2 );
  9    :new.column3 := upper( :new.column3 );
 10    :new.column4 := upper( :new.column4 );

 11* end;
SQL> /
Trigger created.

SQL> insert into t ( column1 ) values ( 'sdf' ); 1 row created.

SQL> select * from t;

COLUMN1 COLUMN2 COLUMN3 COLUMN4 ---------- ---------- ---------- ---------- SDF SQL> update t set column2 = 'wer';
1 row updated.

SQL> select * from t;

COLUMN1 COLUMN2 COLUMN3 COLUMN4 ---------- ---------- ---------- ---------- SDF WER hope this helps

chris.

>
>Any ideas?
>stj
>
>

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jul 14 1999 - 12:49:21 CDT