Home » SQL & PL/SQL » SQL & PL/SQL » Data Updation. (Oracle 9i)
Data Updation. [message #346664] Tue, 09 September 2008 05:09 Go to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Hi,

I am not able to update values in Oracle table.

Currently I am having
table: com_client_name
Data type: varchar2(100)
Value: Da-Xia

When i try to update value 'Da-Xia' with 'Da-xia' oracle internally converts 'X' into upper case and displays as 'Da-Xia'. I want my value to be displayed as 'Da-xia'.

UPDATE COM_CLIENT_NAME CCN SET CCN.STRFIRSTNAME = 'Da-Xia' WHERE STRCLIENTCD = '12557';

Could you please help me in this matter?

Thank You very much.
Amit.
Re: Data Updation. [message #346665 is a reply to message #346664] Tue, 09 September 2008 05:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's not Oracle doing that. Oracle itself couldn't care less about the case of your string.

I reckon you've got a trigger on that table that is doing some sort of init-cap on the data.
SELECT * FROM user_triggers WHERE table_name = 'COM_CLIENT_NAME';
Re: Data Updation. [message #346666 is a reply to message #346664] Tue, 09 September 2008 05:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, possibly you've gto the wring record

Or, the update statement you posted is actually setting the name to 'Da-Xia', not to 'Da-xia'. Is this the statement you're running?
Re: Data Updation. [message #346705 is a reply to message #346664] Tue, 09 September 2008 08:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
amit_kiran wrote on Tue, 09 September 2008 06:09
Hi,
I want my value to be displayed as 'Da-xia'.

UPDATE COM_CLIENT_NAME CCN SET CCN.STRFIRSTNAME = 'Da-Xia' WHERE STRCLIENTCD = '12557';

Could you please help me in this matter?



This is the expected result. What did you expect? Use the correct update statement and you'll get what you want. Sheesh.
Re: Data Updation. [message #346712 is a reply to message #346705] Tue, 09 September 2008 08:39 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
In case you are checking the value from another user, a "commit" will help. I hope you didn't forget that. Razz
Re: Data Updation. [message #346748 is a reply to message #346712] Tue, 09 September 2008 10:46 Go to previous messageGo to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Hi, Thanks for your reply.

Apologies, it was my mistake in posting incorrect SQL statement

The correct statement is

UPDATE COM_CLIENT_NAME CCN SET CCN.STRFIRSTNAME = 'Da-xia' WHERE STRCLIENTCD = '12557';

Currently there is a trigger which converts name into Initcap.
:NEW.strfirstname := INITCAP(:NEW.strfirstname);

I wanted to update the initial character into Caps and not later half of the name.

Thank You.
Amit.
Re: Data Updation. [message #346751 is a reply to message #346748] Tue, 09 September 2008 11:01 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi, try combining initcap with substr.
Re: Data Updation. [message #346773 is a reply to message #346748] Tue, 09 September 2008 12:24 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
amit_kiran wrote on Tue, 09 September 2008 11:46

I wanted to update the initial character into Caps and not later half of the name.



Cna you change the trigger? If not, there is no way to get that value into the column as the trigger is going to override anything you try. If you can change the trigger, use a combination of upper, lower and substr functions.
Previous Topic: Problem with Calling Procedure (PL/SQL)
Next Topic: Table Sizing
Goto Forum:
  


Current Time: Sat Dec 03 16:16:01 CST 2016

Total time taken to generate the page: 0.07295 seconds