Tuesday, November 17, 2009

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.

4 comments:

s1m0n said...

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"

Aivars Smaukstelis said...

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.

Anonymous said...

You also need to drop the demo_images table

alm said...

In the newest APEX release, you can omit
DROP TABLE DEMO_PAGE_HIERARCHY CASCADE CONSTRAINTS PURGE

that table is not in the demo application.