CODEWITHSUNDEEP
sqldatabaseoracle-databasestored-proceduresplsql
user3610096
user3610096

Reputation: 23

ORACLE - error PLS-00103 in a procedure

I wrote this simple procedure following ORACLE Documentation in order to practice with PL/SQL: 

create procedure BADGE_PROCEDURE (badges NUMBER) AS  
  loops NUMBER;  
BEGIN  
  loops := badges;  
  loops := loops - 1;  
  BEGIN LOOP  
    insert into BADGE values (BADGE_ID_SEQUENCE.nextval, 'prova', 'prova', '01-JAN-01');       
    loops := loops - 1;  
    EXIT WHEN loops < 0;  
  END LOOP;  
END;  
/

This is my BADGE table:

CREATE TABLE BADGE  
(badge_id NUMBER PRIMARY KEY,   
name VARCHAR(20),   
surname VARCHAR(20) NOT NULL,   
birthday DATE);

Finally this is my sequence: 

CREATE SEQUENCE BADGE_ID_SEQUENCE   
start with 1      
increment by 1    
nocache   
nocycle;

However, the procedure compiles with the following error:

LINE/COL ERROR
-------- -----------------------------------------------------------------
11/5     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 delimited-identifier>
        <a bind variable> << continue close current delete fetch lock
        insert open rollback savepoint set sql execute commit forall
        merge pipe purge

What am I doing wrong?

Upvotes: 1

Views: 2209

Answers (2)

MT0
MT0

Reputation: 168588

The syntax for a loop is LOOP ... END LOOP; without the BEGIN.

create procedure BADGE_PROCEDURE (badges NUMBER) AS  
  loops NUMBER;  
BEGIN  
  loops := badges;  
  loops := loops - 1;  
  LOOP  
    insert into BADGE values (BADGE_ID_SEQUENCE.nextval, 'prova', 'prova', DATE '2001-01-01');       
    loops := loops - 1;  
    EXIT WHEN loops < 0;  
  END LOOP;  
END;  
/

However, an even simpler version would be:

create procedure BADGE_PROCEDURE (badges NUMBER) AS  
BEGIN  
  FOR loops IN 1 .. badges LOOP  
    insert into BADGE values (BADGE_ID_SEQUENCE.nextval, 'prova', 'prova', DATE '2001-01-01');       
  END LOOP;  
END;  
/

Upvotes: 3

cableload
cableload

Reputation: 4385

Your loop syntax is wrong..

Just do 

             LOOP  
                     insert into BADGE values (BADGE_ID_SEQUENCE.nextval, 'prova', 'prova', '01-JAN-01');       
                     loops := loops - 1;  
                     EXIT WHEN loops < 0;  
             END LOOP;

Here is an example from oracle docs.

Upvotes: 2

Related Questions