I have the TEMPO column with type STRING and I need to add the values as hours.
I use all sorts of queries to do this sum, but I always fall into the field problem with DATATYPE DATE do not accept values larger than 23:59:59 .
How to do this via query in oracle?
TIME_AVG
42:12:57
null
98:31:06
20:16:12
04:00:31
05:18:39
05:18:06
50:09:12
22:59:27
null
25:39:39
25:20:27
42:12:57
null
98:31:06
20:16:12
04:00:31
To sum these STRINGS you can transform them in seconds like this:
As an example I'm adding the first and second line of your question.
SELECT ( SUBSTR( '42:12:57', 1, 2 ) * 3600 ) +
( SUBSTR( '42:12:57', 1, 2 ) * 60 ) +
SUBSTR( '42:12:57', 7, 2 ) +
( SUBSTR( '98:31:06', 1, 2 ) * 3600 ) +
( SUBSTR( '98:31:06', 1, 2 ) * 60 ) +
SUBSTR( '98:31:06', 7, 2 ) AS SEGUNDOS
FROM DUAL
Andtotransformthesesecondsintotheresultingstringtostoreitinthebank,doso:
SELECTTO_CHAR(TRUNC(512463/3600),'FM9900')||':'||TO_CHAR(TRUNC(MOD(512463,3600)/60),'FM00')||':'||TO_CHAR(MOD(512463,60),'FM00')ASMEDIAFROMDUAL 
I think with this you can easily create a function.
An example script to sum all the values in the TIME_AVG column of the table.
DECLARE
SEGUNDOS NUMERIC( 15, 0 );
TEMP NUMERIC( 15, 0 );
BEGIN
SEGUNDOS := 0;
FOR REC IN ( SELECT TIME_AVG FROM TABELA )
LOOP
SELECT ( SUBSTR( REC.TIME_AVG, 1, 2 ) * 3600 ) +
( SUBSTR( REC.TIME_AVG, 1, 2 ) * 60 ) +
SUBSTR( REC.TIME_AVG, 7, 2 ) INTO TEMP FROM DUAL;
SEGUNDOS := SEGUNDOS + TEMP;
END LOOP;
.... RETORNA O VALOR
END;