CODEWITHSUNDEEP
sqloracle-databasestored-procedurestriggersoracle10g
atrueresistance
atrueresistance

Reputation: 1368

Trigger calling procedure error

I am having some troubles with this trigger. I created a procedure to check and see if salary is within a certain boundary. If it fails to fall within a certain range, raise the exception. The problem is even though the procedure compiles with no errors, the trigger can not find the procedure. 

set serveroutput on;
create or replace procedure check_salary (
    tmp_id in varchar2,
    tmp_sal in number
  )
IS
v_sal number(6,0) := tmp_sal;
v_min number(6,0);
v_max number(6,0);
ex_fail exception;
cursor cur_select is
    select min_salary, job_id, max_salary
    from jobs where job_id = tmp_id;

BEGIN

 for rec_something in cur_select loop
  v_min := rec_something.min_salary;
  v_max := rec_something.max_salary;
    if v_sal >= v_min and v_sal <= v_max then
      raise ex_fail;
    end if;
 end loop;
 exception
  when ex_fail then
    dbms_output.put_line('Invalid salary ' || v_sal || ' must be between ' || v_min  || ' and ' || v_max ||'.');
END;
/ 
show errors;


create or replace trigger check_salary_trg
  after insert or update on employees
  for each row
declare

begin
  IF UPDATING or INSERTING THEN
    execute check_salary(:NEW.job_id, :NEW.salary);
  end if;
end;
/
show errors;

The Error Message:

PROCEDURE check_salary compiled
No Errors.
TRIGGER check_salary_trg compiled
Warning: execution completed with warning
5/13           PLS-00103: Encountered the symbol "CHECK_SALARY" when expecting one of the following:

   := . ( @ % ; immediate
The symbol ":=" was substituted for "CHECK_SALARY" to continue.

Upvotes: 1

Views: 2129

Answers (3)

user1983636
user1983636

Reputation: 1

The stack overflow exception is due to the use of dbms_output.put_line inside check_salary procedure.

SQL*Plus command set serveroutput on reserves little size as default, you must specify the buffer size or remove the dbms_output.put_line from check_salary procedure.

In order to increase default buffer size use this:

set serveroutput on size 1000000

Upvotes: 0

psaraj12
psaraj12

Reputation: 5072

When you are executing a procedure within a PL/SQL block, you do not use the

 EXECUTE syntax

More information about execute you can check the below link

http://docstore.mik.ua/orelly/oracle/prog2/ch23_01.htm

Upvotes: 0

Eggi
Eggi

Reputation: 1714

Change it to:

create or replace trigger check_salary_trg
  after insert or update on employees
for each row
begin
  IF UPDATING or INSERTING THEN
    check_salary(:NEW.job_id, :NEW.salary);
  end if;
end;
/

Upvotes: 2

Related Questions