Install APEX receipe

How to install APEX - shorthand.

Prepare the database

There are some options and tools to be installed. If you use the DBCA to generate scripts, make sure you at least run these:

@$AH/scripts/CreateDB.sql
@$AH/scripts/CreateDBFiles.sql
@$AH/scripts/CreateDBCatalog.sql
@$AH/scripts/JServer.sql
@$AH/scripts/context.sql
@$AH/scripts/xdb_protocol.sql
@$AH/scripts/postDBCreation.sql

$AH stands for my Administrative Home directory; usually something like $ORACLE_BASE/admin/{SID}

Check

Not sure? You should at least get the following three, when running this:

select comp_id from dba_registry;

COMP_ID
------------------------------
  XDB
  CONTEXT
  JAVAVM

owa_util

select owa_util.get_version from dual;

should result in 10.1.2.0.6 or higher. It will need to get patched/upgraded if not. You can safely execute owainst.sql in the owa sub directory of the installation, as the OWA installation checks the version, and only patches if there's something to patch

shared_pool

Only when not using SGA_TARGET (10g and 11g) or MEMORY_TARGET (11g), define shared_pool_size 100MB or higher.

Install

Now, you are ready to install:

@apexins sysaux sysaux temp /i/

Don't forget the last "/"... Change the password of the public account, and unlock the account:

alter user apex_public_user identified by ABC123DEF account unlock;

The OHS

Assuming a MS Windows workstation, zip the images directory of the apex install. Transfer that to your OHS (Oracle Http Server). Now, create a directory and unzip:

$ mkdir $ORACLE_BASE/htdocs/apex
$ cd $ORACLE_BASE/htdocs/apex
$ unzip /.../apeximg.zip

plsql.conf

Add this entry to plsql.conf ($ORACLE_BASE points to /oracle/apex):

PlsqlMaxParameters 15000

dads.conf

Add this dads.conf:

Alias /i/ "/oracle/apex/htdocs/apex/images/"
AddType text/xml xbl
AddType text/x-component htc
<Location /demo>
   SetHandler pls_handler
   Order deny,allow
   Allow from All
   AllowOverride None
   PlsqlDatabaseUsername apex_public_user
   PlsqlDatabasePassword   ABC123DEF
   PlsqlDatabaseConnectString cs-frank03.home.local:1521:orcl.home.local ServiceNameFormat
   PlsqlNLSLanguage American_America.UTF8
   PlsqlAuthenticationMode Basic
   PlsqlSessionStateManagement StatelessWithFastResetPackageState
   PlsqlDocumentPath docs
   PlsqlDocumentProcedure wwv_flow_file_mgr.process_downloadd
   PlsqlDocumentTablename wwv_flow_file_objects$
   PlsqlDefaultPage apex
   PlsqlAlwaysDescribeProcedure Off
   PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
</Location>

Obfuscate password

It is not a good practice to leave cleartext passwords; obfuscate it:

cd $ORACLE_HOME/ohs/modplsql/conf
export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib
export PATH=$ORACLE_HOME/ohs/modplsql/conf:$ORACLE_HOME/perl/bin:$PATH

perl dadTool.pl -o

$ Error: Could not obfuscate password 'ABC123DEF' for dad '/demo'
Number of failed obfuscations : 1

Nevermind - it is. Just check it. You're almost done, just make OHS aware of all changes:

$ opmnctl restartproc type=ohs

You are now ready to add workspaces and users, like I've done here.

OHS2 on Ubuntu (error loading shared libraries)

Trying to install the Oracle HTTP Server powered by Apache 2, I got the following errors:

/oracle/as/10.1.3/ohs/bin/apachectl startssl: execing httpd
/oracle/as/10.1.3/ohs/bin/httpd: error while loading shared libraries: libgdbm.so.2: cannot open shared object file: No such file or directory

That can be resolved by:

sudo ln -s /usr/lib/libgdbm_compat.so.3.0.0 /usr/lib/libgdbm.so.2

The next error on the opmn http log will be:

/oracle/as/10.1.3/ohs/bin/apachectl startssl: execing httpd
/oracle/as/10.1.3/ohs/bin/httpd: error while loading shared libraries: libdb-3.3.so: cannot open shared object file: No such file or directory

In order to resolve that, execute:

sudo ln -s /usr/lib/libdb-4.7.so /usr/lib/libdb-3.3.so

Oracle 10G R2 on Ubuntu 9.10 (ins_rdbms.mk link error)

Installing Oracle 10G release 2 on Ubuntu 9.10 (Karmic Koala) is pretty much the same as installing on 8.04, except for the extra packages needed. In this entry, I used

