Oracle sqlplus functions shows as ###

After submitting a select query, which contains functions such as row_number() or any other function the result is ####. If I use rownum as something, it shows ### if I use just rownum everything is ok.

I had this problem with other generic columns but I could simply use column columnname format 9999;

The problem is not that there is not enough space, there is only one digit per record.

I have googled all over the internet and nothing has answered my problem yet.

Does anyone know what could be the problem with functions and how to format them?

select ROW_NUMBER() OVER (ORDER BY student_surname) "Nr.", 
      student_surname || ' ' || student_name "Student", 
      NVL(sum(m.stipend),0) + NVL(sum(m.compensation),0) "Total" 
from student s 
  inner join money m on m.student_id=s.id_student 
group by student_surname, student_name;

Have tried clear columns, did not work.

Answer

It looks as if you formatted numeric column with a wrong format, which doesn’t allow all digits to be displayed.

For example, this is someone’s salary – looks OK:

SQL> select sal from emp where rownum = 1;

       SAL
----------
       920

But, if you formatted it as follows (i.e. saying that I want to have only two digits (99) for that column), the result is ###:

SQL> col sal format 99
SQL> select sal from emp where rownum = 1;

SAL
---
###

SQL>

What to do? The simplest way is to clear formatting:

SQL> col sal clear
SQL> select sal from emp where rownum = 1;

       SAL
----------
       920

SQL>

---

Another possibility is that numformat (in general) is wrongly set; all these columns are NUMBER, and all of them are affected with this set numformat:

SQL> set numformat 9
SQL> select empno, mgr, sal, deptno from emp where rownum = 1;

     EMPNO        MGR        SAL     DEPTNO
---------- ---------- ---------- ----------
########## ########## ########## ##########

How to fix that? Exit SQL*Plus and reconnect.