OraFAQ Forum: Server Administration » varchar2 accepts spaces in Oracle 10g

Home » RDBMS Server » Server Administration » varchar2 accepts spaces in Oracle 10g

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

varchar2 accepts spaces in Oracle 10g [message #220455]

Tue, 20 February 2007 14:14

astar
Messages: 4
Registered: February 2007

Junior Member

Hi,

We have oracle 9i and oracle 10g. For some reason Oracle 10g accepts spaces in varchar2 as supplied values for ex :
if table has varchar2(20) as column and I insert values
'abc' it works fine
but if i insert 'abc ', it shows results as 'abc ' instead of 'abc'.
Our application did not had this issue in 9i, it used to truncate trailing spaces automatically.
The data has been loaded from FixedWidth file using cobol api. No change has been made in the code so it's only due to oracle 9i to 10g migration.
Is there any kind of setting in 10g which changes this behaviour to accepted values ?

Thanks in advance.
Astar

Report message to a moderator

Re: varchar2 accepts spaces in Oracle 10g [message #220457 is a reply to message #220455]

Tue, 20 February 2007 14:24

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

It is the expected behaviour, in any version.

Report message to a moderator

Re: varchar2 accepts spaces in Oracle 10g [message #220458 is a reply to message #220455]

Tue, 20 February 2007 14:29

astar
Messages: 4
Registered: February 2007

Junior Member

shouldn't it truncate the trailing spaces ?

Report message to a moderator

Re: varchar2 accepts spaces in Oracle 10g [message #220460 is a reply to message #220458]

Tue, 20 February 2007 14:39

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

It is a purposely intended blank character. Anything within the quotes will be inserted.

scott@9i >  create table t1 (c1 varchar2(20));

Table created.

scott@9i > insert into t1 values ('ABC ');

1 row created.

scott@9i > commit;

Commit complete.
scott@9i > select c1,length(c1) from t1;

C1                   LENGTH(C1)
-------------------- ----------
ABC                           4

May be your application had a trigger/function/some mechanism that would do an RTRIM.

Report message to a moderator

Re: varchar2 accepts spaces in Oracle 10g [message #220463 is a reply to message #220455]

Tue, 20 February 2007 15:10

astar
Messages: 4
Registered: February 2007

Junior Member

Yes I noticed that in 10g and 9i the behaviour is same.
Is there some kind of setting to turn this off ?
Our application requires spaces to be trimmed.

thanks,
A.

Report message to a moderator

Re: varchar2 accepts spaces in Oracle 10g [message #220464 is a reply to message #220455]

Tue, 20 February 2007 15:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Yes I noticed that in 10g and 9i the behaviour is same.

So this NOT a database issue.

>Our application requires spaces to be trimmed.
So the application should automagically trim the space(s).

Report message to a moderator

Re: varchar2 accepts spaces in Oracle 10g [message #220465 is a reply to message #220455]

Tue, 20 February 2007 15:20

astar
Messages: 4
Registered: February 2007

Junior Member

Yes you are right not a db issue. but is there a way to automatically set this in oracle ?

Thanks.

Report message to a moderator

Re: varchar2 accepts spaces in Oracle 10g [message #220467 is a reply to message #220465]

Tue, 20 February 2007 15:28

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> but is there a way to automatically set this in oracle
Already responded.
You can write a before insert trigger to do this in backend, which will be painful if you are loading lot of data.
Better to use an RTRIM in you application code.

Report message to a moderator

Previous Topic:	NoBody is there for MIgration
Next Topic:	Oracle 10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 15:37:23 CST 2024