the function needs to get the average from a single row, which are the grades from a student. Also, it needs to handle the errors, insert them on another table called ERRORES to archive them. I want to use the SQLCODE and SQLERRM, but it doesn't let me. It gives me the following errors:

Error(22,9): PL/SQL: SQL Statement ignored
Error(23,42): PL/SQL: ORA-00984: column not allowed here

The table ERRORES was created with this parameters:

CREATE TABLE ERRORES
(id_error NUMBER(10) NOT NULL,
 subprograma_error VARCHAR2(50) NOT NULL,
 descripcion_error VARCHAR2(500),
 PRIMARY KEY (id_error));

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;

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
        INSERT INTO ERRORES
        VALUES(
            SEQ_ERRORES.NEXTVAL, 
            'Error en función "FN_PROMEDIO', 
            TO_CHAR(SQLCODE) || ' - ' || SQLERRM);

END;

What should I do? Are the error messages to long for ERRORES?

the function needs to get the average from a single row, which are the grades from a student. Also, it needs to handle the errors, insert them on another table called ERRORES to archive them. I want to use the SQLCODE and SQLERRM, but it doesn't let me. It gives me the following errors:

Error(22,9): PL/SQL: SQL Statement ignored
Error(23,42): PL/SQL: ORA-00984: column not allowed here

The table ERRORES was created with this parameters:

CREATE TABLE ERRORES
(id_error NUMBER(10) NOT NULL,
 subprograma_error VARCHAR2(50) NOT NULL,
 descripcion_error VARCHAR2(500),
 PRIMARY KEY (id_error));

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;

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
        INSERT INTO ERRORES
        VALUES(
            SEQ_ERRORES.NEXTVAL, 
            'Error en función "FN_PROMEDIO', 
            TO_CHAR(SQLCODE) || ' - ' || SQLERRM);

END;

What should I do? Are the error messages to long for ERRORES?

• sql
• oracle-database
• plsql

1 Answer 1

In the SQLCODE documentation it states:

A SQL statement cannot invoke SQLCODE.

And the SQLERRM documentation states similar:

A SQL statement cannot invoke SQLERRM.

Instead, assign SQLCODE and SQLERRM to a variable and use the variable in the SQL statement, rather than trying to directly use them:

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_errm  VARCHAR2(1000);
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_errm  := 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_errm
    );
    RETURN NULL;
END;
/

However, you may want to move the logging into an autonomous transaction and re-raise the exception:

CREATE PROCEDURE log_error(
  i_error NUMBER,
  i_errm  VARCHAR2
)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO ERRORES (
    id_error,
    subprograma_error,
    descripcion_error
  ) VALUES(
    SEQ_ERRORES.NEXTVAL, 
    'Error en función "FN_PROMEDIO', 
    TO_CHAR(i_error) || ' - ' || i_errm
  );
  COMMIT;
END;
/

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_errm  VARCHAR2(1000);
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
    log_error(SQLCODE, SQLERRM);
    RAISE;
END;
/

fiddle