SELECT codigo, nome,
NVL(ROUND(AVG(TO_NUMBER(NOTA_FINAL)),2),'-1') AS MEDIA_ALUNO
FROM nota
WHERE ...
very well, the problem is specifically on top of the "note_final" field. The value where it is stored is an open field, that is, I can put both numerical and textual values.
I need a function to identify the returned data type in order to use along with a CASE function.
DECLARE
V_VALOR VARCHAR2(100);
BEGIN
SELECT TO_NUMBER('A')
INTO V_VALOR
FROM DUAL;
EXCEPTION
-- Se cair aqui, é porque é ALFANUMÉRICO, ou seja,
-- não deixou realizar o TO_NUMBER
WHEN OTHERS THEN
V_VALOR := 0;
END;
If you want to test this on multiple SELECT lines, I believe that the ideal is to create a function that tests this. Hence you can quietly use multiple lines without problem and without error.
CREATE OR REPLACE FUNCTION "LYCEUM"."RETORNA_NUMERICO" (
VALOR_ENVIADO character varying
)
RETURN NUMBER
IS VALOR_RETORNADO NUMBER(3,2);
begin
SELECT TO_NUMBER(VALOR_ENVIADO)
INTO VALOR_RETORNADO
FROM DUAL;
EXCEPTION
-- Se cair aqui, é porque é ALFANUMÉRICO, ou seja,
-- não deixou realizar o TO_NUMBER
WHEN OTHERS THEN
VALOR_RETORNADO := -1;
RETURN(VALOR_RETORNADO);
end;
SELECT RETORNA_NUMERICO('4.5') FROM DUAL
*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "LYCEUM.RETORNA_NUMERICO", line 15
CREATE OR REPLACE PACKAGE PCK_CRISTIANO IS
FUNCTION fun_E_NUMERO (p_VALOR VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
PROCEDURE prc_CONSULTA;
END;
/
CREATE OR REPLACE PACKAGE BODY PCK_CRISTIANO IS
FUNCTION fun_E_NUMERO (p_VALOR VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
v_VALOR NUMBER;
BEGIN
v_VALOR := TO_NUMBER(p_VALOR);
RETURN ' é número';
EXCEPTION
WHEN OTHERS THEN
RETURN ' não é número';
END;
PROCEDURE prc_CONSULTA IS
BEGIN
FOR reg IN (SELECT NOTA_FINAL,
fun_E_NUMERO (NOTA_FINAL) E_NUMERO
FROM MENS_ERRO) LOOP
DBMS_OUTPUT.put_line(' O valor '||reg.NOTA_FINAL||' '||reg.E_NUMERO);
END LOOP;
END;
END;
SQL> select nome, nota_final from mens_erro;
NOME NOTA_FINAL
---------------------------------------- ----------
Alberto 9.00
Maria 8.00
Zuleica X
Claudia 7.50
SQL> BEGIN
2 PCK_CRISTIANO.prc_consulta;
3 END;
4 /
O valor 9.00 é número
O valor 8.00 é número
O valor X não é número
O valor 7.50 é número
PL/SQL procedure successfully completed.
This is why after a few years, by maintaining Oracle Base, I come across the situation of this topic again. Therefore, for the sake of honor, I decided to solve the problem definitely: -O. I did not test the solution with package because it would not fit for me in the query format I'm trying to run. Finally, the solution is simpler than I thought ... Correcting only the solution with "FUNCTION":
CREATE OR REPLACE FUNCTION "LYCEUM"."RETORNA_NUMERICO"
(VALOR_ENVIADO IN VARCHAR2)
RETURN NUMBER
IS VALOR_RETORNADO NUMBER;
begin
SELECT TO_NUMBER(VALOR_ENVIADO) INTO VALOR_RETORNADO FROM DUAL;
-- A linha dá para simplificar ainda mais, colocando simplesmente
-- VALOR_RETORNADO := TO_NUMBER(VALOR_ENVIADO);
RETURN(VALOR_RETORNADO);
EXCEPTION
WHEN OTHERS THEN
RETURN(-1);
end;
observe that I declared the variable "value_reno" only as a number, without scale.
DECLARE
N_NUMBER VARCHAR2(20) := '12345A';
BEGIN
IF (regexp_like(N_NUMBER, '^[[:digit:]]+$'))THEN
DBMS_OUTPUT.put_line('NUMBER');
ELSE
DBMS_OUTPUT.put_line('NOT');
END IF;
END;
WITH testdata AS
(SELECT 'abcdef' txt FROM DUAL UNION ALL
SELECT '1234567' txt FROM DUAL union all
SELECT '0' txt FROM DUAL union all
SELECT '123a4567x00' txt FROM DUAL union all
SELECT '123.4567,00' txt FROM DUAL
)
select txt,
translate(txt,'a1234567890','a') sem_numeros,
translate(txt,'0abcdefghijklonopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜ''á"çéíóúàèìòùâêîôûãõëü¿§¦¹²³£¢¬ªº°¨½¼¾Æ','0') so_numeros
from testdata
where txt = translate(txt,'0abcdefghijklonopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜ''á"çéíóúàèìòùâêîôûãõëü¿§¦¹²³£¢¬ªº°¨½¼¾Æ','0') /*so_numeros*/
The problem is to put all characters that you do not want to consider in the list.