PL-SQL I'm trying to use a variable value into an Insert Into - ERROR ORA-00984

Question

I have a package that contains the following procedure: `

PROCEDURE PRC_DO_ISCRIZIONE( P_ID_STUD IN NUMBER, P_ID_CORSO IN NUMBER)
IS
    V_ID_CORSO NUMBER := NULL;
    V_ID_STUD NUMBER := NULL;
    V_NEXT_ID NUMBER := NULL;
    EX_NO_STUD EXCEPTION;
    EX_NO_CORSO EXCEPTION;
    CURSOR C_LISTA_CORSI IS ( SELECT ID FROM CORSO);
    CURSOR C_LISTA_STUD IS ( SELECT ID FROM STUDENTE);

BEGIN
    SELECT MAX(ID) +1
    INTO V_NEXT_ID
    FROM ISCRIZIONE;
    
    FOR S IN C_LISTA_STUD
    LOOP
        IF P_ID_STUD != S.ID
            THEN RAISE EX_NO_STUD;
        END IF;
    END LOOP;
    
    FOR C IN C_LISTA_CORSI
    LOOP
        IF P_ID_CORSO != C.ID
            THEN RAISE EX_NO_CORSO;
        END IF;
    END LOOP;
    
    SELECT ID 
    INTO V_ID_CORSO
    FROM CORSO 
    WHERE ID = P_ID_CORSO;
    
    SELECT ID 
    INTO V_ID_STUD
    FROM STUDENTE 
    WHERE ID = P_ID_STUD;
    
    INSERT INTO ISCRIZIONE(ID, ID_CORSO, ID_STUDENTE, DATA)
    VALUES ( V_NEXT_ID , V_ID_CORSO, V_ID_STUDENTE, SYSDATE);
    
EXCEPTION 
    WHEN EX_NO_STUD
        THEN DBMS_OUTPUT.PUT_LINE('NESSUNO STUDENTE CORRISPONDE ALL''ID INSERITO');
        RETURN;
    WHEN EX_NO_CORSO
        THEN DBMS_OUTPUT.PUT_LINE('NESSUN CORSO CORRISPONDENTE ALL''ID INSERITO');
        RETURN;
        
END PRC_DO_ISCRIZIONE;

` But when I launch the create package body I get the error ora-00984: column not allowed in this case

the goal is that given a student ID and a course ID,if they exists in their relative tables, the procedure adds to the ENROLLMENT(Iscrizione) table a row containing student id, course id and date

Answer 1

The source of your error appears to be the identifier V_ID_STUDENTE in the VALUES clause of your INSERT statement.

You don't have a local variable named V_ID_STUDENTE, but you do have one named V_ID_STUD. Try replacing V_ID_STUDENTE with V_ID_STUD.