Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Unanswered: Cursor Fetching Error Help (Oracle)

    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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2010
    Posts
    2
    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.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    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.
    Last edited by magicwand; 03-28-10 at 15:54.
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •