CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Ravi
Ravi

Reputation: 863

how to execute delete statement inside plsql block and call it in a procedure

I have written below pl sql block and trying to create a procedue. But i am getting warnings and not able to execute the procedue.

Please suggest if something i am missing \

Please let me know if this question is duplicate as i am not able to get the exact link to refer

create or replace PROCEDURE EmployeeProc
IS 
BEGIN
delete from Employeetable where EmplId in (
select EmployeeId FROM EmployeeMstrTbl where JoiningDate between to_date('2019-01-01','YYYY-MM-DD') and to_date('2019-02-28','YYYY-MM-DD'));
commit;
DBMS_OUTPUT.PUT_LINE('Deleted '||SQL%ROWCOUNT ||' records from Employeetable');
END;

Error: Object Invalid

Upvotes: 1

Views: 4232

Answers (3)

Nick
Nick

Reputation: 337

Your code works just fine.

CREATE TABLE Employeetable
(
    EmplId NUMBER
);

CREATE TABLE EmployeeMstrTbl
(
    EmployeeId NUMBER,
    JoiningDate DATE
);

CREATE OR REPLACE PROCEDURE EmployeeProc
IS
BEGIN
    DELETE FROM Employeetable
          WHERE EmplId IN
                    (SELECT EmployeeId
                       FROM EmployeeMstrTbl
                      WHERE JoiningDate BETWEEN TO_DATE ('2019-01-01',
                                                         'YYYY-MM-DD')
                                            AND TO_DATE ('2019-02-28',
                                                         'YYYY-MM-DD'));

    COMMIT;
    DBMS_OUTPUT.PUT_LINE (
        'Deleted ' || SQL%ROWCOUNT || ' records from Employeetable');
END;

EXEC EmployeeProc;

DROP TABLE Employeetable;
DROP TABLE EmployeeMstrTbl;
DROP PROCEDURE EmployeeProc;

Script output:

Table created.
Table created.
Procedure created.
 PL/SQL procedure successfully completed.
Table dropped.
Table dropped.
Procedure dropped.

DBMS Output:

Deleted 0 records from Employeetable

Maybe you have a typo in a table name, column name or something similar.

I suggest that you try to execute your delete statement first to check if it works.

Upvotes: 1

ᎮᏒᏗᏉᏋᏋᏁ
ᎮᏒᏗᏉᏋᏋᏁ

Reputation: 29

Try using cursor

CREATE OR REPLACE PROCEDURE EMPLOYEEPROC IS 
CURSOR C1 IS 
SELECT EMPLOYEEID 
FROM EMPLOYEEMSTRTBL 
WHERE JOININGDATE BETWEEN TO_DATE('2019-01-01','YYYY-MM-DD') AND TO_DATE('2019-02-28','YYYY-MM-DD'));
BEGIN
    FOR I IN C1 LOOP        
        DELETE FROM EMPLOYEETABLE 
        WHERE EMPLID=I.EMPLOYEEID;
    END LOOP;

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('DELETED '||SQL%ROWCOUNT ||' RECORDS FROM EMPLOYEETABLE');

END;

Upvotes: 0

TineO
TineO

Reputation: 1033

Not sure if perhaps you mistyped something, or if it is to do with how you have it set up. But even if it is a mistype and it could work, it's not nice, so do a loop.

i = 0;
FOR r in (select * FROM EmployeeMstrTbl where JoiningDate between to_date('2019-01-01','YYYY-MM-DD') and to_date('2019-02-28','YYYY-MM-DD'))
  LOOP
  DELETE FROM Employeetable where EmplId = r.EmployeeId;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Deleted '|| i ||' records from Employeetable');

Because this will work, and more importantly, its easier to understand. Keeping code short and abbreviated has become much less important nowadays since the size of the code is almost never the problem, but keeping it easy to understand is extremely important so that it can be maintained in the future.

Upvotes: 0

Related Questions