Friday, March 4, 2011

Dynamic SQL - Execute Immediate -that accept table name and where clause

CREATE OR REPLACE PROCEDURE delete_rows ( 
             v_table_name USER_OBJECTS.OBJECT_NAME%TYPE,
             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 '
         ||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;

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

No comments:

Post a Comment

External Table

Oracle External Table External tables are defined as tables that do not resides in the database allows you to access data that is stor...