Posts

Showing posts from June, 2013

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#,
..