error:00103 for plsql procedure

1
votes

I am getting error such as:

Error(5,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current cursor delete exists prior external language The symbol "begin" was substituted for "DECLARE" to continue.

Error(5,1): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current cursor delete exists prior external language The symbol "begin" was substituted for "DECLARE" to continue.

and my code is:

CREATE OR REPLACE PROCEDURE procExplicitCursorAccountSlct
AS
DECLARE 
  CURSOR C1 IS SELECT * FROM ACCOUNT;
BEGIN
  OPEN C1;
  FOR i in C1 LOOP
  FETCH C1 INTO ID,ACCOUNTTYPE,BALANCE;
  EXIT WHEN C1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(ID||'-'||ACCOUNTTYPE||'-'||BALANCE);
    DBMS_OUTPUT.PUT_LINE(C1%ROWCOUNT);
  END LOOP;  
  CLOSE C1;    
END;
2
plsql
Just remove DECLARE keyword. Refer to the syntax of CREATE PROCEDURE: docs.oracle.com/database/121/LNPLS/… Threre is declre_section directtly after IS/AS keyword, but this section doesnt containt DECLARE keyword. This keyword is used only in anonymous blocks and in the triggers. – krokodilko

2 Answers

1
votes

Try this and see inline comments for modifications. Hope it helps.

CREATE OR REPLACE
PROCEDURE procExplicitCursorAccountSlct
AS
  CURSOR C1
  IS
    SELECT
      *
    FROM
      ACCOUNT;
--Variabke declaration was missing
  ID          NUMBER;
  ACCOUNTTYPE VARCHAR2(100);
  BALANCE     NUMBER;
  --Variabke declaration was missing
BEGIN
  OPEN C1;
  LOOP
    FETCH
      C1
    INTO
      ID,
      ACCOUNTTYPE,
      BALANCE;
    EXIT
  WHEN C1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(ID||'-'||ACCOUNTTYPE||'-'||BALANCE);
    DBMS_OUTPUT.PUT_LINE(C1%ROWCOUNT);
  END LOOP;
  CLOSE C1;
END;
0
votes

You could make it way more simple with implicit loop (and removing DECLARE too):

CREATE OR REPLACE PROCEDURE procExplicitCursorAccountSlct
AS
  CURSOR C1 IS SELECT * FROM ACCOUNT;
BEGIN
  FOR i in C1 LOOP
    DBMS_OUTPUT.PUT_LINE(i.ID||'-'||i.ACCOUNTTYPE||'-'||i.BALANCE);
    DBMS_OUTPUT.PUT_LINE(C1%ROWCOUNT);
  END LOOP;  
END;