Error(26,14): PLS-00103: Encountered the symbol <cursor> when expecting one of the following: := . ( @ % ;

Question

I am currently new to oracle with a MSSQL knowledge. currently, i am writing down few procedures to understand the syntax . I am not able to compile the following procedure. Not sure what is going wrong . I am constantly facing the error

Error(26,14): PLS-00103: Encountered the symbol "CRIT_CURSOR" when expecting one of the following:     := . ( @ % ; 

Below is the code:

create or replace 
PROCEDURE STUDY_ORA
AS
BEGIN  
  DECLARE 
    CRITVALID INTEGER;

    CURSOR CRIT_CURSOR IS
      SELECT ID FROM USERLIST;

  BEGIN
    OPEN CRIT_CURSOR;

    LOOP
      FETCH CRIT_CURSOR INTO CRITVALID;
      EXIT WHEN CRIT_CURSOR%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(CRITVALID);
    END LOOP;

    CLOSE CRIT_CURSOR  ;
  END;

  CURSOR CRIT_CURSOR IS
    SELECT ID FROM TXSLIST;

  BEGIN
    OPEN CRIT_CURSOR;

    LOOP
      FETCH CRIT_CURSOR INTO CRITVALID;
      EXIT WHEN CRIT_CURSOR%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(CRITVALID);
    END LOOP;

    CLOSE CRIT_CURSOR ;
  END; 
END;

I would appreciate if someone can provide some insights . thanks.

Answer 1

To expand on @AlexPoole's suggestion to use implicit cursors, I suggest rewriting your procedure as:

CREATE OR REPLACE PROCEDURE STUDY_ORA AS
BEGIN  
  FOR row1 IN (SELECT ID FROM USERLIST)
  LOOP
    DBMS_OUTPUT.PUT_LINE(row1.ID);
  END LOOP;

  FOR row2 IN (SELECT ID FROM TXSLIST)
  LOOP
    DBMS_OUTPUT.PUT_LINE(row2.ID);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error in STUDY_ORA: ' || SQLCODE || ' : ' || SQLERRM);
    RAISE;
END STUDY_ORA;

I think this version is not only shorter but much clearer. I also added an exception block so that in the case that an exception is raised you'll get a reasonable report of the error which occurred.

Answer 2

your problem is that the second cursor is not declared within a DECLARE block, you will also need to redeclare the variable you are using (CRITVALID)

create or replace 
PROCEDURE STUDY_ORA
AS
BEGIN  
   DECLARE 
   CRITVALID INTEGER;

CURSOR CRIT_CURSOR IS
   SELECT ID FROM USERLIST;

   BEGIN
      OPEN CRIT_CURSOR;
    LOOP
    FETCH CRIT_CURSOR INTO CRITVALID;
    EXIT WHEN CRIT_CURSOR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(CRITVALID);
    END LOOP;
      CLOSE CRIT_CURSOR  ;
    END;

declare
CRITVALID INTEGER;
      CURSOR CRIT_CURSOR IS
      SELECT ID FROM TXSLIST; 

      BEGIN
      OPEN CRIT_CURSOR;
    LOOP
    FETCH CRIT_CURSOR INTO CRITVALID;
    EXIT WHEN CRIT_CURSOR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(CRITVALID);
    END LOOP;
      CLOSE CRIT_CURSOR ;
    END; 

END;

Answer 3

You're missing a DECLARE before your second declaration of that cursor:

..
END;

DECLARE
  CURSOR CRIT_CURSOR IS
  SELECT ID FROM TXSLIST;
BEGIN
...

And CRITVALID is only in scope for your first inner block; you either need to redeclare that too, or move its declaration to the procedure level (i.e. before the very first BEGIN for the procedure itself).

Using implicit cursors would be simpler than having separate blocks and repeated (scoped) names. It's unusual (I think) to have nested blocks unless you need to capture an exception. They don't really add anything to flow here, apart from allowing you to redefine the cursor.