sqloracle-database

Keep getting error: PLS-00103: Encountered the symbol "CREATE"


I get the following error in Oracle SQL Developer

PLS-00103: Encountered the symbol "CREATE"

CREATE OR REPLACE PROCEDURE EnrollStudent(
     p_StudentID IN INT,  
    p_CourseID IN INT   
) IS

    v_student_skill INT;
    v_course_skill INT;
    v_enrolled_count INT;
    
BEGIN
    SELECT skill INTO v_student_skill
    FROM A1_Student
    WHERE StudentID = p_StudentID;

    SELECT skill INTO v_course_skill
    FROM A1_Course
    WHERE CourseID = p_CourseID;

    IF v_student_skill < v_course_skill THEN
        RAISE_APPLICATION_ERROR(-20001, 'Student skill level is not sufficient for this course.');
    END IF;

    SELECT COUNT(*) INTO v_enrolled_count
    FROM A1_Enrollment
    WHERE StudentID = p_StudentID AND CourseID = p_CourseID;

    IF v_enrolled_count > 0 THEN
        RAISE_APPLICATION_ERROR(-20002, 'Student is already enrolled in this course.');
    END IF;

    INSERT INTO A1_Enrollment (StudentID, CourseID)
    VALUES (p_StudentID, p_CourseID);

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Student successfully enrolled in the course.');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Student or Course not found.');
    WHEN OTHERS THEN
        
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END EnrollStudent;
/

Not entirely sure why this error is popping up, any help would be greatly appreciated.


Solution

  • I copied your code into a sql developer worksheet, ran it and...

    Procedure ENROLLSTUDENT compiled
    
    LINE/COL  ERROR
    --------- -------------------------------------------------------------
    11/5      PL/SQL: SQL Statement ignored
    12/10     PL/SQL: ORA-00942: table or view does not exist
    15/5      PL/SQL: SQL Statement ignored
    16/10     PL/SQL: ORA-00942: table or view does not exist
    23/5      PL/SQL: SQL Statement ignored
    24/10     PL/SQL: ORA-00942: table or view does not exist
    31/5      PL/SQL: SQL Statement ignored
    31/17     PL/SQL: ORA-00942: table or view does not exist
    Errors: check compiler log
    
    

    The procedure compiled. With errors, but it compiled. The errors are there because I don't have any of those tables.

    Then I put the following code before the code I had pasted. Note that this is an incomplete anonymoust pl/sql code block. Why is it incomplete ? It's missing a trailing "/".

    BEGIN
      NULL;
    END;
    

    and ran the whole thing again. The error now is ...

    ...
    END EnrollStudent;
    Error report -
    ORA-06550: line 5, column 1:
    PLS-00103: Encountered the symbol "CREATE" 
    06550. 00000 -  "line %s, column %s:\n%s"
    *Cause:    Usually a PL/SQL compilation error.
    *Action:
    ...
    

    So, most probably you have some other, incomplete, statement before the CREATE OR REPLACE