Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create or replace function check_time(myStr varchar2) return number 
is
 p_temp date;
begin
   p_temp := TO_DATE(myStr, 'HH24:MI:SS');

   return 1;
exception
   when others then 
     return 0;
end;
/
with cte (str) as (
  select '0' from dual union all
  select '0:0' from dual union all
  select '0:0:0' from dual union all
  select '00:00:00' from dual union all
  select '23:59:59' from dual union all
  select '00:00:60' from dual union all
  select '00:60:00' from dual union all
  select '24:00:00' from dual
)
select str, check_time(str)
from cte


STR
CHECK_TIME(STR)


0
1

0:0
1

0:0:0
1

00:00:00
1

23:59:59
1

00:00:60
0

00:60:00
0

24:00:00
0


create or replace function check_time(myStr varchar2) return number 
is
 p_temp date;
begin
   p_temp := TO_DATE(myStr, 'FXHH24:MI:SS');

   return 1;
exception
   when others then 
     return 0;
end;
/
with cte (str) as (
  select '0' from dual union all
  select '0:0' from dual union all
  select '0:0:0' from dual union all
  select '00:00:00' from dual union all
  select '23:59:59' from dual union all
  select '00:00:60' from dual union all
  select '00:60:00' from dual union all
  select '24:00:00' from dual
)
select str, check_time(str)
from cte


STR
CHECK_TIME(STR)


0
0

0:0
0

0:0:0
0

00:00:00
1

23:59:59
1

00:00:60
0

00:60:00
0

24:00:00
0


with cte (str) as (
  select '0' from dual union all
  select '0:0' from dual union all
  select '0:0:0' from dual union all
  select '00:00:00' from dual union all
  select '23:59:59' from dual union all
  select '00:00:60' from dual union all
  select '00:60:00' from dual union all
  select '24:00:00' from dual
)
select str, validate_conversion(str as date, 'FXHH24:MI:SS')
from cte


STR
VALIDATE_CONVERSION(STRASDATE,'FXHH24:MI:SS')


0
0

0:0
0

0:0:0
0

00:00:00
1

23:59:59
1

00:00:60
0

00:60:00
0

24:00:00
0