ORACLE CUSTOM FUNCTION - SPLIT DELIMITER STRING

-- User Defined Types to Hold string as table Starts --
CREATE OR REPLACE TYPE SPLITTED_TEXT AS OBJECT(SPLITTED_VALUE VARCHAR2(50));
CREATE OR REPLACE TYPE SPLITTED_TEXT_TABLE AS TABLE OF SPLITTED_TEXT;
-- User Defined Types to Hold string as table Ends--

-- Function to split delimiter string Starts --

CREATE OR REPLACE
FUNCTION OBF_SPLIT_DELIMITER_STRING_FN(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN SPLITTED_TEXT_TABLE PIPELINED
AS
    v_length   NUMBER := LENGTH(p_string);
    v_start    NUMBER := 1;
    v_index    NUMBER;
    --v_tempstring SPLITTED_TEXT_TABLE.SPLITTED_VALUE%type;
BEGIN
    WHILE(v_start <= v_length)
    LOOP
        v_index    := INSTR(p_string, p_delimiter, v_start);

        IF v_index = 0
        THEN    
           -- v_tempstring := SUBSTR(p_string,v_start);
            PIPE ROW(new SPLITTED_TEXT(SUBSTR(p_string,v_start)));
            v_start    := v_length + 1;
        ELSE
            --v_tempstring := SUBSTR(p_string, v_start, v_index - v_start);
            PIPE ROW(new SPLITTED_TEXT(SUBSTR(p_string, v_start, v_index - v_start)));
            v_start    := v_index + 1;
        END IF;
    END LOOP;

    RETURN;
END OBF_SPLIT_DELIMITER_STRING_FN;

-- Function to split delimiter string Ends--

-- Usage of Function Starts --

select * from table(OBF_SPLIT_DELIMITER_STRING_FN('one#two#three#four','#'))

-- Usage of Function Ends--

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--------------