Home » SQL & PL/SQL » Client Tools » column name getting truncated upon displaying the result
column name getting truncated upon displaying the result [message #288632] Tue, 18 December 2007 04:03 Go to next message
LaFilipina
Messages: 46
Registered: May 2007
Location: spain
Member
Hi All,

I have a rather weird question. One developer came to me yesterday complaining about their vb code previously running fine but suddenly was spitting out errors. She told me nothing was changed on the code since 2006. I checked the oracle objects involved and they were not changed since 2006 either. Here's their problem:

A part of their codes looks like this:

SELECT DISTINCT SUBSTR(SS.LONG_COLUMN_NAME, 1, 4) FROM
SOME_VIEW SS

Now, the result of this query should look something like this:

SUBSTR(SS.LONG_COLUMN_NAME, 1, 4)
--------------------------------
VALUE1
VALUE2
VALUE3

They told me it has been like this every since. It looks like the name of the column (don't ask me why they are not using alias), is being used in other part of the program. But yesterday, they found out that the result given by the query above started to look like this:

SUBSTR(SS.LONG_COLUMN_NAME, 1,
------------------------------
VALUE1
VALUE2
VALUE3

I tried connecting via sqlplus and sqr and I am getting the same result as the latter. The developer keeps on telling me that it wasn't like that before. They keep on telling me that before, when they run the same query even on their sqlplus, they get the name of the column with whole string and not truncated as the latter result above.

My dba question now, is there any settings on a database level that affects how it displays the name of the column in its result set? Kindly help me.

Thanks,
LaFilipina
Re: column name getting truncated upon displaying the result [message #288643 is a reply to message #288632] Tue, 18 December 2007 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on:
- database server version
- client version
- cursor_sharing parameter value

Check all these.

Regards
Michel
Re: column name getting truncated upon displaying the result [message #288652 is a reply to message #288643] Tue, 18 December 2007 04:29 Go to previous messageGo to next message
LaFilipina
Messages: 46
Registered: May 2007
Location: spain
Member
Thanks Michel for the quick response.

As for the database server version, I am not sure if you refer the oracle version - if so, then we have oracle 9.0.2 and this wasn't changed prior to the errors received.

I would need to check on the client version if it was changed. There is a big possibility on this. If so, is there any particular setting that can be changed on the client? And how so.

Cursor_sharing parameter value wasn't also changed prior to the incident not even after.

Thanks and will be looking forward to your response once again Smile

Re: column name getting truncated upon displaying the result [message #288653 is a reply to message #288632] Tue, 18 December 2007 04:30 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

SQL> select substr(lastname,1,5) from employees;

SUBST
-----
Baer
Baida
Banda
Bates
Bell
Berns
Bisso
Bloom
Bull

9 rows selected.

SQL>column substr(lastname,1,5) format a20 heading ' substr(lastname,1,5)';
SQL>  select substr(lastname,1,5) from employees;

 substr(lastname,1,5
--------------------
Baer
Baida
Banda
Bates
Bell
Berns
Bisso
Bloom
Bull

9 rows selected.


Kiran.

[Updated on: Tue, 18 December 2007 04:31]

Report message to a moderator

Re: column name getting truncated upon displaying the result [message #288657 is a reply to message #288653] Tue, 18 December 2007 04:34 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Although it is possible, I believe it is not probable - who would do something like this?
Quote:

SQL>column substr(lastname,1,5) format a20 heading ' substr(lastname,1,5)';
Re: column name getting truncated upon displaying the result [message #288660 is a reply to message #288632] Tue, 18 December 2007 04:39 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Ya, Can't do it for all the columns every time.
But OP may get relief from developer for now at least.


Kiran.
Re: column name getting truncated upon displaying the result [message #288661 is a reply to message #288657] Tue, 18 December 2007 04:40 Go to previous messageGo to next message
LaFilipina
Messages: 46
Registered: May 2007
Location: spain
Member
Thanks Kiran, but like what littlefoot said, though it is possible, it is not probable since we can't embed this in our vb code. Besides, we could have just very well add an alias but this is going to require recoding the already migrated code in production. You know how development process goes. We would like to avoid recoding and remigration of objects in production as much as possible.
Re: column name getting truncated upon displaying the result [message #288671 is a reply to message #288661] Tue, 18 December 2007 04:56 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I don't know VB that much, but in Java you can get the column names, too, by using a query metadata object.

I can't imagine that would be influenced by sql*plus settings.

The one way to fix it in the long run would be to use column aliases in the queries.

As for the short term fix, I would suggest to try to find out what was changed since it last worked.

Maybe some new VB runtime DLLs? A new Client version? Some OS Patch on the client side?

There seem to be some bugs with column names longer than 17 characters or with Oracle through VB.

Maybe an OS/Client update was installed that tries do address that problem by truncating the column names in the driver?

Re: column name getting truncated upon displaying the result [message #288681 is a reply to message #288671] Tue, 18 December 2007 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
10g client introduced the limitation to 30 characters for implicit column aliases (those derived from the expression) as for all other identifiers.
And guess what: "SUBSTR(SS.LONG_COLUMN_NAME, 1, " is just 30 characters.

Regards
Michel
Re: column name getting truncated upon displaying the result [message #288684 is a reply to message #288671] Tue, 18 December 2007 05:25 Go to previous messageGo to next message
LaFilipina
Messages: 46
Registered: May 2007
Location: spain
Member
Hi ThomasG,

You are right, it is not necessarily affected by sqlplus settings since I tried different clients for doing queries, I am getting similar result.

I agree with you regarding the long term fix for this. But this has quite set some big curiosity on my side.

Some vb dlls could probably the culprit.. but even if I ran the same query on sqlplus on our unix (which nothing has been changed at all), I am getting the same result. Since all kind of interfaces show the same result. Having this said, it really seems like the the issue lies on the database.

Hi Michel,

What you have said is quite interesting and could very well be the cause. The only thing that bugs me is that we don't have oracle 10g client installed on our unix where the 9i database in question is housed. But if I ran the query using the sqlplus from the unix box, I get the same result.

In any case, do you have a link of the site where this 10g client limitation on implicit aliases is explained?

Thanks. You all are really helpful.
Re: column name getting truncated upon displaying the result [message #288686 is a reply to message #288684] Tue, 18 December 2007 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For cursor_sharing erratic behaviour part:
SQL> @v

Version Oracle : 9.2.0.6.0

SQL> select substr(rpad(dummy,35,dummy),1,25)||'1234567890' from dual;
SUBSTR(RPAD(DUMMY,35,DUMMY),1,25)||
-----------------------------------
XXXXXXXXXXXXXXXXXXXXXXXXX1234567890

1 row selected.

BZFD01> alter session set cursor_sharing=force;

Session altered.

SQL>  select substr(rpad(dummy,35,dummy),1,25)||'1234567890' from dual;
SUBSTR(RPAD(DUMMY,35,DUMMY),1,
----------------------------------------------------------------------------------------------------------
XXXXXXXXXXXXXXXXXXXXXXXXX1234567890

1 row selected.

SQL> select '123456789012345678901234567890' from dual;
'12345678901234567890123456789
--------------------------------
123456789012345678901234567890

1 row selected.

SQL> alter session set cursor_sharing=exact;

Session altered.

SQL>  select '123456789012345678901234567890' from dual;
'12345678901234567890123456789
------------------------------
123456789012345678901234567890

1 row selected.

I don't have any link I just notice the behaviour.
More it changed between 10.1, 10.2.0.2.1 and 10.2.0.3.

Regards
Michel
Re: column name getting truncated upon displaying the result [message #288690 is a reply to message #288684] Tue, 18 December 2007 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For column alias, you may be interested in reading Metalink notes:
359055.1: Column Header is getting Truncated to 30 while Selecting from Dual
4867724: COLUMN HEADER IS GETTING TRUNCATING TO 30 WHILE SELECTING FROM DUAL

Regards
Michel

[Updated on: Tue, 18 December 2007 06:12]

Report message to a moderator

Re: column name getting truncated upon displaying the result [message #288720 is a reply to message #288690] Tue, 18 December 2007 07:32 Go to previous messageGo to next message
LaFilipina
Messages: 46
Registered: May 2007
Location: spain
Member
Thanks Michel. This is really something very useful. I have done some tests as well following test you did on your previous note. I tried to change the session setting for cursor_sharing and i got the desired result by setting the cursor_sharing session to EXACT.

I am seeing better light on this. Thanks for all your help Smile Smile Smile
Re: column name getting truncated upon displaying the result [message #288760 is a reply to message #288720] Tue, 18 December 2007 10:42 Go to previous messageGo to next message
LaFilipina
Messages: 46
Registered: May 2007
Location: spain
Member
Hi All,

Just an update on this, hoping that someone else might find this useful too in the future.

We have changed the database parameter CURSOR_SHARING to EXACT and it did the trick! It actually resolved the issue I have explained.

The parameter was changed to FORCE a while back to solve some performance issue. Little did we know that this has some effects on the behavior of some queries.

Thank you very much again to all the ideas and help.

More power!

LaFilipina
Re: column name getting truncated upon displaying the result [message #288771 is a reply to message #288760] Tue, 18 December 2007 11:18 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thank you for the feedback.

Regards
Michel
Previous Topic: iSqlPlus problem
Next Topic: Use of toad formatted code in Unix
Goto Forum:
  


Current Time: Mon Dec 05 14:53:09 CST 2016

Total time taken to generate the page: 0.09761 seconds