apt-get install build-essential libaio1 gawk ksh libmotif3 alien libtool lsb-rpm libstdc++5

Problem with 9.10 is: libstdc++5 has been fased out and superseeded with libstdc++6.
When you try to install nevertheless, you'll get:

Package libstdc++5 is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source
E: Package libstdc++5 has no installation candidate

Trying to go along with the installation will get you:

Error in invoking target 'all_no_orcl' of makefile 'ins_rdbms.mk'.

Workaround

The solution is simple: create a link to the V6 library:

sudo ln -s /usr/lib/libstdc++.so.6.0.13 /usr/lib/libstdc++.so.5

Fast!

I am, by the way, impressed about the speed of this install. I have the impression, this ext4 filesystsem is a lot faster than the 2-disk lvm I had on CentOS.

Reaching out: the database as Web-browser

Many people may not know of the possibility to use the database as browser. Oracle dumped the RDBMS as database since the introduction of Oracle 8i, and made it some Jack-of-all-Trades. The browser capabilities were introduced even earlier, in release 7.3.3.

Why?

Indeed, you may want to ask: why? What use could this possibly have? Well, for one: gathering statistics about something you do not have direct access to. The weather, for example. Combined with PHP, you could deliver very sophisticated graphs on temperature, rain, moisture, etc, etc. In fact, I used it for such an application log time ago: prevent firefighters from getting asphyxiated by smoke from chemical fires by plotting a safe route to the fire.

METAR

OK, in order to have firefighters use safe access roads, I need to know the current wind direction - how to get that? Luckily, there's something called METAR (flyboy speak for Meteorological Aerodrome Report), and all (military) airfields provide one, as well as all automated weather stations, (weather-)ships and buoys.
The beauty of METAR is that it is released every half-hour, 24*7*365. I suspect the background of METAR was telex; these messages were distributed by telex, later by teletype machines. Just look how there pages are formatted:

ZCZC
SA171155 EHFS
METAR EHFS 171155Z AUTO 26015KT 9999NDV NCD 05/04 Q1016=

ZCZC
SA171325 EHAK
METAR EHAK 171325Z AUTO 13010KT 050V200 9999NDV SCT003/// OVC004///
04/04 Q1011=

ZCZC
SA171325 EHAM
METAR EHAM 171325Z 28015KT 9999 FEW012 07/05 Q1014 NOSIG=

Nowadays, these pages can be found on the internet. A couple of years ago, when I coded for this project, the teletype look-alike was still there. By now, those pages have to be "official", and carry links and graphical elements.

Decode

Let's take a look at a METAR, and take the last one from the previous example:

ZCZC
SA171325 EHAM
METAR EHAM 171325Z 28015KT 9999 FEW012 07/05 Q1014 NOSIG=

ZCZC is the attention signal, the (radio-)teletype "Start of Message" code. Is not a part of the METAR itself, but is a part of the communications protocol.
SA171325 EHAM SA used to signify this is a Surface Aviation observation, but the coding would differ from the METAR standard. Not sure what it signifies here. 171325 is the date (17th) and time (13:25) on which this message was released. Time is Zulu, or GMT.
EHAM is the international designation of the location, according to ICAO (International Civil Aviation Organization) standard. It just so happens to be Amsterdam Airport, Schiphol (or Ships Hell)
METAR Indicates this is METAR coded, as opposed to SA-format.
EHAM See above: designates origin.
171325Z See above: date and time (GMT)
28015KT Wind speed and direction: 15 Knots, from 280 degrees. North being 0 degrees, so this would be West-North-West-Western (WNWW), as West would be 270. Sometimes followed by G33, meaning Gusts of wind up to 33 knots.
9999 Visibility (in meters). 9999 is excellent, better than 10 Km. Sometimes entries like 1000SE 5000N: 1000 meters South East, 5000 meters in Northerly direction.
FEW012 Cloud indication: there are a few (12.5~25%) clouds from 1200 feet upwards. May be suffixed with type of cloud (like CB: cumulonimbus, or thunderstorm cloud)
07/05 Temperature readings (in Celsius): 07 is the current temperature, 05 is the thaw temperature. These can be used to deduct the Relative humidity. Possibly prefixed with the letter 'M' to indicate minus. Zero filled, two position numbers.
Q1014 Pressure in hPa. Current air pressure in Amsterdam is 1014 hPa. Zero filled, four position number.
NOSIG No Significant changes in weather. Civilian code; military use color codes.
= The End-of-Communication indicator. Used to be NNNN.

