Dirt Cheap Oracle step #2 of 6: Install Linux

[Previous]

Ok, I’ve got all the hardware I need (took a few minutes with a vacuum cleaner to carefully suck out as much of the dust that was caked in there), and hooked it all up. The computer starts up ok, but it needs an OS – the hard disk was wiped clean of all government secrets, as expected.

I downloaded Oracle Enterprise Linux release 5 from OTN [link]. I choose the x86 32 bit version, Release 5 Media Pack. It comes as five ISO images (ignoring the four Source ISOs), which I burned onto five CDs (just happened to have some blanks lying around).

Pop the first CD into the tray and hope nothing’s broken. It works! It’s alive!

Oracle Enterprise Linux welcomes me into its world and I recklessly accept all the defaults. It’s successfully detected all the hardware, and after the last CD has been inserted the transformation from boring ex-government PC into a shiny clean Linux box is complete.

A bit of a brush up on linux commands (Guide to Linux File Command Mastery), and now it’s play time!

[Next]


Dirt Cheap Oracle step #1 of 6: The Machine

[Previous]

My main computer, used by my wife as well, runs XP and I want to remove Oracle from it because it takes too long to boot up, and Oracle can suffer when I run CPU-intensive applications on it.

In spite of my asking very nicely, none of my friends, family, in-laws, or colleagues had any old computers lying around they didn’t need, so I’ve done a bit of shopping around. An ad in the local paper pointed me to McLernon’s Supply & Demand, an auction house which sells heaps of old office furniture and computer equipment. They had just stacks of secondhand computers, mostly from DOLA (Department of Land Administration) (who, coincidentally, I once worked for), and I picked up a good PC for $99. It had 512MB of RAM so I also got a second half-gig for $50.

This is the hardware manifest:

  • 1 x Intel P4 2.0GHz, with 512MB RAM, 40GB HD, onboard video & network: $99.00
  • 1 x 512MB RAM: $50.00
  • Network cable: free (was in my box of computer junk, left over following an upgrade of my router)
  • DSL-G604T 4-port+wireless ADSL Router: $86.00 (secondhand via eBay)
  • Labour: free (me)

It’ll use my existing broadband to access the Internet. I’m on a $49.95/month plan from iiNet.

TOTAL COST: $235 upfront, plus the monthly broadband cost.

(not mentioned: a KVM switch, dirt cheap for $35, just for convenience while setting it up)

From here on in, there’ll be no further outlays; free software only!

[Next]


Dirt Cheap Oracle

Seeing how Oracle XE and Linux is free, I want to see for just a minimum of outlay I could get a working database and web server running. I am also keen to give Linux a try; I’ve been a Windows user ever since my dad upgraded his computer from MS-DOS 3.3 to Windows 3.1, and while I’ve always worked for companies that used Unix I’ve never really had to learn much about it.

My goal is to get Linux up and running on the cheapest hardware I could find (free, if possible), and to run Oracle XE, the free version of the Oracle database, which includes Application Express. On the same machine I want to set up a web server to serve static web pages and streaming media.

My plan is as follows:

  1. Obtain a barebones machine to be the server. Must be free, or dirt cheap.
  2. Install Oracle Enterprise Linux on it.
  3. Install Oracle XE 10g R2.
  4. Upgrade APEX to version 3.0.
  5. Set up the router for external access.
  6. Migrate all Apex applications from the Win XP box.

In the next few posts I’ll describe how I went on all six points.

[Next]


Which APEX am I logging into?

I’ve got two computers at home both running APEX, and when I log into APEX the page looks exactly the same – except for the IP address in the URL, which is just slightly different. If you’re forgetful like me it’s easy to confuse which machine I’m logged in to. Here’s a tip:

Login to apex_admin on each machine in turn and click Manage Service, then Messages. Then select a Custom Message for the Login Message and/or a System Message, and enter the name of the machine, or some other message that will remind you which machine it is. The message will be shown whenever you see the login screen, and when you login to APEX.

If you find the text isn’t visible enough, you can insert HTML into the message, e.g. <B>Development</B>.


APEX 3.0 via Embedded PL/SQL Gateway

I managed to get APEX 3.0 working on Oracle 10.2.0.1 using the embedded PL/SQL gateway (i.e. without installing Apache), contrary to the advice given here. I used apex_epg_config instead of apex_epg_config11. For it to work, however, I had to remove some security on XDB so it’s not a recommended option. This involved modifying my xdbconfig.xml to allow anonymous access to XDB using this:

DECLARE
  configxml SYS.XMLType;
