Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2012
    Posts
    6

    Unanswered: PL/SQL - Using Cursors (PLS-00103)

    Hi all,

    I'm trying to run the above code and I am getting an error where it's telling me it expects something else except ";". I can't seem to get what the problem is.

    Code:
    DECLARE
        CURSOR bookCursor IS
        SELECT btName, bc.Isbn, pubName, dateDestroyed
          FROM booktitle bt
          JOIN publisher p
            ON bt.pubId = p.pubId 
          JOIN bookcopy bc
            ON bt.Isbn = bc.Isbn 
         WHERE datedestroyed IS NULL
        ;
        bookCursorRec bookcursor%ROWTYPE;
    BEGIN
        OPEN bookCursor;
        LOOP
            FETCH bookCursor INTO bookCursorRec;
            WHILE bookCursor%found loop
            dbms_output.put_line( 'ISBN: ' ||bookCursorRec.isbn
                                  || ' - Book Name: ' || bookcursorrec.btname
                                  || ' - Publisher: ' || bookcursorrec.pubname );
    	    FETCH bookCursor INTO bookCursorRec;
        END LOOP;
        CLOSE bookCursor;
    END;
    /
    Code:
    ERROR at line 23:
    ORA-06550: line 23, column 4:
    PLS-00103: Encountered the symbol ";" when expecting one of the following:
    loop
    Can anyone help me please?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    code contains 2 LOOP statements but only 1 END LOOP;
    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
    Jan 2012
    Posts
    6
    Where? There's only one loop statement as far as I can see

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
        LOOP
            FETCH bookCursor INTO bookCursorRec;
            WHILE bookCursor%found loop
    I see "LOOP" twice above.
    does your browser support SEARCH function?
    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
    Jan 2012
    Posts
    6
    Code:
    DECLARE
        CURSOR bookCursor IS
        SELECT btName, bc.Isbn, pubName, dateDestroyed
          FROM booktitle bt
          JOIN publisher p
            ON bt.pubId = p.pubId 
          JOIN bookcopy bc
            ON bt.Isbn = bc.Isbn 
         WHERE datedestroyed IS NULL
        ;
        bookCursorRec bookcursor%ROWTYPE;
    BEGIN
        OPEN bookCursor;
        LOOP
            FETCH bookCursor INTO bookCursorRec;
            WHILE bookCursor%found loop
            dbms_output.put_line( 'ISBN: ' ||bookCursorRec.isbn
                                  || ' - Book Name: ' || bookcursorrec.btname
                                  || ' - Publisher: ' || bookcursorrec.pubname );
    	    FETCH bookCursor INTO bookCursorRec;
    		END loop;
        END LOOP;
    	
        CLOSE bookCursor;
    END;
    /
    Ok so I added another 'END loop;" and Oracle remains processing without giving me an output

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Ok so I added another 'END loop;" and Oracle remains processing without giving me an output
    Oracle responded correctly.

    Realize that we don't have your tables & don't have your data.
    Therefore we can't run, test, or improve posted code.

    post results from SQL below
    Code:
        SELECT COUNT(*)
          FROM booktitle bt
          JOIN publisher p
            ON bt.pubId = p.pubId 
          JOIN bookcopy bc
            ON bt.Isbn = bc.Isbn 
         WHERE datedestroyed IS NULL
        ;
    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.

  7. #7
    Join Date
    Jan 2012
    Posts
    6
    So the output is

    Code:
      COUNT(*)
    ----------
            29
    I have a couple of tables as below:
    BookTitle contains ISBN(PK), btName, datePublished, pubId*, ageLower, ageUpper, value.
    Borrower contains borID(PK), borName, borAddress and borMaxbook
    BOokCopy cvontains bcID(PK), ISBN*, dateAcquired, dateDestroyed
    oan contains borID(PH)*, bcID(OK)*, dateOut(FK), dateDue and dateBack
    Publisher contains PubId(PK), pubName and pubAddress
    Publisher cotains PubID(8), pubName, pubAddress
    Authoer contains authorID(PK) and authorName
    Authorship contains authorId*, and ISBN*

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    if you modify the code as below

    Code:
    	
        CLOSE bookCursor;
        DBMS_OUTPUT.PUT_LINE('The END!');
    END;
    /
    what is the result?
    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.

  9. #9
    Join Date
    Jan 2012
    Posts
    6
    Code:
    ERROR at line 26:
    ORA-06550: line 26, column 4:
    PLS-00103: Encountered the symbol ";" when expecting one of the following:
    loop

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Fix the syntax error
    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.

  11. #11
    Join Date
    Jan 2012
    Posts
    6
    Hi I fixed it.. just removed the LOOP !

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm not sure whether you understand what you did ... you were having a loop within a loop, and it seems that there's no reason to do that.

    Here's how you code should have looked like (I simplified it a little bit):
    Code:
    declare
      cursor bookcursor is
        select btname, bc.isbn, pubname, datedestroyed
    	  from booktitle bt
          ...;
      bookcursorrec bookcursor%rowtype;
    begin
      open bookcursor;
      loop
        fetch bookcursor into bookcursorrec;
    	exit when bookcursor%notfound;
    	
    	dbms_output.put_line('ISBN: ' || bookcursorrec.isbn);	
      end loop;
      close bookcursor;
    end;
    It can be rewritten so that you don't have to take care about cursor manipulation (which is manual in your case) - use cursor FOR loop:
    Code:
    begin
      for cur_r in (select btname, bc.isbn, pubname, datedestroyed
                    from booktitle bt
                    ...
                   )
      loop
        dbms_output.put_line('ISBN: ' || bookcursorrec.isbn);	 
      end loop;
    end;
    If you compare both codes (which will produce the same result), you'll notice that cursor FOR loop is much simpler as you don't have to
    - declare a cursor variable
    - open a cursor
    - fetch from it into a cursor variable
    - take care about exiting a loop
    - close a cursor

    Have a look at PL/SQL User's Guide and Reference and Application Developer's Guide - Fundamentals - quite an interesting reading for you.

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
  •