'Invalid SQL Statement' when creating PL/SQL Procedure

Question

In an Oracle DB I have the following two tables:

Task (TaskID, ..., AuthorID)
Author (AuthorID, level)

where AuthorID in Task represents the author that wrote the task. Now I wrote the following procedure, that is supposed to update level-Attribute based on the number of tasks the author has written.

CREATE OR REPLACE PROCEDURE profil_level
IS
    CURSOR c1 IS SELECT AuthorID, COUNT(AuthorID) as Total FROM Task
       GROUP BY AuthorID;
    result c1%ROWTYPE;
BEGIN
    OPEN c1;
        LOOP
        FETCH c1 INTO result;
        EXIT WHEN c1%NOTFOUND;

      IF(result.Total = 2 OR result.Total = 3) THEN
        UPDATE TABLE Author SET level = 'advanced' 
            WHERE AuthorID = result.AuthorID;
      END IF;

      IF(result.Total >= 4) THEN
        UPDATE TABLE Author SET level= 'proficient'
            WHERE AuthorID = result.AuthorID;
      END IF;
    END LOOP;
  CLOSE c1;
END;

Trying to execute this statement, I get tons of errors like

invalid SQL Statement

e.g. for the line result c1%ROWTYPE or FETCH c1 into result. Additionally, I get the error

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ...

for the line BEGIN OPEN c1. I cannot figure out, what the problem is with my code. I've read tutorials from Oracle and on other sites, but in every example I've seen so far, they have used the exact same syntax. Do I need to change the 'delimiter' (like e.g. in MySQL)?

Thanks for your help!

If you are using SQL*Plus, you need to terminate the create table with a / (on a single line) — user330315
– user330315, Commented Nov 28, 2012 at 22:14
@a_horse_with_no_name I have tried that already, but it made no difference (just checked again). — kafman
– kafman, Commented Nov 28, 2012 at 22:19
@a_horse_with_no_name Did you really mean the create table statement or is it just a typo and you meant create procedure? edit: Yes, I am aware of that (but there are people who want me to specifically create a procedure ;-)) — kafman
– kafman, Commented Nov 28, 2012 at 22:23
Sorry, of course I meant create procedure. See the link I posted and click on build schema. — user330315
– user330315, Commented Nov 28, 2012 at 22:25
@a_horse_with_no_name Great link - thanks! I tried to build the schema with my entire code, but it did not work (check this). There must be something wrong with my code ... — kafman
– kafman, Commented Nov 28, 2012 at 22:39

kafman · Accepted Answer · 2012-11-28 22:54:25Z

2

I have just found the problem: the update statement within the if-clause was erroneous. It's supposed to be:

UPDATE Author SET level = 'advanced' 
            WHERE AuthorID = result.AuthorID;

instead of

UPDATE TABLE Author SET level = 'advanced' 
            WHERE AuthorID = result.AuthorID;

answered Nov 28, 2012 at 22:54

kafman

2,8602 gold badges31 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lv0gun9 Over a year ago

Good answer. :) I'm new about pl/sql, your question & answer really helpful to me.

David Aldridge Over a year ago

@lv0gun9 this is not a good coding pattern to learn from. Explicit cursors are to be avoided when possible. stackoverflow.com/questions/13658505/…

A B · Accepted Answer · 2012-11-29 22:16:32Z

1

Error #1: LEVEL is a reserved word in PL/SQL

I tried the rest of your code without the IFs and it looks good - so this will compile (see below). Yes, the IF's have UPDATE TABLE - the word TABLE should not be there (Error #2)

** Just don't use LEVEL as a column name. **

CREATE OR REPLACE PROCEDURE profil_level
IS
    CURSOR c1 IS SELECT FIELD1, COUNT(FIELD1) AS TOTAL FROM T1
      GROUP BY FIELD1;
    result c1%ROWTYPE;
BEGIN
    OPEN c1;
        LOOP
        FETCH c1 INTO result;
        EXIT WHEN c1%NOTFOUND;


        END LOOP;
    CLOSE c1;
END;

edited Nov 29, 2012 at 22:16

answered Nov 28, 2012 at 22:28

A B

4,1581 gold badge22 silver badges23 bronze badges

1 Comment

kafman Over a year ago

I tried to compile the procedure without the IF's, too, but it failed anyway.

Collectives™ on Stack Overflow

'Invalid SQL Statement' when creating PL/SQL Procedure

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related