BEGIN
  SELECT INSERTCHILDXML(xdburitype('/xdbconfig.xml').getXML(),
         '/xdbconfig/sysconfig/protocolconfig/httpconfig',
         'allow-repository-anonymous-access',
         XMLType('true'),
         'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
  INTO configxml FROM DUAL;
  DBMS_XDB.cfg_update(configxml);
END;
/

Now for some general tips:

Tip #1. List Your DAD Authorizations

As described so well in the excellent article DBMS_EPG – The Embedded PL/SQL Gateway in Oracle 10g Database Release 2, you can authorize and deauthorize a DAD using the DBMS_EPG package. It should be noted that dropping a DAD will not remove the authorizations as well, so you need to deauthorize it as well. To find out what authorizations have been made on your database, you can query the DBA_EPG_DAD_AUTHORIZATION view, e.g.:

SQL> select * from dba_epg_dad_authorization;

DAD_NAME USERNAME
-------- ---------
APEX     ANONYMOUS

1 row selected.

There’s also a USER_EPG_DAD_AUTHORIZATION view which shows the DAD_NAME for authorized DADs for the current user.


Tip #2. List Your DADS

You can find out what DADs have been set up on your instance using the DBMS_EPG package. There is a bug, however, that causes dbms_epg.get_all_dad_attributes to return nothing, so this script gets each attribute separately with dbms_epg.get_dad_attribute. The script lists all the DADS, their mappings and their attributes.

set serveroutput on

PROMPT list all dads
declare
  blank       dbms_epg.varchar2_table;
  dad_names   dbms_epg.varchar2_table;
  paths       dbms_epg.varchar2_table;
  procedure show_dad_attribute(dad in varchar2, attr in varchar2) is
    val varchar2(4000);
  begin
    val := dbms_epg.get_dad_attribute(dad, attr);
    if val is not null then
      dbms_output.put_line('... ' || attr || '=' || val);
    end if;
  end;
begin
  dbms_epg.get_dad_list(dad_names);
  if dad_names.count > 0 then
    for i in dad_names.first..dad_names.last loop
      dbms_output.put_line(dad_names(i));
      paths := blank;
      dbms_epg.get_all_dad_mappings(dad_names(i), paths);
      if paths.count > 0 then
        for j in paths.first..paths.last loop
          dbms_output.put_line('... path=' || paths(j));
        end loop;
      else
        dbms_output.put_line('... No mappings found');
      end if;
      show_dad_attribute(dad_names(i),'after-procedure');
      show_dad_attribute(dad_names(i),'always-describe-procedure');
      show_dad_attribute(dad_names(i),'authentication-mode');
      show_dad_attribute(dad_names(i),'before-procedure');
      show_dad_attribute(dad_names(i),'bind-bucket-lengths');
      show_dad_attribute(dad_names(i),'bind-bucket-widths');
      show_dad_attribute(dad_names(i),'cgi-environment-list');
      show_dad_attribute(dad_names(i),'compatibility-mode');
      show_dad_attribute(dad_names(i),'database-username');
      show_dad_attribute(dad_names(i),'default-page');
      show_dad_attribute(dad_names(i),'document-path');
      show_dad_attribute(dad_names(i),'document-procedure');
      show_dad_attribute(dad_names(i),'document-table-name');
      show_dad_attribute(dad_names(i),'error-style');
      show_dad_attribute(dad_names(i),'exclusion-list');
      show_dad_attribute(dad_names(i),'fetch-buffer-size');
      show_dad_attribute(dad_names(i),'input-filter-enable');
      show_dad_attribute(dad_names(i),'info-logging');
      show_dad_attribute(dad_names(i),'owa-debug-enable');
      show_dad_attribute(dad_names(i),'max-requests-per-session');
      show_dad_attribute(dad_names(i),'nls-language');
      show_dad_attribute(dad_names(i),'path-alias');
      show_dad_attribute(dad_names(i),'path-alias-procedure');
      show_dad_attribute(dad_names(i),'request-validation-function');
      show_dad_attribute(dad_names(i),'session-cookie-name');
      show_dad_attribute(dad_names(i),'session-state-management');
      show_dad_attribute(dad_names(i),'transfer-mode');
      show_dad_attribute(dad_names(i),'upload-as-long-raw');
    end loop;
  else
    dbms_output.put_line('No dads found');
  end if;
end;
/

When the above script is run, you’ll get something like this:

APEX
... path=/apex/*
... authentication-mode=Basic
... database-username=ANONYMOUS
... default-page=apex
... document-path=docs
... document-procedure=wwv_flow_file_mgr.process_download
... document-table-name=wwv_flow_file_objects$
... nls-language=american_america.al32utf8
... request-validation-function=wwv_flow_epg_include_modules.authorize

APEX Tip: Page Auto Refresh

This tip provides your users with the option of choosing a refresh interval for the page. The following steps were tested with APEX version 2.2 but should work on earlier versions of APEX or HTMLDB.

  1. Create an Application Item to store the current refresh interval (e.g. F100_REFRESH_INTERVAL)
  2. Create an Application Computation to initialise it (e.g. F100_REFRESH_INTERVAL_COMP):
    • Computation Item = F100_REFRESH_INTERVAL
    • Computation Point = On New Instance (e.g. On Login)
    • Computation Type = Static Assignment
    • Computation = (default number of seconds, e.g. 60)
  3. Create a static List of Values (e.g. REFRESH_INTERVAL):

Display – Return
1 sec – 1
5 sec – 5
10 sec – 10
30 sec – 30
1 min – 60
5 min – 300
(you can change this list how you like, as long as the return values are positive integers)
(it is probably not a good idea to provide zero seconds as an option, as this will cause the page to continually refresh without giving the user much chance to intervene)

Now, for each page you wish to have auto-refreshed, edit the Page Properties:

  • HTML Header = <meta http-equiv="refresh" content="&F100_REFRESH_INTERVAL.">

Somewhere in your application you will want to provide the user with a way of modifying the refresh rate. I prefer to provide this on the same page that is auto-refreshed.
Create an Item (e.g. P1_REFRESH):

  • Display As = Select List with Submit
  • Region = (any region, I prefer the Breadcrumb region if one exists)
  • Label = Refresh
  • Source Used = Always, replacing any existing value in session state
  • Source Type = Item (application or page item name)
  • Maintain session state = Per session
  • Source value or expression = F100_REFRESH_INTERVAL
  • List of Values definition = REFRESH_INTERVAL

I also like to show the date/time when the page was last refreshed. To do this, I just add a display-only text item to the page with:

  • Source Used = Always, replacing any existing value in session state
  • Source Type = PL/SQL Expression or Function
  • Maintain session state = Per session
  • Source value or expression = TO_CHAR(SYSDATE,'Dy DD Mon HH24:MI:SS')

Now, the page will be auto-refreshed according to the default interval, and the user can change the refresh interval using the select list.

You can add the item and the auto-refresh header to any page you like. If you need a different refresh interval on different pages, you will need to create additional Application Items, along with their own Application Computations to initialise them.

If the user hits the “Stop” button in IE, the page seems to stop auto-refreshing. I don’t know if this feature works the same in other browsers, however.


Learning APEX

I’ve been enjoying learning Oracle APEX the last few weeks, and have started to appreciate the depth of the product – there’s more than meets the eye. At first I was dependent on the wizards (of which there are many) for just about everything except SQL and PL/SQL. When I came across problems I’d just delete and re-create the entire page – but now I’m more often able to find the source of a problem and fix it. I’ve found that having a good knowledge of SQL, PL/SQL, HTML and Cascading Style Sheets is very helpful when working with APEX.


Upgraded to HTMLDB 2.0

Upgraded from 1.6. I’ve already done some playing on 2.0 on htmldb.oracle.com, already liked the little interface and management improvements.

I’m not too interested in all the bells and whistles – I just want to see first if I can produce usable maintenance forms for my preferred flavour of tables. I’ve been using the wizards to create all my pages, but have come across problems whenever the tables use natural PKs (specifically, when I try to make master-detail pages) – so far most problems have been resolved by adding a PK populated by a sequence from a trigger. Not what I really want to do; I’m hoping that I can find a way to get HTMLDB to work with natural-pk tables.

The main disadvantage to using surrogate keys I’ve found is practical – apart from the obvious one that I have to ensure that a unique constraint is on the natural key (just have to be careful to do it) – but then these surrogate keys get propogated throughout my design (especially when I have modelled multiple inheritance with table-per-subclass, which is my personal preference) and writing queries and certain types of constraints can become tricky.

So far my impression of HTMLDB is that it is a nice simple product – but hopefully not so simplistic as that it requires surrogate keys everywhere?

Another problem that I’ll investigate is HTMLDB’s behaviour in regards to checking for record changes prior to updates; I’ve gotten the “can’t update because the hash values differ” error quite often when I’ve based a page on an updateable view. I use updateable views a lot as a way of hiding the database complexity from the application, and I don’t want to have to (a) denormalise my design just to suit HTMLDB, or (b) try to design HTMLDB to work with each entity separately.


Experimenting with Oracle

I’ll post here results from some experiments I’m doing with Oracle. I want to explore the implications and possible benefits of mapping a truly relational logical database model directly to the physical model. In other words:

  1. Every entity mapped to a “base table”
  2. Every base table has one or more Unique keys (one arbitrarily chosen as “Primary”)
  3. No surrogate keys, except where the model doesn’t include any natural key
  4. All columns mandatory
  5. Application interfaces via fully updateable views (materialized or not)

In particular, I would like to determine how (if possible) to implement various types of table and database constraints apart from the standard PK and FK constraints.

Initial experiments indicate most of this is very possible, and with careful coding can be made to work. In fact, I’m sure some advantages over common practice will be demonstrated.

I’m doing all this on Oracle 10g Release 2 which I downloaded recently. I’ve installed it on my P4 with XP Home. For the frontend I’m using HtmlDB 1.6, although I’ll probably upgrade to HtmlDB 2.0 soon.

I should note that I don’t seriously expect this approach to have enough merit to be used in real projects – but this is not because of a lack in the relational model of data but because of the poor support of it by the SQL standard (and therefore, by DBMS’s that implement it). Therefore, I expect that to get all this to work I’ll have to write a lot of code very carefully, but that the resulting database will be very easy to write applications and queries for. For example, implementing all constraints on the database for a particular entity means that I don’t need complicated constraint checking code in every module that works with that entity.

I expect I may have to wrestle with row locking and handling constraint violations correctly in the presence of multiple users/sessions; I don’t want to have to enforce serialized access to get this to work because of the scalability problem.

I’ve found it interesting so far, anyway.