Setting NUMWIDTH in PL/SQL Developer

This post is based on a problem I was asked to look at recently.

 

A colleague was using PL/SQL Developer to compare two tables in an Oracle 11 database. He was matching the rows on a key column then checking the corresponding values from a different column and reporting them if they did not match. His query returned almost 2000 rows but they appeared to have equal, not different values. Five people looked at the problem and could not see the cause. They then gave it to me, suggested it might be an Oracle bug and asked me to look for a patch to fix it.

 

I have reproduced the issue in the screen print below although in my example, the two tables only have two columns and one row each. COL1 is set to PI in both tables. I have given this column an alias of NAME. The query joins the two tables using this value as a key. It then compares the COL2 values and reports them if they do not match. I have given TAB1.COL2 an alias of VALUE1 and TAB2.COL2 an alias of VALUE2. According to the screen print, COL2 has the same value in both tables. This being the case, the query should not have displayed these values. As usual, click on the image to enlarge it if necessary:

I’m not an expert with PL/SQL Developer so I decided to see if the same thing happened in SQL*Plus and it did:

 

SQL> select a.col1 name, a.col2 value1, b.col2 value2

  2  from tab1 a, tab2 b

  3  where a.col1 = b.col1

  4  and a.col2 != b.col2

  5  /

 

NAME     VALUE1     VALUE2

---- ---------- ----------

PI   3.14159265 3.14159265

 

SQL>

 

I noticed that the value of COL2 filled the output area in both cases so I checked the value of NUMWIDTH:

 

SQL> show numwidth

numwidth 10

SQL>

 

I gave it a higher value. At this point I cheated a bit because I had set up the test data so I knew how big to make it:

 

SQL> set numwidth 22

SQL>

 

I ran the query again and this time it showed that the values had been different all the time:

 

SQL> select a.col1 name, a.col2 value1, b.col2 value2

  2  from tab1 a, tab2 b

  3  where a.col1 = b.col1

  4  and a.col2 != b.col2

  5  /

 

NAME                 VALUE1                 VALUE2

---- ---------------------- ----------------------

PI         3.14159265358979  3.1415926535897932385

 

SQL>

 

I wondered how I might do the same thing in PL/SQL Developer so I did a bit of research. Then I clicked on Tools / Preferences / SQL Window and put a tick against Number fields to_char. This made the little box next to it show up as green in the screen print below:

I clicked on Apply and OK in the usual way and when I ran the query again, the difference between the column values was obvious: