CODEWITHSUNDEEP
databaseoracle-databasesymbolsprocedure
VGe0rge
VGe0rge

Reputation: 1100

Oracle Stored Procedure "Encountered the symbol... " error

After a looong research i have decided i have no idea what causes the following problem! I want to create a stored procedure that will increase the value of a column if that satisfied a statement.I can run the procedure as a anonymous block but i cant run it as a stored procedure!

Here is the stored procedure code:

1 create or replace
2 Procedure RaiseSalary
3 Is
4 Salary number;
5 Cursor RaiseCursor
6 Is
7 Select Salary_A From Person Where Salary_A<400 for update of Salary_A;
8 Begin 
9 Open RaiseCursor;
10    Loop
11 Fetch RaiseCursor Into Salary;
12    Exit When RaiseCursor%Notfound;
13 Update Person Set Salary_A=(Salary + 200) Where Current Of RaiseCursor;
14    End Loop;
15 Close RaiseCursor;
16 Commit;
17 End;

I am getting an error at line 3 that says "Encountered the symbol "SALARY" when expecting one of the following:...." and i am getting the same error at line 5 at the "s" letter of the word Cursor ,but at this time it says ";" instead of "Salary". The compiler log shows "insufficient privileges" but i can't find a reasonable explanation for this. I have tried to run the code as an anonymous block like this:

 Declare
 Salary number;
 Cursor RaiseCursor
 Is
  Select Salary_A From Person Where Salary_A<400 for update of Salary_A;
  Begin
  Open RaiseCursor;
    Loop
  Fetch RaiseCursorInto Salary ;
    Exit When RaiseCursor%Notfound;
  Update Person Set Salary_A =(Salary + 200) Where Current Of RaiseCursor;
    End Loop;
  Close RaiseCursor;
  Commit;
  End;
  .
  run;

And works great!!!I am sure that i have the privileges to write stored procedures because i have created some successfully!
Am i doing something so wrong? I can't figure out what causes the problem, if you have any suggestions i would really appreciate that:)

EDIT: For some reason my procedure got wrong Schema that's why i had that problem! I only dropped the Procedure and create it again and it fixed!!!! Thanks for your time anyway!

Upvotes: 2

Views: 935

Answers (1)

eaolson
eaolson

Reputation: 15090

Let me just point out this would be vastly more efficient if you did it as:

UPDATE Person Set Salary_A=(Salary_A + 200)
 Where Salary_A<400

No cursor, no looping. It would also be a lot simpler.

Upvotes: 6

Related Questions