SQL * Plus - a numeric column shows hexadecimal characters

Question

I made a summary report in SQL * Plus, counting the number of records with the status "ERROR" and encountering hexadecimal values ​​in the results. It can be played on 11g using the following:

SQL> select 1 error from dual; ERROR ---------- ########## 

Then I tested several options:

 SQL> select 1 errors from dual; ERRORS ---------- 1 SQL> select 'a' error from dual; ERROR ----------------------------------------------------------------- a SQL> select 'a' errors from dual; E - a 

It seems that a column called "error" does strange things for your result in SQL * Plus, since this problem does not occur in the SQL developer. Does anyone have an explanation? There seems to be no workaround other than renaming.

Answer 1

It appears that the NUMBER format for the ERROR column is set by default in SQL*Plus .

ATTRIBUTE shows the format of this.

 SQL> attribute error COLUMN ERROR ON FORMAT A65 word_wrap 

---

So let's clean 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 desktop, but you can use your int as a char.

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