APEX: Drop Sample Application Database Objects

Here's a simple script to remove the database objects from the default Sample Application from Application Express (APEX). Deleting the application from the interface does not remove the database objects.

There are 30 database objects created by default along with the Sample Application:

OBJECT_TYPE         OBJECT_NAME
------------------- ----------------------------------------
FUNCTION            CUSTOM_AUTH
                    CUSTOM_HASH
INDEX               DEMO_CUSTOMERS_PK
                    DEMO_CUST_NAME_IX
                    DEMO_ORDER_ITEMS_PK
                    DEMO_ORDER_PK
                    DEMO_ORD_CUSTOMER_IX
                    DEMO_PAGE_HIERARCHY_PK
                    DEMO_PRODUCT_INFO_PK
                    DEMO_USERS_PK
LOB                 SYS_LOB0000077614C00007$$
SEQUENCE            DEMO_CUST_SEQ
                    DEMO_ORDER_ITEMS_SEQ
                    DEMO_ORD_SEQ
                    DEMO_PROD_SEQ
                    DEMO_USERS_SEQ
TABLE               DEMO_CUSTOMERS
                    DEMO_ORDERS
                    DEMO_ORDER_ITEMS
                    DEMO_PAGE_HIERARCHY
                    DEMO_PRODUCT_INFO
                    DEMO_STATES
                    DEMO_USERS
TRIGGER             BI_DEMO_USERS
                    DEMO_ORDER_ITEMS_GET_PRICE
                    DEMO_USERS_T1
                    INSERT_DEMO_CUST
                    INSERT_DEMO_ORDER_ITEMS
                    INSERT_DEMO_PROD
                    UPDATE_ORDER_TOTAL

30 rows selected.

If you have nothing else in your schema, you can run this:

DECLARE
  l_sql VARCHAR2(32767);
BEGIN
  FOR i IN ( SELECT object_name, object_type 
             FROM user_objects
             WHERE object_type NOT IN ( 'LOB', 'INDEX', 'TRIGGER' ) 
             ORDER BY object_type )
  LOOP
    CASE 
      WHEN i.object_type = 'TABLE' THEN
        l_sql := 'DROP TABLE ' || i.object_name || ' CASCADE CONSTRAINTS PURGE';
      ELSE
        l_sql := 'DROP ' || i.object_type || ' ' || i.object_name;
    END CASE;
    EXECUTE IMMEDIATE l_sql;
  END LOOP;
END;
/

If however, you do have other items in there, use this:

DROP FUNCTION CUSTOM_AUTH;
DROP FUNCTION CUSTOM_HASH;
DROP SEQUENCE DEMO_ORDER_ITEMS_SEQ;
DROP SEQUENCE DEMO_PROD_SEQ;
DROP SEQUENCE DEMO_ORD_SEQ;
DROP SEQUENCE DEMO_USERS_SEQ;
DROP SEQUENCE DEMO_CUST_SEQ;
DROP TABLE DEMO_STATES CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_PAGE_HIERARCHY CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_USERS CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_ORDER_ITEMS CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_ORDERS CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_PRODUCT_INFO CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_CUSTOMERS CASCADE CONSTRAINTS PURGE;
DROP TABLE DEPT CASCADE CONSTRAINTS PURGE;
DROP TABLE EMP CASCADE CONSTRAINTS PURGE;

Triggers, LOBs and Indexes will be dropped along with their associated objects.