This forum is currently locked. You can't register or post questions at this time. (read more)

In SQLPLUS Column Names not displaying Properly

All posts relating to Oracle database administration.

Moderator: Tim...

Guru3i
Senior Member
Posts: 96
Joined: Fri Aug 24, 2007 7:17 am

In SQLPLUS Column Names not displaying Properly

Postby Guru3i » Tue Nov 17, 2009 9:54 am

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
Senior Member
Posts: 96
Joined: Fri Aug 24, 2007 7:17 am

Re: In SQLPLUS Column Names not displaying Properly

Postby Guru3i » Tue Nov 17, 2009 10:01 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: In SQLPLUS Column Names not displaying Properly

Postby Tim... » Tue Nov 17, 2009 8:10 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Guru3i
Senior Member
Posts: 96
Joined: Fri Aug 24, 2007 7:17 am

Re: In SQLPLUS Column Names not displaying Properly

Postby Guru3i » Wed Nov 18, 2009 3:09 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: In SQLPLUS Column Names not displaying Properly

Postby Tim... » Thu Nov 19, 2009 10:27 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Guru3i
Senior Member
Posts: 96
Joined: Fri Aug 24, 2007 7:17 am

Re: In SQLPLUS Column Names not displaying Properly

Postby Guru3i » Fri Nov 20, 2009 6:43 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: In SQLPLUS Column Names not displaying Properly

Postby Tim... » Fri Nov 20, 2009 7:07 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Guru3i
Senior Member
Posts: 96
Joined: Fri Aug 24, 2007 7:17 am

Re: In SQLPLUS Column Names not displaying Properly

Postby Guru3i » Sat Feb 06, 2010 5:05 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: In SQLPLUS Column Names not displaying Properly

Postby Tim... » Sat Feb 06, 2010 9:13 am

OK. :)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 10 guests

cron