PL/SQL script throws errors

Question

I'm using PLSQL to create a procedure to update a table based on querying another table, and I'm using a loop for the purpose. FOr testing, I'm holding off on the procedure code, and just trying to make the following script work. But it keeps throwing errors including "Encountered the symbol "end-of-file"" or "invalid SQL statement", based on minor tweaks. Where am I going wrong?

DECLARE CURSOR cur IS
    SELECT * FROM Summary;
BEGIN
FOR rec in cur
LOOP
UPDATE Award
SET monthly_sales = (
   SELECT COUNT(*)
   FROM Sales
   WHERE employee_id = rec.employee_id
     AND to_char(Sales.SALES_DATE,'YY/MM')=to_char(SYSDATE,'YY/MM')
)
WHERE Summary.employee_id = rec.employee_id
END LOOP
END;
/

It may be better to use AND trunc(Sales.SALES_DATE,'month') = trunc(SYSDATE,'month') then the to_char functions with two digit years in the format string. If your sales table ever contained > 100 years of data you could get counts from more than one century, though this bug will take a long time to show up. — Sentinel
– Sentinel, Commented Sep 3, 2015 at 16:32
To @boneist point, you can do this in pure SQL, which will be faster than a row by row update in a loop. — WoMo
– WoMo, Commented Sep 3, 2015 at 16:37

Boneist · Accepted Answer · 2015-09-03 16:33:44Z

As well as the missing semicolons that Sentinel pointed out, your where clause is incorrect:

WHERE Summary.employee_id = rec.employee_id;

Perhaps you meant:

WHERE award.employee_id = rec.employee_id;

since you're updating the AWARD table?

However, I would question why you're using a row-by-row (aka slow-by-slow) approach for this, when you could easily do it in a single statement? Perhaps something like the following would give you the correct results (untested, since you gave no table create scripts or sample input data etc):

merge into award tgt
using (select sls.employee_id,
              count(*) monthly_sales
       from   sales sls
       where  trunc(sls.sales_date,'mm') = trunc(sysdate, 'mm')
       and    sls.employee_id in (select employee_id from summary)
       group by sls.employee_id) src
  on (tgt.employee_id = src.employee_id)
when matched then
update set tgt.monthly_sales = src.monthly_sales;

Sentinel · Accepted Answer · 2015-09-03 16:33:23Z

4

You are missing semicolons at the end of lines 12 and 13:

    DECLARE CURSOR cur IS
    SELECT * FROM Summary;
BEGIN
  FOR rec in cur
  LOOP
    UPDATE Award
    SET monthly_sales = (
       SELECT COUNT(*)
       FROM Sales
       WHERE employee_id = rec.employee_id
         AND trunc(Sales.SALES_DATE,'month') = trunc(SYSDATE,'month')
    )
    WHERE Summary.employee_id = rec.employee_id;
  END LOOP;
END;
/

edited Sep 3, 2015 at 16:33

answered Sep 3, 2015 at 16:26

Sentinel

6,4871 gold badge20 silver badges24 bronze badges

Collectives™ on Stack Overflow

PL/SQL script throws errors

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related