Enumeration types in PL/SQL?

In the book Oracle PL/SQL for DBAs the definition of the type BOOLEAN is shown:

SELECT text
  FROM all_source
 WHERE owner = 'SYS'
   AND type = 'PACKAGE'
   AND name = 'STANDARD' 
   AND line <5
/

TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
-------------------------------------------------------------
package STANDARD AUTHID CURRENT_USER is              

  /********** Types and subtypes, do not reorder **********/ 
  type BOOLEAN is (FALSE, TRUE);

4 rows selected.

Have you seen that kind of type definition before? I did, it looks like a Delphi enumeration type when you replace the is with = . Let's define our own type.

create package TRAFFIC is 
  type LIGHT_T is (RED, YELLOW, GREEN);
end;
/

Warning: Package created with compilation errors.

SHOW ERR

LINE/COL   TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
-------------------------------------------------------------
2,19       PLS-00505: User Defined Types may only be defined 
           as PLSQL Tables or Records                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
2,3        PL/SQL: Declaration ignored                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      

2 rows selected

Perhaps this is an enhancement request for Oracle 11g release 2?

APEX 3.0 on XE

On 29 Februari 2008 Oracle released Application Express 3.1 . With this release you can upgrade the Application Express within Oracle XE to 3.1. For more information on this topic see Oracle Database XE and Application Express 3.1 and APEX 3.1 on XE .

Oracle Database Express Edition (XE) contains Application Express (APEX2.1) version 2.1 and yesterday Oracle released version 3.0 . While reading the installation guide regarding the Oracle Database requirement I noticed the following:

Do not install this version of Oracle Application Express on Oracle Database Express Edition (Oracle Database XE).

With the previous release of APEX2.2.1 some people installed this version into their XE database (see this OTN thread: Install APEX 2.2.1 on XE is possible! I think...). So why not try to upgrade my APEX2.1 to APEX3.0 on my XE database? It really sounds like a challenge!

However installing APEX3.0 on XE is an unsupported configuration by Oracle. This does not imply it will not work but if you run into problems there is no technical support available (see this OTN thread Install Apex 3.0 on Oracle XE). Also make a backup before you start the installation. My first attempt to upgrade ended with a database where I could not login into APEX, the images where missing and when trying to find out what went wrong, a BSOD appeared. You are warned...

The installation consists of two parts: running update scripts and copying the images file into the virtual path /i/. The first script to run is apexins.sql. The good news about this script is this remark: 02/15/2007 - Remove XE check. There is no check on the database edition anymore. And the bad news is apexins.sql calls coreins.sql which contains the following code:

Rem
Rem  Set XE variable to '1' for XE installation, '0' for non-XE installation
Rem
define XE = '0'

Set the variable XE to 1 and save this script and run apexins.sql according to the installation guide.After completion of this script make sure there are no invalid objects. I have still one invalid package body WWV_FLOW_XE_CONFIG. There are no other objects calling this package, so errors can occur if this package is called with dynamic PL/SQL.

While the script is running you can copy the images into the virtual directory (see Where are the /images of Application Express in OracleXE). The total installation time was 1 hour.

Login and choose About Application Express, you should see something like this picture.

PL/SQL Test-A-Thon links

Some links concerning the Test-a-Thon.

• Not such a crazy idea after all!
• Automated Code Testing Prevails at the PL/SQL Test-A-Thon

PL/SQL Test-A-Thon

At the end of the first day of OPP2007, Steven Feuerstein organized a Test-A-Thon to promote the use of Quest Code Tester for Oracle (QCTO).

A Test-A-Thon is a contest that tests your ability to construct unit tests for PL/SQL code with a tool like QCTO.

You have a fixed amount of time to solve a number of exercises. Each exercise consists of a PL/SQL program and a number of tests with the expected outcome.

The winner is the person who successfully builds the most test cases within a certain amount of time and is able to show the test results with the test tool.

I joined in the contest and obtained a shared second place together with two other participants.The first price went to Filipe Silva from Portugal who solved four exercises and a bonus exercise within an hour. Congratulations!