Eindhoven airport is a military airport; this is their METAR:

ZCZC
SA171425 EHEH
METAR EHEH 171425Z AUTO 27010KT 9999NDV SCT017 BKN020 07/04 Q1016
WHT WHT TEMPO BLU=

There's a nice table here, explaining the color codes.

Of course, there's much more with METAR. The internet has plenty examples, and this is always a nice start. At least, now I know where to look for wind direction, and possibly a forecast.

utl_http

In order to get the internet downloaded into the database, I use utl_http. Basically, the code consisted of only a few lines:

procedure weerrapport is
l_piece   utl_http.html_pieces;
l_url1   varchar2(80) := 'http://www.knmi.nl/product/luchtv/nedactbe.htm';
l_url2   varchar2(80) := 'http://weather.noaa.gov/cgi-bin/mgetmetar.pl?cccc=EHLE';
l_tmp   varchar2(32767);
l_line   varchar2(256);
dummy   boolean;
begin
  l_piece := utl_http.request_pieces (l_url1, 10);
  for i in 1..l_piece.count
-- Piece the lot together again...
  loop
    l_tmp := l_tmp||l_piece(i);
  end loop;
-- Discard everything not in the BODY...
     l_tmp := substr(l_tmp, instr(upper(l_tmp), '<BODY'),32676);
-- Get Deelen (EHDL) and Lelystad info (EHLE)
     into_weer(l_tmp,'EHDL');
  into_weer(l_tmp,'EHLE'); 
  commit;
end weerrapport

Lots of unsafe programming here: I simply work on the basis (not an assumption, back then!) that the page returned will not exceed 20kB. Yes - that is 20kB, not 32kB, as I only fetch 10 pieces (of 2000 byte) of HTML code. By now, the page has increased in size, but it still is about 14kB.
Nevertheless, some mechanism to handle overflows should have been incorporated.

The into_weer procedure is simply a further stripping of unnecessary text, and saving the metar to the database:

procedure into_weer(p_string IN varchar2, p_station IN varchar2)
IS
 l_line varchar2(256);
 begin
 if instr(upper(p_string),p_station) > 0
 then
   l_line := replace(substr(p_string,
                     instr(upper(p_string),p_station,1,2)
                     ,256
                    ),
     chr(10),' ');
   l_line := substr(l_line, 1, instr(upper(l_line),'ZCZC') - 1);
   begin
     insert into weer (bron, site, dag, metar) values
          ('KNMI', p_station, to_date(substr(l_line,6,6),'DDHH24MI'), l_line);
   exception
     when others then
       NULL;
   end;
 end if;
end into_weer;

Again, very bad programming: an exception handler that hides any exception: "I do not wanna know about any mishaps"

Follow-up

Of course, there could be a follow-up: how to get from METAR to ordinary spoken weather forecast, or how to get from temperature and thew point to relative humidity. May be I will.

Special Characters - notes and comments

Reason for this entry is the rather disturbing observation, you can actually get different results from one and the same script.
The scenario is running a script, that uses embedded "special" characters (an €, in this case) and is executed using either the CLI version of SQL*Plus (sqlplus.exe), or the Windowed version (sqlplusw.exe).

Code Pages

It turns out that both versions use different MS Windows code pages: the Windowed version, sqlplusw.exe, picks up the code page of MS Windows, while the CLI version uses the code page of the CLI. I ended up with different binary values of the character.
By default, these differ on all non-US installations of MS Windows!

Debugging

In the course of the blog entries, I have mentioned, but never made a list of tell-tale signs. A troubleshooting list so to say. Here's a start:

• Remember: there's always at least one way to screw up. There's no recipe, no Rule-of-Thumb, or whatever to make sure you'll never end up with weird glyphs.
  Unless you control the whole, single-user, system, that is...
  
• If you see a small diamond in web browser, start hitting your web designer/builder: the page encoding is incorrect for the characters used.
  You can change the Character Set Coding in most browsers as workaround.
