sqlplus substr formating and column width


Doing a little connection pool troubleshooting (OAAM, I am looking at you!)  I found a great sqlplus script on stackoverflow.

The script uses the substr function to format the width of each column. As in:


select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
..

The problem: The column widths are all too wide. The values specified in the substr function seem to be ignored.

The solution:  If your database is using a multi-byte character encoding (and why wouldn't you?) you need to use the substrb function. Like this:

select
       substrb(a.spid,1,9) pid,
       substrb(b.sid,1,5) sid,
       substrb(b.serial#,1,5) ser#,
..
Post a Comment

Popular posts from this blog

Apache reverse proxy with LDAP authentication

Stupid Oracle vktm tricks to improve VirtualBox performance

SAML Federation in OAM 11g R2