Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2007
    Posts
    9

    Unanswered: compilation error

    Dear all,
    i have created a stored procedure in Oracle 9i by using the following code.It's aim is to take designation as argument and calculates the avg salary and then inserts the result into another table.

    create or replace procedure my_proc( desig IN varchar)
    as
    declare
    cursor s_cursor is
    select avg(salary) from Teachers where designation=desig;
    s_value s_cursor%ROWTYPE;
    begin
    open s_cursor;
    fetch s_cursor into s_value;
    insert into Salary values(s_value);
    close s_cursor;
    end;


    when i'm executing this code it says the procedure created with compilation errors.
    but the procedure does't working.

    Please help me..

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    How many columns are in the Salary table? If it's more than 1 you need to specify the column in which you want to insert s_value.

  3. #3
    Join Date
    Mar 2007
    Posts
    9
    Dear Ivon,
    i'm calculating the average and it is stored into the cursor. So do I need to mention the column name using (.) operator as i have only one column (i.e the avg(salary)) ??
    Plz explain...

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    No, I mean the table you are inserting the value into. How many columns does that have?

  5. #5
    Join Date
    Mar 2007
    Posts
    9
    the table Salary has only one column.

  6. #6
    Join Date
    Nov 2002
    Posts
    272
    Then I don't see any obvious errors I'm afraid...

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why are you using a cursor?
    1. Declare a variable
    2. SET your variable = select avg(salary) from Teachers where designation=desig
      (should there be quotes around the desig?)
    3. Then run an insert statement using your variable value.
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Wrong! The right question is "why PL/SQL?" I'd rather put it as
    Code:
    INSERT INTO salary 
    SELECT AVG(salary) 
      FROM teachers
      WHERE designation = &desig;

  9. #9
    Join Date
    Mar 2007
    Posts
    623
    It is nice to have this discussion, however the reason of the error is still not clear.

    ashek02: Whenever you get this message, call SHOW ERRORS, if you are in SQL*Plus; if not, query the table USER_ERRORS for NAME=<your_procedure_name>.

    Without that information I will just guess using CREATE PROCEDURE command syntax as stated in SQL Reference Guide, that the DECLARE keyword should not be used at all.

    Definitely Littlefood's solution is better than the original one with cursors.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    LittleFoots suggestion is far superior to my own
    But just to throw another coin in the fountain - why not create this as a view? It may be a bit of overkill but the total will update live with data changes.
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Personally, I wouldn't store any sum, avg, min etc. anywhere (unless we are discussing Data Warehousing). A view, as you have suggested, would be a much better idea than @OP's table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •