ORACLE CUSTOM FUNCTION - DELETE DB OBJECTS BY TYPE AND NAME

This function used to delete oracle DB objects such as Table,View And Index Etc.

--------- Function Body Starts ---------------

FUNCTION FIND_AND_DELETE_OBJECT_FN(STR_OBJECT_TYPE IN VARCHAR2,STR_OBJECT_NAME IN VARCHAR2)
RETURN VARCHAR2
IS
v_exist  INTEGER;
v_output VARCHAR2(10) := 'FAIL';
BEGIN
 select count(*) into v_exist from all_objects where OBJECT_NAME = STR_OBJECT_NAME AND OBJECT_TYPE = STR_OBJECT_TYPE;
 if v_exist = 1 then
  EXECUTE IMMEDIATE 'TRUNCATE TABLE '||STR_OBJECT_NAME;
  EXECUTE IMMEDIATE 'DROP TABLE '||STR_OBJECT_NAME;
  v_output := 'SUCCESS';
 end if;
RETURN v_output;
exception when others then
 raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END OBF_FIND_AND_DELETE_OBJECT_FN;

--------- Function Body Ends ---------------

-------- Function Usage Starts --------------

DECLARE
  STR_OBJECT_TYPE VARCHAR2(200);
  STR_OBJECT_NAME VARCHAR2(200);
  v_Return VARCHAR2(200);
BEGIN
  STR_OBJECT_TYPE := 'TABLE';
  STR_OBJECT_NAME := 'FSSPECANDTECH';
  v_Return := OBF_FIND_AND_DELETE_OBJECT_FN(
    STR_OBJECT_TYPE => STR_OBJECT_TYPE,
    STR_OBJECT_NAME => STR_OBJECT_NAME
  );
  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;

-------- Function Usage Ends--------------


0 Responses to "ORACLE CUSTOM FUNCTION - DELETE DB OBJECTS BY TYPE AND NAME"