CREATE OR REPLACE PROCEDURE delete_rows (
v_table_name USER_OBJECTS.OBJECT_NAME%TYPE,
v_where_clause VARCHAR2 )
v_where_clause VARCHAR2 )
IS
l_table_name USER_OBJECTS.OBJECT_NAME%TYPE;
l_sql_stmt VARCHAR2(2000);
l_count PLS_INTEGER;
BEGIN
SELECT object_name INTO l_table_name
FROM user_objects
WHERE object_type = 'TABLE'
AND object_name = UPPER(v_table_name);
l_sql_stmt := 'DELETE FROM '|| l_table_name || ' WHERE '
||v_where_clause;
EXECUTE IMMEDIATE l_sql_stmt;
l_count := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE (l_count||' Record(s) from '
l_table_name USER_OBJECTS.OBJECT_NAME%TYPE;
l_sql_stmt VARCHAR2(2000);
l_count PLS_INTEGER;
BEGIN
SELECT object_name INTO l_table_name
FROM user_objects
WHERE object_type = 'TABLE'
AND object_name = UPPER(v_table_name);
l_sql_stmt := 'DELETE FROM '|| l_table_name || ' WHERE '
||v_where_clause;
EXECUTE IMMEDIATE l_sql_stmt;
l_count := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE (l_count||' Record(s) from '
||v_table_name ||' table has been deleted');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Invalid Table Name');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Other exception raised '||sqlerrm);
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Invalid Table Name');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Other exception raised '||sqlerrm);
END;
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 delete_rows('Emp_t','empno = 7654');
3 end;
4 /
1 Record(s) from Emp_t table has been deleted
PL/SQL procedure successfully completed
SQL> BEGIN
2 delete_rows('Emp_t','empno = 7654');
3 end;
4 /
1 Record(s) from Emp_t table has been deleted
PL/SQL procedure successfully completed
No comments:
Post a Comment