CODEWITHSUNDEEP
sqloracle-databaseplsqlsyntax-error
pecabum
pecabum

Reputation: 27

Getting error in PL/SQL Oracle

I'm newbie in PL/SQL and I have problem with the following code: 

ALTER TRIGGER secure_employees DISABLE;

ALTER TABLE employees ( ADD (exceed_avgsal VARCHAR2(3) DEFAULT 'NO' 
 CONSTRAINT employees_exceed_avgsal_ck CHECK (exceed_avgsal IN ('YES', 'NO')));

CREATE OR REPLACE PROCEDURE check_avgsal IS
  avgsal_exceeded employees.exceed_avgsal%type;
  CURSOR emp_csr IS
    SELECT employee_id, job_id, salary FROM employees FOR UPDATE;
  e_resource_busy EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_resource_busy, -54);

  FUNCTION get_job_avgsal(jobid VARCHAR2) RETURN NUMBER IS
    avg_sal employees.salary%type;
  BEGIN
    SELECT (max_salary + min_salary) / 2
      INTO avg_sal
      FROM jobs
     WHERE job_id = jobid;
    RETURN avg_sal;
  END;
BEGIN
  FOR emprec IN emp_csr LOOP
    avgsal_exceeded := 'NO';
    IF emprec.salary >= get_job_avgsal(emprec.job_id) THEN
      avgsal_exceeded := 'YES';
    END IF;
    UPDATE employees
       SET exceed_avgsal = avgsal_exceeded
     WHERE CURRENT OF emp_csr;
  END LOOP;
EXCEPTION
  WHEN e_resource_busy THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20001, 'Record is busy, try later.');
END check_avgsal;

EXECUTE check_avgsal 

SELECT e.employee_id, e.job_id, (j.max_salary-j.min_salary/2) job_avgsal, e.salary, e.exceed_avgsal avg_exceeded 
 FROM employees e, jobs j WHERE e.job_id = j.job_id and e.exceed_avgsal = 'YES';
COMMIT;

I get the error message

PROCEDURE CHECK_AVGSAL compiled
Errors: check compiler log

If anyone can help me I will be thankful

Upvotes: 1

Views: 1286

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

You need a / on a line on its own, between the end chk_avgsal; and the next statement, to run the PL/SQL block:

...
END LOOP; 
EXCEPTION WHEN e_resource_busy THEN ROLLBACK; 
RAISE_APPLICATION_ERROR (-20001, 'Record is busy, try later.');
END check_avgsal;
/

EXECUTE check_avgsal 
...

Your alter table syntax is wrong too:

ALTER TABLE employees ADD (exceed_avgsal VARCHAR2(3) DEFAULT 'NO',
  CONSTRAINT employees_exceed_avgsal_ck CHECK (exceed_avgsal IN ('YES', 'NO')));

SQL Fiddle demo.

Upvotes: 3

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59642

You forgot the semicolon:

EXECUTE check_avgsal;

But you could do it in a simple UPDATE:

UPDATE employees e
    SET avgsal_exceeded = (
WITH avg_salaries AS 
    (SELECT (max_salary + min_salary)/2 AS avg_sal, job_id FROM jobs)
SELECT CASE 
    WHEN employee_id >= avg_sal THEN 'YES'
    ELSE 'NO'
    END
FROM avg_salaries
    WHERE a.job_id = e.job_id);

Upvotes: 1

Related Questions