In SQLPLUS Column Names not displaying Properly

[Guru3i]

Hi Tim,

Greetings and thanks for your continued help to developer community of ORACLE.

I am connecting to 2 databases through a 10.1.0.4.2 Version of SQLPLUS.
1st Database runs with - 10.2.0.4 Patchset
2nd Database runs with - 10.2.0.1.0 Patchset

From both the Databases we are running a long query with columns using Decode funtions. We are not able to see the column names completely in the 1st DB i.e 10.2.0.4 Patchset. Whereas in 2nd DB - Version 10.2.0.1.0 - Its working properly. I am not sure whether here DB version is the problem. I have attached the screen shot of both the scenarios. Please see it .

This is the query i am firing from both DB's. Here in the 1st column with decode function doesnt prints the column heading in SQLPLUS. Please see the screen shot where i have selected and highlighted the same.

"
SELECT DECODE('ENG','ENG',A.PC_DESC,A.PC_DESC_BL)
FROM PCOM_CODES A, PCOM_CODES_APPL_CODES B
WHERE CAC_TYPE = 'VEH_CC_TYPE'
AND A.PC_TYPE = CAC_PC_TYPE_2
AND A.PC_CODE = B.CAC_PC_CODE_2
AND ROWNUM < =2
"

Is there any specific User specific or DB specific setting for such issues. We dont want to use any alias column names for
specific purpose to be handled with that column name.

[Guru3i]

Hi Tim,

How to upload a screen shot to your forum. I am unable to do so. So could not upload the screen shot i wanted to.

Thanks

[Tim...]

Hi.

I would prefer it if you didn't upload screen shots as it's gonna fill up my database real quick and that costs me money.

This is actually not a database issue, it's an issue with the SQL*Plus tool. There are an assortment of environment options you can see using "HELP SET". Chances are it's the linesize one you need, like:

Code: Select all

SET LINESIZE 500

If you are talking about the display of data from within LONG or CLOB columns, then you need to alter the LONG parameter, like:

Code: Select all

SET LONG 10000

Cheers

Tim...

[Guru3i]

Hi Tim,

We have tried all those, its not working. Its not the actually the data. Its the Column heading.

i.e
SELECT DECODE('ENG','ENG',A.PC_DESC,A.PC_DESC_BL)
FROM PCOM_CODES A, PCOM_CODES_APPL_CODES B
WHERE CAC_TYPE = 'VEH_CC_TYPE'
AND A.PC_TYPE = CAC_PC_TYPE_2
AND A.PC_CODE = B.CAC_PC_CODE_2
AND ROWNUM < =2

The Column binded with BOLD needs to print in the same way, it actually prints only half i.e not beyond A.PC_DESC

In the same SQLPLUS session its working for user A but not working for user B. The only difference i could infer is DB Version. In 10.2.0.4 - It doesnt works and in 10.2.0.1.0 it works. Your help in this regard is highly appreciated.

Thanks

[Tim...]

Hi.

Oh. I see. You mean the column heading is being truncated.

This is pretty normal. The heading is being truncated to the estimated size of the result. You need to manually format the column to the appropriate size, or preferable give it a sensible alias. Here the column has been renamed to description using the " AS description".

Code: Select all

SELECT  DECODE('ENG','ENG',A.PC_DESC,A.PC_DESC_BL) AS description
FROM PCOM_CODES A, PCOM_CODES_APPL_CODES B...

I can then format the column width using the COLUMN command.

Code: Select all

COLUMN description FORMAT A50

A50 = 50 alphanumeric characters.

Cheers

Tim...

[Guru3i]

Hi Tim,

Thanks for the update.

As i had mentioned earlier, we are not in a position to give any ALIAS name for the column. Its not the problem in SQLPLUS alone actually we have this problem in front end also, we need the whole column name in the front end to handle few stuffs dynamically. So having a alias column is a problem for us. Hope i have made it clear.

Thanks

Guru

[Tim...]

Hi.

Don't really know what to suggest. I would never not use an alias for something like that, so I don't know what I would do.

Any column formatting mentioned earlier is SQL*Plus specific, so that is no good for you.

I think you need to contact Oracle support and supply a test case to them and ask for instructions. I'm guessing they will say use an alias.

Cheers

Tim...

[Guru3i]

Hi Tim,
I did log a SR with Oracle Support and the solution was very strange.

I had to change the CURSOR_SHARING parameter value to = FORCE and check again. It did work fine, it seems there is a bug for the same in version 10.2.0.4 of SQLPLUS. Thanks for your time on this.

Thanks

[Tim...]

OK.