Convert integer to hours and minutes - getting PL/SQL numeric or value error

Question

I am getting

ORA-06502 PL/SQL numeric or value error

when I am trying to execute this statement from Report Builder 6i and Oracle database 11g.

select to_char(to_date(lateby, 'sssss'), 'mi:ss') 
from attendancelogs   
where emp_no = :emp_no 
  and attendancedate = :dat;

late    number(15);

The same statement when run under SQL*Plus is running fine without any error and fetching values the way I want.

Any help will be much appreciated.

Regards, Mac

Answer 1

It might be a Reports bug, I don't know, but as a workaround you could try wrapping this into a stored procedure in the database as follows:

CREATE OR REPLACE FUNCTION sssss_to_miss ( p1 in varchar2 ) RETURN VARCHAR2 IS
BEGIN
   RETURN ( to_char(to_date(p1, 'sssss'), 'mi:ss') );
END;
/

Then in the Reports Code:

select sssss_to_miss (lateby) 
from attendancelogs   
where emp_no = :emp_no 
  and attendancedate = :dat;

late    number(15);

Alternatively, something like (not tested):

select to_char(to_number(lateby)/60,'00') || ':' || to_char(mod(to_number(lateby),60),'00') 
    from attendancelogs   
    where emp_no = :emp_no 
      and attendancedate = :dat;