Live ORACLE

DROP TABLE employees_temp CASCADE CONSTRAINTS PURGE;


CREATE TABLE employees_temp
AS
SELECT *
FROM   employees;


CREATE OR REPLACE PROCEDURE Delete_rows

(table_name  IN VARCHAR2,

 condition   IN VARCHAR2 DEFAULT NULL)

AS

where_clause  VARCHAR2(100) := ' WHERE '

                            ||condition;

v_table       VARCHAR2(30);

BEGIN

-- first make sure that the table actually exists; if not, raise an exception

SELECT object_name

INTO   v_table

FROM   user_objects

WHERE  object_name = Upper(table_name)

    AND object_type = 'TABLE';

IF condition IS NULL THEN

where_clause := NULL;

END IF;

EXECUTE IMMEDIATE 'DELETE FROM '|| v_table || where_clause;

EXCEPTION

WHEN no_data_found THEN

dbms_output.Put_line('Invalid table: '

                    ||table_name);

END;

/


BEGIN

Delete_rows('employees_temp','employee_id = 111');

END;

/

Dynamic SQL Procedure that Accepts Table Name and WHERE Clause