Procedure error - PLS-00103 Encountered the symbol ">"

Question

I am trying to learn PLSQL and I have a problem creating a procedure.

The task I am solving is: Create a procedure to check commissions. Commissions higher than 0.35 can only be entered for employees with more than 15 years of experience. If the commission will be higher or practice will be lower, then an error will be printed. Take advantage of exceptions (Exception and Raise) to define an error message.

I wrote this, but there is an error:

PLS-00103 Encountered the symbol ">" when expecting one of following::= . ( @ % ;

create or replace PROCEDURE PROVIZIA(num in number) IS
employee_id number;
com_pct employees.commission_pct%type;
begin
select commission_pct into com_pct from employees where employee_id = num;
if PRAX(employee_id) > 15 then
com_pct > 0.35;
else PRAX(employee_id) < 15 then
com_pct < 0.35;
end if;
exception when com_pct > 0.35 and PRAX(employee_id) < 15 then
dbms_output.put_line('error');
raise;
end PROVIZIA;

Can you please show me where i am making a mistake?

Thank you.

Answer 1

Suppose this is a test case (table and prax function which returns some number; I don't know which and why, so I made it so that it returns an "invalid" value for employee 1):

SQL> create table employees as
  2  select 1 employee_id, 0.5 commission_pct from dual union all
  3  select 2, 0.2 from dual;

Table created.

SQL> create or replace function prax(par_empid in number) return number is
  2  begin
  3    return case when par_empid = 1 then 10
  4                else 50
  5           end;
  6  end prax;
  7  /

Function created.

SQL> select employee_id, commission_pct, prax(employee_id) prax_result
  2  from employees;

EMPLOYEE_ID COMMISSION_PCT PRAX_RESULT
----------- -------------- -----------
          1             ,5          10     --> this combination is invalid
          2             ,2          50     --> this is OK

SQL>

Procedure which raises an error if values are "wrong"; doesn't do anything otherwise (because you didn't say what to do in that case):

SQL> create or replace procedure provizia(num in number) is
  2    com_pct      employees.commission_pct%type;
  3    l_err        exception;
  4  begin
  5    select commission_pct
  6      into com_pct
  7      from employees
  8      where employee_id = num;
  9
 10    if com_pct > 0.35 and prax(num) < 15 then
 11       raise l_err;
 12    end if;
 13
 14  exception
 15    when l_err then
 16      raise_application_error(-20000, 'Error');
 17  end provizia;
 18  /

Procedure created.

SQL>

Let's test it:

SQL> exec provizia(num => 1);
BEGIN provizia(num => 1); END;

*
ERROR at line 1:
ORA-20000: Error
ORA-06512: at "SYS.PROVIZIA", line 16
ORA-06512: at line 1


SQL> exec provizia(num => 2);

PL/SQL procedure successfully completed.

SQL>

Now that you have a working example, feel free to improve it.