SQL*Plus - numeric column shows hex characters

Question

Was doing a pivot report in SQL*Plus, counting number of records with status 'ERROR' and encounter hex values in the results. It can be reproduced on 11g using the following:

SQL> select 1 error from dual;

     ERROR
----------
##########

Then I tested with a few variety:

SQL> select 1 errors from dual;

    ERRORS
----------
         1

SQL> select 'a' error from dual;

ERROR
-----------------------------------------------------------------
a

SQL> select 'a' errors from dual;

E
-
a

Seems like having a column named 'error' does weird stuffs to your result in SQL*Plus as this problem does not happen in SQL developer. Does anyone has an explanation? There seems no workaround except renaming it..

Answer 1

Looks like the NUMBER format for column ERROR is set by default in SQL*Plus.

ATTRIBUTE command shows the format of this.

SQL> attribute error
COLUMN   ERROR ON
FORMAT   A65
word_wrap 

---

So, lets clear it.

SQL> column error clear

---

Now,

SQL> select 12 error from dual;

     ERROR
----------
        12

Full Script:

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 24 04:26:15 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> attribute error
COLUMN   ERROR ON
FORMAT   A65
word_wrap 

SQL> show numwidth
numwidth 10

SQL> select 12 error from dual;

     ERROR
----------
##########

SQL> column error clear

SQL> attribute error
SP2-0046: ATTRIBUTE 'error' not defined

SQL> select 12 error from dual;

     ERROR
----------
        12

SQL> column error format A10

SQL> select 12 error from dual;

     ERROR
----------
##########

Answer 2

It is just a workarround, but you can cast your int as char.

SQL> select cast(1 as char(50)) as error from dual;`

ERROR
---------
1