Hey, I'm having a problem creating this function. It needs to get the average of grades of a row. Also, it needs to archive any error that may occur on another table called ‘ERRORES’. Problem is, when I try to use the function, I get this errors:
“Error at Command Line : 2 Column : 5
Error report -
SQL Error: ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "C##GERA.FN_PROMEDIO", line 23
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "C##GERA.FN_PROMEDIO", line 8
14551. 00000 - "cannot perform a DML operation inside a query "
*Cause: DML operation like insert, update, delete or select-for-update
cannot be performed inside a query or under a PDML slave.
*Action: Ensure that the offending DML operation is not performed or
use an autonomous transaction to perform the DML operation within
the query or PDML slave.”
This is the function:
CREATE OR REPLACE FUNCTION FN_PROMEDIO(cod_asig nota_alumno.cod_asignatura%TYPE)
RETURN NUMBER IS
promedio nota_alumno.nota1%TYPE := 0;
v_error NUMBER;
v_errorm VARCHAR(500);
BEGIN
SELECT
ROUND((nota1 + nota2 + nota3 + nota4 + nota5) / 5, 1)
INTO
promedio
FROM
nota_alumno
WHERE
cod_asignatura = cod_asig;
RETURN
promedio;
EXCEPTION
WHEN OTHERS THEN
v_error := SQLCODE;
v_errorm := SQLERRM;
INSERT INTO ERRORES (
id_error,
subprograma_error,
descripcion_error
) VALUES(
SEQ_ERRORES.nextVal,
'Error en función "FN_PROMEDIO"',
TO_CHAR(v_error) || ' - ' || v_errorm);
RETURN NULL;
END;
What should I do?