PLS-00103: Encountered the symbol "end-of-file" in simple update block

Question

The following Oracle statement:

 DECLARE ID NUMBER;
 BEGIN
  UPDATE myusername.terrainMap 
  SET playerID = :playerID,tileLayout = :tileLayout 
  WHERE ID = :ID
 END;

Gives me the following error:

ORA-06550: line 6, column 15:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 3, column 19:
PL/SQL: SQL Statement ignored
ORA-06550: line 6, column 18:
PLS-00103: Encountered the symbol "end-of-file" 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>

I am pretty much at a loss. This appears to be a rather simple statement. If it helps any, I had a similar statement that performed an INSERT which used to work, but today has been giving me the same message.

Are you running this in SQLPlus? – MJB Jun 16 '10 at 19:15 — MJB, Jun 16 '10 at 19:15

score 6 · Accepted Answer · answered Jun 16 '10 at 19:16

6

Add a semi-colon after where id=:id

answered Jun 16 '10 at 19:16

MJB

7,639
2
31
41

@rageingnonsense : no problem. I was wondering if you copy/pasted or if the missing semi-colon was an artifact of retyping the code. – MJB Jun 16 '10 at 19:38

score 3 · Answer 2 · answered Jun 17 '10 at 01:28

You have a number of problems here:

Missing semi-colon (as MJB saw)
:ID refers to an in-bound variable, so your local declaration (DECLARE ID NUMBER;) is not being used.
You're using a variable name which (apparently) is the same name as a colum in your table. If you try to use your local ID variable, the query will still not use it unless you use a block label.

That said, it looks like you're sending ID in as a bind variable anyway, so it's more likely that you should just remove the declaration from the block.

That's why I asked whether this was in SQLPlus. I could not understand how his bind vars were being used. And I think perhaps this is **not** really what he is running -- it is a test version of code he thought should run. — MJB, Jun 17 '10 at 12:51

score 0 · Answer 3 · edited Dec 05 '14 at 22:12

In addition to the previous you have to prevent spaces between equal operation ,:,and the value like this:

SQL> BEGIN  
2    IF x > y THEN high := x; END IF;  -- correct

3    IF x > y THEN high := x; ENDIF;   -- incorrect

4  END;

5  /

END;

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

visit the website to read more.... https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/fundamentals.htm#LNPLS002

PLS-00103: Encountered the symbol "end-of-file" in simple update block

3 Answers3