Home » SQL & PL/SQL » Client Tools » select statement returns blank spaces padded with value
select statement returns blank spaces padded with value [message #297077] Wed, 30 January 2008 01:12 Go to next message
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
Re: select statement returns blank spaces padded with value [message #297081 is a reply to message #297077] Wed, 30 January 2008 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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



Re: select statement returns blank spaces padded with value [message #297089 is a reply to message #297086] Wed, 30 January 2008 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
are using Java application deployed in weblogic 9

So maybe it is Weblogic that right pads.

Regards
Michel
Re: select statement returns blank spaces padded with value [message #297091 is a reply to message #297077] Wed, 30 January 2008 02:13 Go to previous messageGo to next message
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'


Re: select statement returns blank spaces padded with value [message #297098 is a reply to message #297091] Wed, 30 January 2008 02:40 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is
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>
Re: select statement returns blank spaces padded with value [message #297103 is a reply to message #297098] Wed, 30 January 2008 02:48 Go to previous messageGo to next message
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
Re: select statement returns blank spaces padded with value [message #297106 is a reply to message #297091] Wed, 30 January 2008 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: select statement returns blank spaces padded with value [message #297124 is a reply to message #297106] Wed, 30 January 2008 03:58 Go to previous messageGo to next message
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 ???
Re: select statement returns blank spaces padded with value [message #297126 is a reply to message #297124] Wed, 30 January 2008 04:09 Go to previous messageGo to next message
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
Re: select statement returns blank spaces padded with value [message #297130 is a reply to message #297126] Wed, 30 January 2008 04:29 Go to previous messageGo to next message
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
Re: select statement returns blank spaces padded with value [message #297133 is a reply to message #297130] Wed, 30 January 2008 04:42 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ character set is irrelevant for your case
2/ Carefully read the documentation before doing an alter database

Regards
Michel
Previous Topic: Spooling file in Unicode format
Next Topic: Host String on SQL +
Goto Forum:
  


Current Time: Fri Dec 02 20:57:03 CST 2016

Total time taken to generate the page: 0.12563 seconds