Home » Other » 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  |
LaFilipina
Messages: 51
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 #288653 is a reply to message #288632] |
Tue, 18 December 2007 04:30  |
|
|
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 #288661 is a reply to message #288657] |
Tue, 18 December 2007 04:40 |
LaFilipina
Messages: 51
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 |
ThomasG
Messages: 3212
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 #288684 is a reply to message #288671] |
Tue, 18 December 2007 05:25 |
LaFilipina
Messages: 51
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 |
 |
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #288760 is a reply to message #288720] |
Tue, 18 December 2007 10:42 |
LaFilipina
Messages: 51
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
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jan 18 08:19:35 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
