OraFAQ Forum: SQL & PL/SQL » UPPER CASE update

Home » SQL & PL/SQL » SQL & PL/SQL » UPPER CASE update

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

UPPER CASE update [message #282204]

Wed, 21 November 2007 03:03

mwansalovewell
Messages: 71
Registered: October 2007
Location: uk

Member

When i do the following update i still get uppercase when i do a select
SQL> update scott.emp set ename=LOWER(ENAME);

16 rows updated.

SQL> desc scott.emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> select ename from scott.emp ;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

ENAME
----------
JAMES
FORD
MILLER

14 rows selected.

How do you store update your table to have all values converted and stored in lower case on if you need them to be in upper case how do you do the update

i used example

update scott.emp set ename = UPPER(ENAME) - to have all ename update to upper case

is this update write if i run on a table with 1 million rows to change all values to upper case for a column

Report message to a moderator

Re: UPPER CASE update [message #282218 is a reply to message #282204]

Wed, 21 November 2007 03:40

kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India

Senior Member

Did you commit after update?

Commit the session then try select * from emp;

Regards,
Kiran

[Updated on: Wed, 21 November 2007 03:41]

Report message to a moderator

Re: UPPER CASE update [message #282221 is a reply to message #282218]

Wed, 21 November 2007 03:44

mwansalovewell
Messages: 71
Registered: October 2007
Location: uk

Member

commit;

still shows upper. I think oracle returns the results as upper by default.

Report message to a moderator

Re: UPPER CASE update [message #282222 is a reply to message #282204]

Wed, 21 November 2007 03:45

Michel Cadot
Messages: 68773
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> update scott.emp set ename=LOWER(ENAME);

14 rows updated.

SQL>  select ename from scott.emp ;
ENAME
----------
smith
allen
ward
jones
martin
blake
clark
scott
king
turner
adams
james
ford
miller

14 rows selected.

You surely did something wrong like selecting in another session without committing in the first one.

Regards
Michel

Report message to a moderator

Re: UPPER CASE update [message #282229 is a reply to message #282204]

Wed, 21 November 2007 03:54

kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India

Senior Member

Oracle will not show in uppercase by default.

[Updated on: Wed, 21 November 2007 03:55]

Report message to a moderator

Re: UPPER CASE update [message #282234 is a reply to message #282229]

Wed, 21 November 2007 04:01

mwansalovewell
Messages: 71
Registered: October 2007
Location: uk

Member

ok, worked fine.

Report message to a moderator

Re: UPPER CASE update [message #282311 is a reply to message #282229]

Wed, 21 November 2007 08:11

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

kir_ait wrote on Wed, 21 November 2007 04:54

Oracle will not show in uppercase by default.

Huh?

Report message to a moderator

Re: UPPER CASE update [message #282316 is a reply to message #282229]

Wed, 21 November 2007 08:26

Michel Cadot
Messages: 68773
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

kir_ait wrote on Wed, 21 November 2007 10:54

Oracle will not show in uppercase by default.

SQL> select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

Oracle shows the data as they are in the database. It does not care about lower or upper case (which in meaningless in many languages).

Regards
Michel

[Updated on: Wed, 21 November 2007 08:27]

Report message to a moderator

Re: UPPER CASE update [message #282317 is a reply to message #282311]

Wed, 21 November 2007 08:27

Frank
Messages: 7901
Registered: March 2000

Senior Member

joy_division wrote on Wed, 21 November 2007 15:11

kir_ait wrote on Wed, 21 November 2007 04:54

Oracle will not show in uppercase by default.

Huh?

This is in response to the original poster when he claimed he committed:

Quote:

still shows upper. I think oracle returns the results as upper by default.

Report message to a moderator

Re: UPPER CASE update [message #282318 is a reply to message #282317]

Wed, 21 November 2007 08:30

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Thanks Frank. Now it all makes sense. Well, maybe.

Report message to a moderator

Re: UPPER CASE update [message #282320 is a reply to message #282317]

Wed, 21 November 2007 08:31

Michel Cadot
Messages: 68773
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Ah OK! I didn't understand either.

Regards
Michel

Report message to a moderator

Re: UPPER CASE update [message #282814 is a reply to message #282204]

Fri, 23 November 2007 14:55

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Run the following code. It looks like you might have a trigger on the table that forces ename to uppercase.

select trigger_name 
from all_triggers
where table_owner = 'SCOTT'
and table_name = 'EMP';

Report message to a moderator

Previous Topic:	strange behaviour when updating
Next Topic:	How to move data between two partitions?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 01 23:07:57 CST 2026