How to convert seconds to HH24:MI:SS

 First example is convert seconds to HH24:MI with using to_char:

with t as (select 800000000 sec from dual)
select nvl(trim(to_char(trunc(round(sec/3600, 2)), 'FM9999999900')), '00')
       || ':'
       || nvl(to_char(mod(trunc(round(sec/60, 2)), 60), 'FM00'), '00') in_hh_mi
  from t
;

IN_HH_MI       
---------------
222222:13      
1 row selected.

 Next example is convert seconds to HH24:MI:SS with using NUMTODSINTERVAL:

with t as(select NUMTODSINTERVAL(nvl(800000000, 0) , 'second') ds_int from dual)
select ds_int
       ,to_char(extract(day from ds_int) * 24
        + extract(hour from ds_int), 'FM9999999900')
          || ':' || to_char(extract(minute from ds_int), 'FM00')
          || ':' || to_char(extract(second from ds_int), 'FM00') in_hh_mi_ss
  from t;

DS_INT                                             IN_HH_MI_SS        
-------------------------------------------------- -------------------
+9259 06:13:20.000000                              222222:13:20       
1 row selected.

Thanks to forum.oracle.com topic convert from numberic seconds to HH:MM:SS answer of michaelrozar17

 Last example is pl/sql function seconds_to_hh24_mi_ss to convert seconds to HH24:MI:SS with using NUMTODSINTERVAL:

declare
  l_var varchar2(50);
 
  function seconds_to_hh24_mi_ss(p_seconds number) return varchar2
  is
    l_ds_int INTERVAL DAY(9) TO SECOND;
  begin
    l_ds_int := NUMTODSINTERVAL(nvl(p_seconds, 0), 'second');
    return to_char(extract(day from l_ds_int) * 24
           + extract(hour from l_ds_int), 'FM9999999900')             -- hh
           || ':' || to_char(extract(minute from l_ds_int), 'FM00')   -- mi
           || ':' || to_char(extract(second from l_ds_int), 'FM00') -- ss
           ;
  end;
begin
  l_var := seconds_to_hh24_mi_ss(800000000);
  dbms_output.put_line(l_var);
end;

PL/SQL procedure successfully completed.

222222:13:20