Home » RDBMS Server » Server Administration » varchar2 accepts spaces in Oracle 10g
varchar2 accepts spaces in Oracle 10g [message #220455] Tue, 20 February 2007 14:14 Go to next message
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
Re: varchar2 accepts spaces in Oracle 10g [message #220457 is a reply to message #220455] Tue, 20 February 2007 14:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It is the expected behaviour, in any version.
Re: varchar2 accepts spaces in Oracle 10g [message #220458 is a reply to message #220455] Tue, 20 February 2007 14:29 Go to previous messageGo to next message
astar
Messages: 4
Registered: February 2007
Junior Member
shouldn't it truncate the trailing spaces ?
Re: varchar2 accepts spaces in Oracle 10g [message #220460 is a reply to message #220458] Tue, 20 February 2007 14:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
Re: varchar2 accepts spaces in Oracle 10g [message #220463 is a reply to message #220455] Tue, 20 February 2007 15:10 Go to previous messageGo to next message
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.
Re: varchar2 accepts spaces in Oracle 10g [message #220464 is a reply to message #220455] Tue, 20 February 2007 15:14 Go to previous messageGo to next message
BlackSwan
Messages: 25033
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).
Re: varchar2 accepts spaces in Oracle 10g [message #220465 is a reply to message #220455] Tue, 20 February 2007 15:20 Go to previous messageGo to next message
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.
Re: varchar2 accepts spaces in Oracle 10g [message #220467 is a reply to message #220465] Tue, 20 February 2007 15:28 Go to previous message
Mahesh Rajendran
Messages: 10672
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.
Previous Topic: NoBody is there for MIgration
Next Topic: Oracle 10g
Goto Forum:
  


Current Time: Sat Dec 03 09:45:09 CST 2016

Total time taken to generate the page: 0.07982 seconds