• If you see inverted ('"upside-down") question marks, your Oracle infrastructure is to blame: somewhere along the line, character set conversion takes place, for which the "to-character set" does not have a glyph defined (e.g. ISO8859P1 does not have a glyph for the Euro-sign)
  

[add Apr 2011] A rather good note on this is Doc ID 158577.1 on Metalink.

Ubuntu 9.10 (and Oracle's support for it)

It's official... CentOS is exit, I'm now running Ubuntu 9.10 (or Karmic Koala).

Of course, I'll set up Oracle again, which appears to be very easy, as Oracle provided a Debian (and thus Ubuntu) repository. This should allow Debian and Ubuntu users to install Oracle by using Synaptic Package Manager. Point an shoot installs... I am curious!

Add the repository

You do have to make Ubuntu aware of the fact Oracle is a source of software, too. For that, you need to add the location of the repository to your configuration. This is accomplished by:

frank@cs-frank03:~$ sudo gedit /etc/apt/sources.list

Scroll to the bottom of the file, and add:

# Oracle stuff
deb http://oss.oracle.com/debian unstable main non-free

After that, update the libraries to allow Software Center to show Oracle, enter:

frank@cs-frank03:~$ sudo apt-get update

Now, fire up Synaptic, and click on the 'Origin' button; you should see oss.oracle.com/main listed. Point at it, and you will see XE (in two flavours) and the XE client.

Now, if only I just wanted XE...

yum

For non-Debian, there's support for yum as well; check it out!

More APEX notes

A very, very quick way of housekeeping:

BEGIN
 APEX_INSTANCE_ADMIN.REMOVE_WORKSPACE ('&workspace_name','Y','Y');
END;
/

It will drop your code, database accounts, and tablespace associated with it.

If you ever want to create a workspace, and do some basic housekeeping:

set echo off verify off
accept apex_short_name prompt 'Enter the short APEX applicaton name: '
prompt ...Creating the APEX tablespace...
--
create tablespace &apex_short_name
 datafile '/oracle//&apex_short_name.01.dbf'
 size 10m
 autoextend on
 next 5m
 maxsize 1000m
 extent management local
 uniform size 64k;
-- User and grants
prompt ...Creating user and granting rights...
--
grant connect, resource
 to &apex_short_name._owner identified by &apex_app_db_password;
grant execute on dbms_pipe to &apex_short_name._owner;
alter user &apex_short_name._owner default atblespace &apex_short_name;
alter user &apex_short_name._owner quota unlimited on &apex_short_name;
-- APEX Workspace and schema
prompt ...Creating APEX Workspace &apex_short_name
BEGIN
   APEX_INSTANCE_ADMIN.ADD_WORKSPACE (
    p_workspace => '&apex_short_name',
    p_primary_schema => '&apex_short_name._owner',
    p_additional_schemas => null);
END;
/
--
prompt ...Creating APEX Workspace administrator account...
--
begin
wwv_flow_api.set_security_group_id(
   p_security_group_id=>APEX_UTIL.FIND_SECURITY_GROUP_ID('&apex_short_name'));
end;
/

begin
wwv_flow_fnd_user_api.create_fnd_user (
 p_user_name    => '&apex_short_name._ADMIN',
 p_web_password => '&apex_short_name._ADMIN',
 p_group_ids    => '',
 p_developer_privs=> 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
 p_default_schema=> '&apex_short_name._OWNER',
 p_account_locked=> 'N',
 p_account_expiry=> to_date('201001011300','YYYYMMDDHH24MI'),
 p_failed_access_attempts=> 0,
 p_change_password_on_first_use=> 'N',
 p_first_password_use_occurred=> 'Y',
 p_allow_access_to_schemas => '');
end;
/

commit;

For a given value of "abc" for apex_short_name, this will create:

• a tablespace ABC, with an associated datafile abc01.dbf
• a database account abc_owner with a password of your choice
• an APEX workspace abc, with an associated abc_owner schema
• an APEX Workspace administrator abc_ADMIN, with a password abc_ADMIN

Mind you: APEX Passwords are case sensitive; account names are not.

RSA-768 cracked

Today I read the announcement, RSA-768 is factorized, that is, the prime numbers of which the 232-digit number consists, are known.
English, and original, announcement, and a Dutch comment.

It may have taken a lot of time, but it just shows: nothing is secret. I previously blogged about SHA1 and MD-5 being broken, and actually being used for forgery.

Nice date format

Today is (was) 54 decimal.

Special characters - part V

The end of the War of the Worlds?

Well... that remains to be seen. I predict the conclusion will be that there's always a possibility unwanted, or "wrong" characters get displayed. But when you start to understand why, my mission is accomplished. By the way - I found an excellent explanation on HTML right here

Setup

For the setup, I used the "we8" database instance (see this entry) I added the 10.1.3.3 Oracle Http Server(based on Apache 2), of which Linux version used to be downloadable off the database download page. There was a bit of work to be done to get things running. The official documentation states:
For Apache 2.0 startup to succeed, link the current libdb.so version to /usr/lib/libdb-3.3.so.

For example, if the current shared library is in /usr/lib is libdb-4.1.so, then run the following command:

cd /usr/lib
ln -s /usr/lib/libdb-4.1.so libdb-3.3.so

Database Access Descriptor (DAD)

My DAD looks simple enough:

# ============================================================================
#                     mod_plsql DAD Configuration File                       
# ============================================================================
# 1. Please refer to dads.README for a description of this file              
# ============================================================================
<Location /pls/myapp>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseConnectString    cs-frank03:1521:we8
PlsqlAuthenticationMode       Basic
PlsqlErrorStyle               ModplsqlStyle
PlsqlNLSLanguage              American_America.UTF8
PlsqlDefaultPage              scott.home
</Location>

I added my own html wrapper, and created the following procedure check if the DAD was configured correctly:

create or replace procedure home as
begin
 htp.p(format.pagestart(
p_title=>'DAD Check page',
p_text=>'DAD Checked out successfully!')
 );
end;
/

This results in a "DAD Checked out successfully!" message, signifying the http server works, the connection to the database works, and my wrapper works.

Webcode

Knowing all the basics are working, I created the following package to display the contents of the blah table:

create or replace package demochar as
procedure showblah;
prodedure editblah;
end;
/
create or replace package body demochar
as
procedure showblah
 is
 begin
   htp.p(format.pagestart(p_title => 'Demonstration of Special Characters on the Web',
    p_text => 'Welcome - contents of table is')
   );
 htp.p('</div>');
 htp.p('<div class="content">');
 htp.p('<table summary="blah" border="1" align="center">');
 htp.p('<tr><th>Character<th>Hex value');
    for i in (select b.a, dump(b.a,16) as d
            from blah b)
    loop
            htp.p('<tr>');
            htp.p('<td>' || i.a);
            htp.p('<td>' || i.d);
    end loop;
 htp.p('</table>');
 htp.p(format.pageend);
 end showblah;
 procedure editblah is
 begin
   null;
 end editblah;
begin
 DEBUG.setlevel(2);
 DEBUG.setdestination(DEBUG.destweb);
 DBMS_RANDOM.initialize(TO_NUMBER(TO_CHAR(SYSDATE, 'miss')));
end;
/

Data

The blah table is there again, and it's contents is restored:

SQL> col dump(a,16) format a20
SQL> select a, dump(a,16) from blah;

A                    DUMP(A,16)
-------------------- --------------------
€                    Typ=1 Len=1: 80
Æ                    Typ=1 Len=1: c6
ß                    Typ=1 Len=1: df

Results and observations

First of all, the UTF8, used in the DAD is standard APEX, to name one. But is it the correct one to use?
Displayed on the web, the page looks like this (Linux/Firefox):

Om MS Windows (XP) with Firefox, as well as using Konqueror on Linux, the dreaded diamond is displayed:


So - the fact that our character does not display in a web browser is depending on the browser and on what is used in the DAD. As we change the DAD characterset to be used to

PlsqlNLSLanguage American_America.WE8ISO8859P15

the page is displayed correctly in Konquerer, incorrect in Firefox on Linux, but that can be corrected by forcing the page to use the 1252 codepage (!). Firefox under MS Windows reacts the same: auto detection, Universal and ISO-8859-15 all do not work, changing the Character Encoding to West European(Windows 1252) displays the page correctly. Internet Explorer V7.0.5730.13: same story: auto detection does not work.

Now - the question arises: is this due to the coding of the page? Character sets are encoded in the header. The last screenshot is the same on all browsers/systems, and is not due to encoding settings, but purely Oracle - the DAD characterset is now WE8ISO8859P1, which indeed does not know how to map the € sign. That results in an upside-down question mark.


Windows...
After inserting the euro character in the MS Windows encoding (0xa4, or 164), all browsers manage to show correct values (that is the € sign) for that when using UTF8 in the DAD, as well as browser Character Encoding. Using WE9ISO8859P15 in the DAD, the pages will display one € correct, based on Character Encoding in the Browser:
- Western ISO-8859-15 will display € for 0xa4
- Western (Windows-1252) will display € for 0x80

Only Konqueror mangages to display the € symbol for both codes, when the DAD uses WE8ISO8859P15, and the page is explicitly coded in ISO-8859-15 (using the META element Content-type in the header):

Conclusion

There's alwyas at least one way to screw up. If it's not the database, that was fed the wrong code points, it's the browser, that does a lousy job. Konqueror does an admirable job, though. Internet Explorer as well as Firefox have the possibility to switch Character Coding, which may result in correct display of the glyphs.
UTF-8 is not always the best PlsqlNLSLanguage!