PL/SQL - Using Cursors (PLS-00103)

blivori · #1 (**permalink**) 01-12-12, 14:17

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?

anacedent · #2 (**permalink**) 01-12-12, 14:21

code contains 2 LOOP statements but only 1 END LOOP;

blivori · #3 (**permalink**) 01-12-12, 14:26

Where? There's only one loop statement as far as I can see

anacedent · #4 (**permalink**) 01-12-12, 14:30

Code:

    LOOP
        FETCH bookCursor INTO bookCursorRec;
        WHILE bookCursor%found loop

I see "LOOP" twice above.
does your browser support SEARCH function?

blivori · #5 (**permalink**) 01-12-12, 14:37

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

anacedent · #6 (**permalink**) 01-12-12, 14:49

>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
    ;

blivori · #7 (**permalink**) 01-12-12, 14:52

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*

anacedent · #8 (**permalink**) 01-12-12, 15:11

if you modify the code as below

Code:

	
    CLOSE bookCursor;
    DBMS_OUTPUT.PUT_LINE('The END!');
END;
/

what is the result?

blivori · #9 (**permalink**) 01-12-12, 15:22

Code:

ERROR at line 26:
ORA-06550: line 26, column 4:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop

anacedent · #10 (**permalink**) 01-12-12, 15:29

Fix the syntax error

blivori · #11 (**permalink**) 01-12-12, 15:34

Hi I fixed it.. just removed the LOOP !

Littlefoot · #12 (**permalink**) 01-13-12, 01:24

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech