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:
- Obtain a barebones machine to be the server. Must be free, or dirt cheap.
- Install Oracle Enterprise Linux on it.
- Install Oracle XE 10g R2.
- Upgrade APEX to version 3.0.
- Set up the router for external access.
- 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]
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>.
Let’s just call it Legacy because I’m not going to say what the source is. It is a single-user desktop application that after a little investigation (i.e. searching the online forum for the app) was found to be storing its data in tables readable by MS Access. I wanted to get at this data, analyze it, maybe do some smart things to it, and then present it via Apex.
Step 1: Get the data into Oracle.
Simple matter of exporting from MS Access via ODBC. At least, it was simple once I replaced the Oracle ODBC drivers with the latest download from OTN. Before that I was getting a number of annoying TNS errors.
Step 2: Transform the data.
Most of the tables are easy-to-understand normalized relational tables. One of them, however, has a column that came through as a CLOB containing strange values like this:
{\rtf1\ansi\deff0\deftab254
{\fonttbl{\f0\fnil\fcharset0 Arial;}
{\f1\fnil\fcharset0 Verdana;}}{\colortbl\red0\green0\blue0;\red255\green0\blue0;\red0\green128\blue0;\red0\green0\blue255;\red255\green255\blue0;\red255\green0\blue255;\red128\green0\blue128;\red128\green0\blue0;\red0\green255\blue0;\red0\green255\blue255;\red0\green128\blue128;\red0\green0\blue128;\red255\green255\blue255;\red192\green192\blue192;\red128\green128\blue128;\red255\green255\blue255;}
\paperw12240\paperh15840\margl1880\margr1880\margt1440\margb1440
{\*\pnseclvl1\pnucrm\pnstart1\pnhang\pnindent720
{\pntxtb}{\pntxta{.}}}
...
Now based on my knowledge of the application I knew that this column was used to store small pieces of text (typically 8 to 30 short lines), with some amount of formatting (e.g. fonts, alignment, etc.). Again the online forum came in useful in that a side comment from one of the developers (regarding a small bug undocumented anti-feature) revealed that they stored the formatted text as RTF – Rich Text Format. Should have known from the opening 6 bytes in the data.
Somewhere in these oceans of rtf codes were swimming the plain text I craved. So Googled RTF, skimmed this old RTF specification, and ended up with this admittedly poorly-performing PL/SQL, which for the 651 rows in this table, each with an RTF of average 3KB, works just well enough for my purposes. As it turned out the only RTF codes I was interested in were \fcharset and \*, both of which I used to ignore bits of text I didn’t want in my output. Oh and \par, which denotes the end of a paragraph. I can run this script once a month on the freshly exported data and apply the full weight of Oracle’s analytic capabilities on it.
The code below exemplifies the use of a pipelined function. This is not a good idea, by the way, if you want to use it in regular queries, e.g. a view. In my case, however, I only wanted to call this from within PL/SQL, and then only once a month. Because of the way parameters work with functions like this, I had to call it with dynamic SQL (execute immediate).
That was kind of fun, but I’d rather not have to deal with RTF ever again, thank you.
create or replace package myutil_rtf is
type t_v4000_table is table of varchar2(4000);
function extract_text (p_recid in number)
return t_v4000_table pipelined deterministic;
end;
/
create or replace package body myutil_rtf is
function extract_text (p_recid in number)
return t_v4000_table pipelined deterministic is
l_ch varchar2(1);
l_ctrl varchar2(4000);
l_line varchar2(4000);
l_rtf clob;
--don't output any text between
--\fcharset and closing }
l_fcharset boolean := false;
--increments for each enclosed pair of { }
--within a discard section
l_discard number;
begin
select rtf_clob into l_rtf
from rtf_table where recid = p_recid;
for i in 1..dbms_lob.getlength(l_rtf) loop
l_ch := substr(l_rtf,i,1);
if l_ch = '}' then
if l_fcharset then
--closing } found; re-enable output
l_fcharset := false;
l_line := null;
end if;
if l_discard > 0 then
l_discard := l_discard - 1;
if l_discard = 0 then
l_discard := null;
end if;
end if;
elsif l_ch = '{' then
if l_discard is not null then
l_discard := l_discard + 1;
end if;
elsif l_ch = '\' then
--controls start with a backslash
l_ctrl := '\';
elsif l_ctrl is not null then
--controls are always ended by some
--non-alphanumeric character
if instr('abcdefghijklmnopqrstuvwxyz'
|| '0123456789',lower(l_ch)) > 0 then
l_ctrl := l_ctrl || lower(l_ch);
else
if l_ctrl = '\par' then
pipe row (l_line);
l_line := null;
elsif substr(l_ctrl,1,9) = '\fcharset' then
l_fcharset := true;
elsif l_ctrl || l_ch = '\*' then
--{\* ... } means you can ignore
--anything between the { }
if l_discard is null then
l_discard := 1;
end if;
end if;
l_ctrl := null;
end if;
elsif l_ch not in (chr(10), chr(13), '{')
and not l_fcharset and l_discard is null then
l_line := l_line || l_ch;
end if;
end loop;
if l_line is not null and not l_fcharset then
pipe row (l_line);
end if;
return;
end extract_text;
end myutil_rtf;
/
To extract the text from the table with recid=1:
select column_value line_of_text
,rownum line_number
from table(myutil_rtf.extract_text(1));
I just noticed the difference – I was wondering why I couldn’t see the virtual columns (i.e. columns that support function-based indexes) on a table, and it was because I was querying USER_TAB_COLUMNS. Once I used USER_TAB_COLS instead there they were.
The difference is noted in the documentation (Oracle 10g Release 2 (10.2)) for USER_TAB_COLS and ALL_TAB_COLS but not for USER_TAB_COLUMNS or ALL_TAB_COLUMNS, unfortunately.
I was annoyed that to get to my main PL/SQL page I have to type in a full URL like this:
http://host:7777/mydad/home
(e.g. “mydad” could be “apex” for Oracle Apex)
If I was using Apache HTTP Server I’d do something this article suggests. But I’m using Oracle’s Embedded PL/SQL Gateway.
A. I got rid of the “:7777” by changing the HTTP port to 80, e.g.:
SQL> exec dbms_xdb.sethttpport(80);
Now, I can get to it without the port number:
http://host/mydad/home
B. Now I want to remove the need to remember to type “home”. To do this, I just tell the DAD what the default page is:
SQL> exec dbms_epg.set_dad_attribute('MYDAD','default-page','home');
Now, the url is a bit simpler:
http://host/mydad
The URL is now rewritten automatically to point to “mydad/home”.
C. Finally, I want to remove the need to specify the DAD. To do this is a little more complicated. I’ll create an XDB resource that will override the default XDB navigator that comes up.
-
- Log into Enterprise Manager
-
- Open the “Administration” tab and select “Resources” under “XML Database”
-
- Click “Create” and set the fields as follows:
Owner = SYS
Name = index.html
Location = /
Type = XML Database Resource File
Source = Specify the file contents
Contents =
<html><head><meta http-equiv="REFRESH" content="0; URL=http://host/mydad"></head><body><a href="http://host/mydad">Home</a></body></html>
- Click “Ok”
(you’ll need to change “host” and “mydad” to appropriate values in the sample Contents above)
Now, the url is simply:
http://host
This causes it to load the index.html page from the XML database, which redirects to the DAD, the default page for which is “home”.
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
I’ve had a few blogs in my head the last few months but haven’t had a chance to write them up. This is partly affected by the release cycle at work, and some extra-curricular volunteer work, but mostly by the birth of our second child.
[Warning: the next paragraph is not Oracle-related – skip ahead to the following paragraph if you like…]
Daniel’s arrival has probably affected our home routine more than Chloe’s did; at least with Chloe there was only one child requiring attention at any given moment; now there’s two kids screaming and during the week Daddy’s away at work for 10+ hours a day, so Rosalie’s got to handle it all by herself. The pressure isn’t helped much when one or more of us falls sick; last week Chloe and I were beset with headaches and fever, and while we’re almost recovered now, Daniel has started getting blocked up and having trouble sleeping. To top it all off Rosalie’s a bit under the weather as well. The cold and wet weather here in Perth has probably been a contributor – we’ve had temperature ranges as low as 8-17 degrees (Celsius)!
Yet another contributor to my lack of blogging activity was a fair amount of involvement in the State Youth Games earlier this month; this took a lot of my time, especially throughout May. Soon, I’ll blog about the Oracle APEX-driven web site I created for our team, partly on my non-Oracle blog [EDIT: since removed] and on this blog. The most interesting part of that was learning how to allow connections directly to my http server at home.
With all that excitement, I’ve managed to mostly keep up with all the blogging out there, which was massively helped with my home-grown RSS reader. It is also an Oracle APEX-driven web site for my private use, it regularly polls all my RSS feeds, downloads them to my computer, and keeps track of which ones I’ve read yet. RSS items that have enclosures (e.g. Rocketboom) are downloaded as well, so when I’m ready to watch or listen to them there’s less delay. At the moment I’ve got some hard-coded rules that automatically mark some items as “not interesting”, but eventually I hope to implement some kind of guided learning so that it will be able to mark new items as “probably interesting” or “probably not interesting” based on my feedback on prior items. Even with the hard coded rules, however, it cut down my reading time by about 50% which is nice. A few months ago the database had over 1,000 unread items; over the last few weeks I’ve managed to whittle that down quite a bit, and when I was sick I read the last few so now I’m fully up to date – e.g. Oracle 11g is on its way! 🙂 I can barely wait to get that installed.
That’s the State Of My Blog(s). I hope you enjoyed that because I normally avoid blogging-about-blogging like the /*TODO: insert cliche*/.
Zettabyte File System (APC)
Pooled storage, transactional control, snapshots… sounds awfully familiar to me. Oh yeah, features that Oracle have provided in the database for years. 🙂
(I know, very different domain and probably very different implementation (database vs. file system) but some of the concepts are similar.)
“Applications Developer – desirable skills: Soccer or Tetrinet”
Job: Applications Developer
Location: Perth – South
Advertiser: Kwinana Software Company
Classification: I.T. & T > Analyst/Programmer
Description: Highly-regarded development house. Build broad & deep skills in Microsoft, Oracle and industrial technologies. Soccer or Tetrinet skills wouldn’t hurt
Link: http://it.seek.com.au/users/apply/index.ascx?Sequence=13&PageNumber=1&JobID=9618584&cid=jobmail
“We have comfy chairs, grunty machines and twin 22” wide-screens on every desk (with webcams, for Skyping between offices).”
Wow.
I came across an inexplicable error when bulk collecting into a PL/SQL table with the NOT NULL constraint the other day. What was confusing was that the code had been passing tests for quite some time.
In the end the only thing that had changed was that a VARCHAR2 which should have been non-null happened to be NULL for one particular row in the table.
Thanks to Connor for the simple test case, listed below.
If you know what might be the cause or reason behind this error, and why it doesn’t occur for dates, I’d be interested.
This was reproduced on Oracle 10.2.0.1.0.
SQL> declare
type t is table of number not null index by pls_integer;
r t;
begin
select case when rownum < 20 then rownum else null end
bulk collect into r from all_Objects
where rownum <= 20;
end;
/
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define
ORA-06512: at line 5
SQL> declare
type t is table of varchar2(80) not null index by pls_integer;
r t;
begin
select case when rownum < 20 then rownum else null end
bulk collect into r from all_Objects
where rownum <= 20;
end;
/
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define
ORA-06512: at line 5
SQL> declare
type t is table of date not null index by pls_integer;
r t;
begin
select case when rownum < 20 then sysdate else null end
bulk collect into r from all_Objects
where rownum <= 20;
end;
/
PL/SQL procedure successfully completed.
