PL/SQL: ORA-01747 error: invalid specification user.table.column, table.column or column

Question

I am trying to learn PL/SQL and I have a problem creating procedure.

I want to create a procedure that will automatically increase the commission by 10% after the end of trial period - 3 months. I need to create a scheduler, who will activate the procedure every day and check whether the condition is fulfilled.

My code:

create or replace procedure upd_sal(v_emp_id in employees.employee_id%type,
                                    v_h_date in employees.hire_date%type) is
  cursor c_emp_sal_update(cv_emp_id employees.employee_id%type) is
  select commission_pct
    from employees
   where employee_id = cv_emp_id
     for update of commission_pct nowait;
begin
  for v_c in c_emp_sal_update(v_emp_id) loop
    if v_c.add_months(sysdate, -3) = v_h_date then
      update employees
         set commission_pct = commission_pct + 0, 10
       where current of c_emp_sal_update;
    end if;
  end loop;
end upd_sal;
/

Can you advise me please?

Thank you

Answer 1

Wellcome to the world of PL/SQL and Oracle's misleading error messages ;-)

I think it's just one typo and one syntax error. Adding 10% should be something like

set commission_pct = commission_pct * 1.10

and secondly, the addmonth muss not have a variable in front of it:

if add_months(sysdate, -3) = v_h_date then

BTW, personally, I'd update all employees in one statement instead of a loop, but that wasn't your question :-)

create or replace procedure upd_sal is
begin
  update employees
     set commission_pct = commission_pct * 1.1
   where add_months(trunc(sysdate), -3) = trunc(hire_date);
end upd_sal;
/