CODEWITHSUNDEEP
oracle-databaseplsql
JK1
JK1

Reputation: 33

Procedure Error: ORA-24344: success with compilation error

The procedure takes two dates and find the reports that issued during this duration, and update total salary, and print them.

CREATE OR REPLACE PROCEDURE Salary_Update(p_Date1 DATE, p_Date2 DATE) AS 
CURSOR MYCR IS 
SELECT ReportID, TotalSale, Rate, TotalSalary, ReportDate
FROM WEEKLY_REPORT
WHERE ReportDate BETWEEN TO_DATE(p_Date1, 'DD-MM-YYYY') AND TO_DATE(p_Date2, 'DD-MM-YYYY'); 
BEGIN
FOR MYPOINTER IN MYCR LOOP
UPDATE WEEKLY_REPORT SET TotalSalary = ((TotalSale/100)*Rate);
DBMS_OUTPUT.PUT_LINE('The total salary for report ' || MYPOINTER.REPORTID || '  updated to ' || MYPOINTER.TotalSalary || 'dollars, which is ' || MYPOINTER.Rate || '% of the total sale of ' || MYPOINTER.TotalSale || 'dollars.');
WHERE ReportID = MYPOINTER.ReportID;
END LOOP;
END Salary_Update;
/

the execution

BEGIN Salary_Update('02-04-2020','05-04-2020');
END;
BEGIN 
Salary_Update(to_date('02-04-2020','dd-mm-yyyy'), to_date('05-04-2020','dd-mm-yyyy'));
END;

both don't work.

Upvotes: 0

Views: 344

Answers (1)

Justin Cave
Justin Cave

Reputation: 231861

There are a number of syntax and logic errors. You haven't specified exactly what you want the code to do or provided a reproducible test case but my guess is that you want something like

CREATE OR REPLACE PROCEDURE Salary_Update(
  p_Date1 DATE, 
  p_Date2 DATE) 
AS 
  CURSOR MYCR IS 
    SELECT ReportID, TotalSale, Rate, TotalSalary, ReportDate
      FROM WEEKLY_REPORT
     WHERE ReportDate BETWEEN p_date1 AND p_date2;
BEGIN
  FOR MYPOINTER IN MYCR 
  LOOP
    UPDATE WEEKLY_REPORT 
       SET TotalSalary = ((TotalSale/100)*Rate)
     WHERE ReportID = MYPOINTER.ReportID;
    DBMS_OUTPUT.PUT_LINE('The total salary for report ' || MYPOINTER.REPORTID || '  updated to ' || MYPOINTER.TotalSalary || 'dollars, which is ' || MYPOINTER.Rate || '% of the total sale of ' || MYPOINTER.TotalSale || 'dollars.');
  END LOOP;
END Salary_Update;
/

The string you're outputting doesn't seem to make sense-- MYPOINTER.TotalSalary is the value from the cursor which will be the value from before the update. If it is important that you print out the value you updated TotalSalary to, you'd probably want something like

CREATE OR REPLACE PROCEDURE Salary_Update(
  p_Date1 DATE, 
  p_Date2 DATE) 
AS 
  CURSOR MYCR IS 
    SELECT ReportID, TotalSale, Rate, TotalSalary, ReportDate
      FROM WEEKLY_REPORT
     WHERE ReportDate BETWEEN p_date1 AND p_date2;
  l_newSalary weekly_report.totalSalary%type;
BEGIN
  FOR MYPOINTER IN MYCR 
  LOOP
    l_newSalary := (mypointer.TotalSale/100) * mypointer.rate;
    UPDATE WEEKLY_REPORT 
       SET TotalSalary = l_newSalary
     WHERE ReportID = MYPOINTER.ReportID;
    DBMS_OUTPUT.PUT_LINE('The total salary for report ' || MYPOINTER.REPORTID || '  updated to ' || l_newSalary || 'dollars, which is ' || MYPOINTER.Rate || '% of the total sale of ' || MYPOINTER.TotalSale || 'dollars.');
  END LOOP;
END Salary_Update;
/

If the error that you're getting is an ORA-00942 error that weekly_report doesn't exist, then there is no such table in the current schema. Assuming that the query works when run interactively, potentially the table exists in a different schema, there is a local synonym in the current schema for that table, and the owner of the procedure only has access to the table granted via a role. In that case, you'd need your DBA to grant access to the table directly to the owner of the procedure.

Upvotes: 1

Related Questions