OraFAQ Forum: Client Tools » select statement returns blank spaces padded with value

Home » Other » Client Tools » select statement returns blank spaces padded with value

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

select statement returns blank spaces padded with value [message #297077]

Wed, 30 January 2008 01:12

ShaShalini
Messages: 59
Registered: January 2007

Member

Hello

I have a simple select statement as follows :-

Select 'L' recipient from tablename. This should return L but with oracle 10G its returning 'L ' up to 32 blank spaces.

Any idea why am getting this error.

Thanks

Report message to a moderator

Re: select statement returns blank spaces padded with value [message #297081 is a reply to message #297077]

Wed, 30 January 2008 01:19

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

Senior Member
Account Moderator

Copy and paste what you have, don't just describe it.
Post your Oracle version both client and server (4 decimals).

Forgot: read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Wed, 30 January 2008 01:20]

Report message to a moderator

Re: select statement returns blank spaces padded with value [message #297086 is a reply to message #297081]

Wed, 30 January 2008 01:39

ShaShalini
Messages: 59
Registered: January 2007

Member

Sorry

The Version of Oracle DB is as follows

Oracle Database 10g Release 10.2.0.1.0 - 64bit Production
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Actually we are using Java application deployed in weblogic 9. The same application was running fine with Oracle 9i Enterprise version.

Can it be something to do with NLS_CHARACTER_SET.

Thanks

Report message to a moderator

Re: select statement returns blank spaces padded with value [message #297089 is a reply to message #297086]

Wed, 30 January 2008 02:05

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

Senior Member
Account Moderator

Quote:

are using Java application deployed in weblogic 9

So maybe it is Weblogic that right pads.

Regards
Michel

Report message to a moderator

Re: select statement returns blank spaces padded with value [message #297091 is a reply to message #297077]

Wed, 30 January 2008 02:13

ShaShalini
Messages: 59
Registered: January 2007

Member

But the problem is the application servers has remained the same , even the application also. We have just updgraded the database server from Oracle 9i Enterprise version to Oracle 10G Standard Edition. And since the upgrade we are getting this problem. So we can't say weblogic is padding the Blanks ..

I have also noticed that the character set in Oracle10G
nls_character_set = 'WE8ISO8859P1'
Oracle 9i
nls_character_set = 'US7ASCII'

Report message to a moderator

Re: select statement returns blank spaces padded with value [message #297098 is a reply to message #297091]

Wed, 30 January 2008 02:40

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Select 'L' recipient from tablename

the real code? Are you, perhaps, using the CHAR datatype instead of VARCHAR2?

SQL> create table test(col_char char(32), col_varchar2 varchar2(32));

Table created.

SQL> insert into test (col_char, col_varchar2) values ('L', 'L');

1 row created.

SQL> select length(col_char) len_char,
  2         length(col_varchar2) len_varchar2
  3  from test;

  LEN_CHAR LEN_VARCHAR2
---------- ------------
        32            1

SQL>

Report message to a moderator

Re: select statement returns blank spaces padded with value [message #297103 is a reply to message #297098]

Wed, 30 January 2008 02:48

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

Was thinking the same Littlefoot, but the behavior you describe was already present in Oracle 8.
And OP mentioned that "it worked" when they were still on Oracle 9

Report message to a moderator

Re: select statement returns blank spaces padded with value [message #297106 is a reply to message #297091]

Wed, 30 January 2008 02:56

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

Senior Member
Account Moderator

But Oracle code has changed.
But driver code has changed.
But cursor_sharing parameter may have changed.
...
If all these are used in a wrong way, it may "work" in a version and not in another one.

Regards
Michel

Report message to a moderator

Re: select statement returns blank spaces padded with value [message #297124 is a reply to message #297106]

Wed, 30 January 2008 03:58

ShaShalini
Messages: 59
Registered: January 2007

Member

The cursor_sharing has infact changed from 'EXACT' to 'FORCE'

Can this cause the problem of Padding Blanks ???

Report message to a moderator

Re: select statement returns blank spaces padded with value [message #297126 is a reply to message #297124]

Wed, 30 January 2008 04:09

ShaShalini
Messages: 59
Registered: January 2007

Member

The following parameters have been changed as compared to Oracle 9i

Cursor_sharing
db_cache_size
Session_cached_cursors
open_cursors
pga_aggregate_target

These are different in Oracle 10G

please help

Report message to a moderator

Re: select statement returns blank spaces padded with value [message #297130 is a reply to message #297126]

Wed, 30 January 2008 04:29

ShaShalini
Messages: 59
Registered: January 2007

Member

The NLS_CHARACTER_SET for Oracle 10G is 'WE*ISO8859P1' and the character set in Oracle 9i is USASCII7.

I tried to modify the character set also but it failed

ALTER DATABASE CHARACTER SET US7ASCII;

ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

Report message to a moderator

Re: select statement returns blank spaces padded with value [message #297133 is a reply to message #297130]

Wed, 30 January 2008 04:42

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

Senior Member
Account Moderator

1/ character set is irrelevant for your case
2/ Carefully read the documentation before doing an alter database

Regards
Michel

Report message to a moderator

Previous Topic:	Spooling file in Unicode format
Next Topic:	Host String on SQL +

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 26 11:22:05 CDT 2025