tookertime.com

Welcome to the first in a series of tutorials on Oracle Application Express.

As you are already here, I’m not going to start evangelising about the benefits of the framework, or even explain what it is – I will save that for another day.

For now, I will assume that you have installed Application Express or have access to a workspace somewhere. If you haven’t, there are plenty of good tutorials online, but I’d recommend the Oracle guides for any installation of Oracle software:

• Installing Application Express 3.2 (ie you already have Oracle installed)
• Installing Oracle XE Windows (ie you just want a playpen environment)
• Installing Oracle XE Linux

Alternatively, you can sign up with oracle for a free workspace here: http://apex.oracle.com/i/index.html. However, if you’re just starting out in your own environment, I’d recommend installing express edition – it’s as simple as double clicking the install file and entering a password. A full blown install can take a little more preparation and tweaking, again I will possibly save that for another post.

By the end of this series of tutorials, we will have developed a very simple blogging application. Any database driven web application framework should find this task a breeze. If you’ve ever worked with ruby on rails you may remember its claim that you can create a weblog in 20 minutes – I’ve noticed now that with “Rails 2” they’ve cut that down to 15 – http://rubyonrails.org/screencasts! We’re not going to achieve that with apex – because the end product will be an application you can deploy rather than scaffolding you can’t. Let’s call it 25 minutes.

Part 1 – Creating a Workspace

So, you’ve installed application express or you’ve got access to an environment. The first thing to do is to create a new workspace. If you have access to the administation screens or are using Oracle’s hosting then the steps are below, otherwise just get your friendly database administrator to do it for you.

I’m going to show two approaches here, using Oracle’s hosted sample workspaces, and using your own workspace.

Option 1: Using Oracle’s Hosting

You’ll need to sign up for a demo account using your name, e-mail address etc here: http://apex.oracle.com/i/index.html. In workspace choose something that makes sense to you, and same in schema. After your request is reviewed (10-15 minutes) you will receive an email with your new workspace details and a link to validate your workspace. After activation, you’ll get a second email with your password. Follow the link in the second e-mail, and use your new details to log in. If all goes well, you should be presented with this screen:

Option 2: Using Your Own Hosting

Log in to the APEX administration services using the admin URL for your host: http://yourhost:7777/pls/apex/apex_admin.

Create the workspace by navigating to:

Manage Workspaces->Create Workspace

Fill out the details as requested. My workspace name is going to be BLOGTUT_DEV and same with schema name. 50Mb of space should be plenty for this application. After this is complete, you may need to add additional users or even yourself depending on how you set up the administrator. To do this, select “Manage Developers and Users” in the “Manage Workspaces” screen.

Now log out of the admin section and click Login again and enter the new details of your workspace. You should see the same screen as above.

Part 2 – Taking Out The Trash

In your workspace you have three BIG buttons. There are two ways you can use these:

1. Clicking on the down arrow next to each image gives you a shortlist of options
2. Clicking on the image itself takes you to a menu screen with all available options in that domain

I’d recommend clicking on the images rather than the down buttons while learning the ropes, since that gives all available options and not a subset. The three domains are:

• Application Builder
• SQL Workshop
• Utilities

We will now use these to solve probably the most annoying thing about a new workspace – the workspace install creates demo tables and a sample application.

Viewing Database Objects

Let us start by viewing what objects the install created in the database. Navigate to:
Utilities->Object Reports->All Objects
To confuse you, the initial list is filtered to “Tables”. Change the filter Type to “-All-“ and you will see a list of objects like this:

Well, we don’t need any of those!

Dropping Demo Database Objects

To drop the demo tables the quick way, we can just run drop table statements. To do this, use the breadcrumbs to get back to the home page, then navigate to:

SQL Workshop->SQL Scripts->Create

Paste this into the text box:

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

Enter a name for the script, and click “Run”. You can now click on the “Script” icon to see the results of your script. If all went well it would look something like this:

Dropping The Demo Application

To drop the application we’ll need to use the final big button – Application Builder. Go back to the home page and click Application Builder, this will present you with the available applications. You should see an application called “XXX – Sample Application”. Click this to see all the hard work gone into building the app, then click “Delete This Application” under Tasks, and “Permanantly Delete Now” in the following screen.

We now have  a clean environment to start developing in!

Next week’s instalment of the tutorial will cover creating an application in our new clean workspace.

Tweet This Post Delicious Digg This Post Facebook