CODEWITHSUNDEEP
oracle-databaseplsql
Darkin
Darkin

Reputation: 1

writing function but been getting this error

the question:

Write a blocK PL/SQL that display the total commission amount of a job id. Use function “compute_commission” that accepts a job id equal to 9 and return his total commission of all corresponding employees.

the error:

`Error at line 11: PLS-00103: Encountered the symbol "DECLARE"

  1. CREATE OR REPLACE FUNCTION compute_commission (C_employee_id in number)
  2. RETURN number
  3. is `

the code:

CREATE OR REPLACE FUNCTION compute_commission (C_employee_id in number)  
 RETURN number
 is 
 sum_commission number;
begin
select sum(job_id)
into sum_commission from employees
where employee_ref_id = C_employee_id;
return sum_commission;
end compute_commission;

declare 
cal_sum_commission number;
begin
cal_sum_commission = compute_commission(cal_sum_commission);
dbms_output.put_line ('employee commission is: ' || compute_commission(cal_sum_commission);
end;

Upvotes: 0

Views: 22

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Should be something like this:

CREATE OR REPLACE FUNCTION compute_commission (C_employee_id IN NUMBER)
   RETURN NUMBER
IS
   sum_commission  NUMBER;
BEGIN
   SELECT SUM (job_id)
     INTO sum_commission
     FROM employees
    WHERE employee_ref_id = C_employee_id;

   RETURN sum_commission;
END compute_commission;
/

DECLARE
   cal_sum_commission  NUMBER := 12345;
BEGIN
   cal_sum_commission := compute_commission (cal_sum_commission);
   DBMS_OUTPUT.put_line (
      'employee commission is: ' || cal_sum_commission);
END;
/

Note that I modified anonymous PL/SQL block and

  • added local variable's value (otherwise you'd pass NULL to the function) (you'll, of course, use some valid value; this - 12345 - is just an example)
  • used local variable in DBMS_OUTPUT.PUT_LINE
  • terminated statement with a semi-colon (you've had a colon)
  • fixed assignment operator (:= instead of just =)

Also, is sum_commision really sum of JOB_ID values? Looks strange to me ...

Upvotes: 1

Related Questions