I'm trying to create this trigger, but get errors regarding the only cursor I've declared. The compiler logs 2 errors, and I can't seem to figure out where my code is flawed. Any ideas? The errors are:

Error(29,5): PLS-00103: Encountered the symbol "FETCH" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set specification> <an alternati

Error(35,3): PLS-00103: Encountered the symbol ")" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge






My code is written as follows:

CREATE OR REPLACE TRIGGER enr_same_course BEFORE
INSERT
ON enrollment
REFERENCING new as new
FOR EACH ROW

DECLARE
v_course_id number(8);

CURSOR mycursor (course_id number) IS
SELECT section_id
FROM SECTION
WHERE course_no = course_id;


e_same_course EXCEPTION;
v_section_id number;
TYPE all_sections is table of number;
a_all_sections all_sections := (:new.section_id);
idx number;
idx2 number;
new_section_id number := :new.section_id;

BEGIN

SELECT s.course_no INTO v_course_id
FROM section s
WHERE new_section_id = s.section_id;

open mycursor (v_course_id);
idx2 := 1;

loop
(
FETCH mycursor INTO v_section_id;
EXIT WHEN mycursor%NOTFOUND;



a_all_sections(idx2) := v_section_id;
)
end loop;
CLOSE mycursor;

idx := 1;
loop
(
if v_section_id != new_section_id then
raise e_same_course;
else
v_section_id := a_all_sections(idx);
end if;
idx := idx + 1;
)
end loop;

EXCEPTION

WHEN e_same_course THEN
dbms_output.put_line('Student cannot be enrolled in the same course twice.');
WHEN OTHERS THEN
IF mycursor%ISOPEN THEN
CLOSE mycursor;
END enr_same_course;


Thanks for any help, it's much appreciated

Please realize that we don't have your tables & we don't have your data.
Therefore we can't run, test or improve your posted SQL.


>Error(29,5):
>Error(35,3):

Realize the leftmost number is the line number of the ERROR & the rightmost number is column or position counting from the left.

Yes, I get that you can't test the code. However, I was wondering if there's just something wrong with my syntax. I'm new to learning Oracle and PL/SQL, and I've been having these same types of errors whenever I try to create and fetch a cursor. I've looked at the PL/SQL documentation and it looks as if I've set it up correctly but don't know what's causing the errors.

The compiler doesn't seem to like when I use the FETCH command, and for some reason doesn't like parsing in a parameter.

>The compiler doesn't seem to like when I use the FETCH command, and for some reason doesn't like parsing in a parameter.

Because you are doing it wrong.

Ask Tom Home contains many fine coding examples

Below show proper PL/SQL syntax
Contents

There are a couple of issues:

1.) LOOP is a control structure, not a procedure. So enclosing the following statements in parenthesis is not syntactically correct.

2.) Your syntax for the exception is sufficiant for the statement
"raise e_same_course"
somewhere in the code, but not for the exception handler. You have to add

PRAGMA EXCEPTION_INIT(e_same_course,<your userdefined ORA errornumber>);

There might be other problems (I just did a quick glance over the code), but you should be able to find syntax errors by yourself


3.) looping over a cursor is very slow (because you force context switches between the SQL and the PL/SQL machine each time the fetch() is executed).
Consider BULK COLLECT into a pl/sql - table and looping through it. It saves you 2*N-1 context switches where N is the number of rows.