ORACLENERD: APEX: Drop Sample Application Database Objects

APEX: Drop Sample Application Database Objects

Tuesday, November 17, 2009

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.

Labels: apex, howto, oradb

¶ 11/17/2009 03:42:00 PM

Comments:

Handy script, i also don't like the automatic creation of these demo object.
But you can also change the option 'Create demonstration objects in new workspaces' to No in the instance configuration "Home > Manage Service > Feature Configuration"

# posted by

s1m0n : November 18, 2009 at 7:30 AM

I am using APEX 3.2.1 on Oracle Database XE.

When creating new workspace, APEX 3.2.1 creates four extra database objects - two tables (DEPT and EMP) and two unique indexes for another demonstration application "Collection Showcase", but application itself is not installed by default.

I think, correct way to remove already installed demonstration applications *should* be
Home > Application Builder [v] > Demonstrations
and then remove both applications.

But...
1) as long as "Collection Showcase" is not fully installed, "Remove" option is unavailable;
2) LOB "SYS_LOB0000077614C00007$$" is not deleted automatically, when removing "Sample Application".

I am not sure, whether these problems occur also on Oracle Database 11g.

# posted by

Aivars Smaukstelis : December 17, 2009 at 10:47 AM

You also need to drop the demo_images table

# posted by