PLS-00103: Encountered the symbol "FETCH" when expecting one of the followingg

Question

Created a procedure as below :

 create or replace
  PROCEDURE TEST AS 
  BEGIN
  DECLARE
     c_service process_state.service%type;
     c_tr_source process_state.tr_source%type;

     CURSOR c_process_state is
        SELECT service, tr_source FROM process_state;

          OPEN c_process_state;
           LOOP
           FETCH c_process_state into c_service, c_tr_source;
             insert into process_state_archive values (c_service, c_tr_source);
             commit;
           EXIT WHEN c_process_state%notfound;
           END LOOP;   
     CLOSE c_process_state;
   END TEST;

After compiling i ran into some errors:

Error(33,4): PLS-00103: Encountered the symbol "FETCH" when expecting one of the following: constant exception table long double ref char time timestamp interval date

Error(44,4): PLS-00103: Encountered the symbol "CLOSE" when expecting one of the following: end not pragma final instantiable volgorde overriding static member constructor map

Can anyone one please explain the problem? I am beginner and learning SQL.

Dmitry Nikiforov Dmitry Nikiforov · Accepted Answer · 2014-03-06T15:52:16

1) You should declare cursor before BEGIN but not after and EXIST WHEN should be immediately after FETCH

create or replace
  PROCEDURE TEST AS 

     c_service process_state.service%type;
     c_tr_source process_state.tr_source%type;
CURSOR c_process_state is
        SELECT service, tr_source FROM process_state;
  BEGIN

          OPEN c_process_state;
           LOOP
           FETCH c_process_state into c_service, c_tr_source;
           EXIT WHEN c_process_state%notfound;
             insert into process_state_archive values (c_service, c_tr_source);
             commit;

           END LOOP;   
     CLOSE c_process_state;
   END TEST;

2) You task seems can be solved in one statement instead of a lot of code with cursor:

INSERT INTO process_state_archive select service, tr_source FROM process_state;

PLS-00103: Encountered the symbol "FETCH" when expecting one of the followingg

1 Answers