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.

Dilbert: You're Fired!

APEX: Install 3.2.1 on 11gR2

Yes, I know it comes pre-installed with 11g. No, I didn't bother to check what the default version was either. I needed practice so I removed it and re-installed it. This follows my recent install of 11gR2 on OEL v5.4.

First up, get the latest version from apex.oracle.com. I unzipped the file in a shared folder (which was then mounted to OEL) in the following directory (from OEL):

/mnt/software/oracle/apex/apex

In another window, I open up the installation doc.

From the database server, I moved to that directory and logged in as SYS. Here's the list of files in that directory:

[oracle@oracledb apex]$ ls -l
apex_epg_config_core.sql
apex_epg_config.sql
apexins.sql
apexvalidate.sql
apxchpwd.sql
apxconf.sql
apxdbmig.sql
apxdevrm.sql
apxdvins.sql
apxe101.sql
apxe102.sql
apxe111.sql
apxldimg.sql
apxpatch.sql
apxrelod.sql
apxremov.sql
apxrtins.sql
apxsqler.sql
apxxemig.sql
apxxepwd.sql
catapx.sql
coreins.sql
devins.sql
endins.sql
load_trans.sql

Since it's already installed, I want to remove it. To do so, I need to run apxremov.sql. I run it and a few minutes later its gone. I confirm this by looking in the DBA_REGISTRY view:

SELECT comp_name, version
FROM dba_registry
ORDER BY comp_name;

There are 4 basic installation scenarios:

1. Downloading from OTN and configuring the embedded PL/SQL gateway (11g)
2. Downloading from OTN and configuring Oracle HTTP Server (9.2.0.3 -- 11gR2)
3. Installing from the database and configuring the embedded PL/SQL gateway (11g)
4. Installing from the database and configuring the Oracle HTTP server (11g)

I'm going with #1.

I've removed the default installation of APEX, now I'm going to install it. I'm still logged in to SYS so I just need to run the apexins.sql script.

SQL>@apexins.sql SYSAUX SYSAUX temp /i/

The first parameter SYSAUX is the tablespace where APEX user will ive. The second SYSAUX is where the APEX files will live. TEMP is the name of the temporary tablespace and /i/ is the image (virtual) directory. Installation on my standalone machine took about 30 minutes.

The installation script logs you out at the end, so for the next step, you have to login as SYS account. Now we'll be setting the ADMIN account password. This password will be used here:



or here if you use INTERNAL as the workspace name (thanks John!):



Script to change the ADMIN password: apxchpwd.sql

Since this is technically an upgrade, I need to run apxldimg.sql

SQL> @apxldimg /mnt/software/oracle/apex

PL/SQL procedure successfully completed.

Directory created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.
timing for: Load Images
Elapsed: 00:02:33.48

Directory dropped.

Verify that the ANONYMOUS account is unlocked, if it isn't unlock it.

ALTER USER ANONYMOUS ACCOUNT UNLOCK;

We need to verify the EPG port now:

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

If a 0 (zero) is returned, EPG is not set-up yet. I'm going to use port 8080 because I'm too lazy to figure out, for now, how to set it up on port 80.

EXEC dbms_epg.sethttpport( 8080 );

Finally we have to enable network services. Since this is my own sandbox, security is not a concern. I'm going with the default as provided by the documentation:

DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  SELECT ACL 
  INTO ACL_PATH 
  FROM DBA_NETWORK_ACLS
  WHERE HOST = '*' 
    AND LOWER_PORT IS NULL 
    AND UPPER_PORT IS NULL;

  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) 
  INTO ACL_ID
  FROM 
    XDB.XDB$ACL A, 
    PATH_VIEW P
  WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) 
    AND EQUALS_PATH(P.RES, ACL_PATH) = 1;

  DBMS_XDBZ.ValidateACL(ACL_ID);
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_030200', 
     'connect') IS NULL 
  THEN 
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 
    'APEX_030200', TRUE, 'connect'); 
  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
      ( 'power_users.xml',
        'ACL that lets power users to connect to everywhere',
        'APEX_030200', 
        TRUE, 
        'connect' );
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

You should be ready to go. The url will be http://localhost:8080/apex/apex_admin

If you are prompted with a XDB login window like this:



You may have forgotten to unlock the ANONYMOUS account. (I did, naturally).

APEX: Manage Service

Home --> Administrative Services

Within the Administrative Services space, you have 4 options:

• Manage Service
• Manage Workspace
• Manage Applications
• Monitor Activity

Of those 4, Manage Service is probably the most important area. This is the location where you will administer your entire APEX instance.



Within the Manage Service space, you have 3 categories:

• Manage Service
• Manage Environment Settings
• Manage Shared Components

Manage Shared Components

This is where you manage your public themes. What are public themes?

Public themes allow you to add to the theme repository. They are available when you create an application or when you create a theme for an existing application.

You create a public theme by selecting a theme from within a workspace and application. Once created, it cannot be edited directly. To edit, create an application based off the theme, edit the theme within that application and then you can create a new public theme with the updated application theme (and delete the old, if you no longer want it).

Manage Service



In the Manager Service section (of Manager Service, naturally), there are 5 sections:

• Site-Specific Tasks
• Logs
• Session State
• Mail Queue
• Installed Translations

I just now learned what Site-Specific Tasks are (isn't that the whole point of this?). You can either create a task that will display on the Workspace Home or the Workspace Login. I've created 2 tasks, one for each area:



Where do those display?

Workspace Login



Workspace Home



Site-Specific Tasks is helpful to relay messages to your development staff, perhaps a planned outage or a reminder to use constraints. ;)

Next up are Logs. From here you can manually purge SQL Workshop, Page View Acitivity, Developer Activity , External Click, Mail and Login Access logs. The reports on these logs can be viewed from the Workspace Home. Not really a whole lot to see here.

Session State
Not a whole lot to see here either. There is a report to view recent sessions with drill-down capabilities, but that's about it. The other 2 items in this section are "Purge Sessions, by age" and "Session State Statistics."

Mail Queue
Basically just a log of all the pending emails to be sent. You have the option to send them or delete individual emails.

Installed Translations
Finally, not much to see here either. Just a list of, you guessed it, installed translations.



I'm going to take on the final piece of the Manage Service section in a separate post. This is where the guts of your APEX instance are managed. Lots of fun stuff here.