CODEWITHSUNDEEP
oracle-database
felipecao
felipecao

Reputation: 1023

Is it possible to configure Oracle session/connection to automatically uppercase strings?

Does anyone know if it's possible to configure an Oracle session or connection so that every string that gets persisted is automatically uppercased?

For example, if I invoke a SQL like this: "INSERT INTO STUDENT (name) VALUES ('john doe')"

The information in my table would be persisted like this:

STUDENT
--------------------
ID   | 1
NAME | JOHN DOE

I've checked this entry but couldn't find anything like this: http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch3globenv.htm#sthref186

Thanks!

Upvotes: 1

Views: 180

Answers (1)

Justin Cave
Justin Cave

Reputation: 231861

There is no session-level configuration parameter for that, no.

You could write a trigger on the STUDENT table that would automatically store the data in uppercase but you'd need to do that for every table.

CREATE TRIGGER trg_student
  BEFORE INSERT ON student
  FOR EACH ROW
BEGIN
  :new.name := upper( :new.name );
END;

Depending on the problem you are trying to solve, you could potentially set your session's NLS settings to ignore case sensitivity so that the string 'John Doeis considered to be equal to the stringJOHN DOE`. The options, limitations, and downsides to this will vary with the specific version of Oracle.

Upvotes: 5

Related Questions