OraTransplant

Jaap Poot started blogging

One of our DBA’s, Jaap Poot, has started blogging actively. Jaap will describe the challenges he faces during his daily job as an Oracle Database and Application Server administrator at Eurotransplant.

Jaap has always been a pleasure to work with both at Eurotransplant and our former joined employer. He has a wide range of knowledge about all sorts of techniques and systems (Oracle database, application server, Unix/Linux, networking, Windows, hardware, etc). He’s one of the few people I know that have such a thorough knowledge of these diverse techniques. This makes him a crucial player in introducing new technology at Eurotransplant.

Using Proxy Authentication with SQLPlus

For our web application we’re using a nice Oracle database feature: Proxy Authentication. I only managed to use it in a J2EE application, not in sqlplus or other SQL tools (like TOAD). Today I discovered how to use Proxy Authentication from any tool you like.

First let me explain a little bit about Proxy Authentication. Our application server maintains a pool of database sessions, as most application servers do. This means that each session from this pool uses the same (fixed) database user to connect to the database. Looking in V$SESSION you see tons of sessions from the same user.

This makes it difficult to distinguish the sessions for a DBA. But more importantly you loose a lot of security functionality in the database:

• SELECT USER FROM DUAL now always returns the same username. This means that views restricting the returned rows based on the currently logged on user no longer work as expected
• Journaling (writing changes to journal tables from database triggers) loses its value since all changes appear to have been made by the same user
• Auditing (insert_user/update_user columns in a table) loses its value since all changes appear to have been made by the same user
• You can no longer easily use database roles to limit access. The user being used for the database connection needs all the roles of all the application users combined. And by default all these roles are enabled when creating a new database connection, unless you add specific code to enable/disable specific roles

The answer to all of this is Proxy Authentication. This process is quite simple. The application server first establishes a connection to the database with the same fixed account. This account is configured to only allow database connections (CREATE SESSION privilege) and nothing else. Once this session is established you can open a proxy connection to another user. This other user is the actual user logged on to the web application (e.g. JSMITH). This immediately gives you all the built-in security features of the database (grants, journaling, auditing, etc).

This is all great, but to open a proxy connection you have to issue an OCI or JDBC call. You can do this from programs you code yourself, like or ADF Faces applications. But for testing and debugging, you also want access to this feature from sqlplus or other SQL tools (like TOAD).

Fortunately this feature was introduced in version 10.2. When connecting, specify “user[proxyuser]” as username. This will try to log you in as “user” and then immediately open a proxy connection to “proxyuser”:

C:\>sqlplus bigappuser[enduser]/bigappuser@dev01

The same trick also works when logging in with other tools like TOAD. Be sure to use an Oracle Net client version 10.2. If you use 10.1 or any previous version this won’t work.

Update 19 dec 2006: Jonathan Lewis blogged about this very same feature of using Proxy Authentication with SQLPlus

PL/SQL enhancements in version 11

I was just browsing the session catalog for Oracle OpenWorld 2006 Develop to see whether it is worth while to visit. I stumbled upon a session by Bryn Llewellyn, PL/SQL product manager from Oracle entitled “PL/SQL Enhancements in the Next Major Release of Oracle Database”.

The session abstract itself already gives a good idea of the new features he will be talking about. It sounds very promising:

Native compilation now requires no C compiler, because the PL/SQL compiler generates the machine code DLL directly. The file system directory is no longer needed, and only the PLSQL_Code_Type parameter remains.

A brand-new systemwide function result cache speeds performance, by avoiding expensive recalculation. Declarative mechanisms are provided to purge a cached result when, for example, table data it relies on changes.

Dependencies are now tracked at the level of the element (subprogram, variable, type declaration, cursor, and so on) in a package or the column in tables. A PL/SQL unit that depends only on elements that are not changed is now not invalidated when other elements are changed. This has the added benefit that the frequency of ORA-04068 errors in a multiuser environment is reduced.

Through Oracle Database 10g, there are some gaps in PL/SQL’s method 4 dynamic SQL functionality. These are now filled. For example, a DBMS_Sql cursor that was opened for a select statement whose select list or bind requirements are not known until runtime can now be converted to a ref cursor to return to a client-side application.

The new compound trigger allows code to be defined that fires at “before statement,” “before each row,” “after each row,” and “after statement” times in a single compilation unit. The unit can declare global state that lasts just for the duration of the firing SQL statement.

These are just a few of the PL/SQL enhancements brought by the next major release of Oracle Database. In this session, you learn about them all.

Our patch and upgrade policy

Eurotransplant allocates organs available for donation in 6 European countries. Whenever an organ donor is available in any of these six countries, Eurotransplant is notified. We collect data about that donor and start the matching to all recipients on the waiting list. This process relies heavily on IT. It’s also a very time critical process and has to be available 24×7, as you can imagine. It can be a matter of life and death for the recipients on the waiting list. That’s why we cannot take a lot of risks with installing new patches and upgrades to our (Oracle) software.

We release a new version of our system every 1.5 to 2 months. During that period, changes to the system are continuously tested. Developers finish the release at least one week before the release date. That last week is used for final testing of all critical components.

With this in mind, we defined our policy for patches and upgrades to the Oracle software. Let’s start with one-off patches. Upgrade and patch sets have to be installed on the test and development environments before the start of the next development cycle. If all goes well, the upgrade or patch set is installed on production at the end of the release cycle, when the new release goes live. So, all tests and the final critical test are done with the patch set or upgrade in place.

Larger upgrades might take even longer and will have separate test environments. A good example was our recent upgrade from database 8.1.7 to 10.2.0. Developing and testing this upgrade was so much work that is was done outside the normal development cycles.

We’re a bit more flexible with installing one-off patches. These patches are limited in scope and thus less risky than full upgrade or patch sets. Also, these tend to be more time critical since they target specific bugs we’re suffering from. A one-off patch also gets installed on test/development first. It doesn’t really matter when it’s installed as long as it is before the start of the final week of critical tests. That way the one-off patch can be installed on the production environment with the next release.

In very exceptional circumstances we can decide to install a one-off patch on production outside normal release dates. But we would only do that if we’re hitting a truly critical bug.

Commit time business rules

This post will explain two things. First is how to setup a business rule that needs to be checked at commit time and no sooner. The other thing is preventing confusing error messages in Oracle Forms when you do so. By default, Oracle Forms will first inform the user that the records where applied and saved followed by an error message about a failing commit.

We wanted to implement a business rule that has to be valid at commit time and no sooner. A well known example for this is a master record that must have at least one detail record. You cannot check this in normal triggers. When inserting the master record there are no detail records yet. The only correct moment to check these kind of rules is at the commit moment.

You can use a trick with a deferred check constraint to accomplish this. You create a column in a table. On that column you place a deferred check constraint. Then you create some database triggers to maintain the value of that column. Make sure the initial value of that column violates the check constraint. Since the constraint is initially deferred it won’t be checked until commit time. Make sure that some database triggers modified the column to a valid value before the commit moment. Read on for a full example. You can also read about the concept in the Oracle Database Concepts guide

That wasn’t too hard, but it led to a problem in Oracle Forms. Oracle Forms would wrongfully notify the user that the records were saved. Immediately after that notification Forms would raise an error saying the commit has failed. That’s very confusing to a user, so we had to work around that too. Keep on reading for that full example.

Let us begin with setting up a simple example of a commit time business rule. In this case I’m creating a master and detail table and I want to make sure that each master record has at least one detail record. First create a Master and Detail table:

create table tab_master (
  id  number(10),
  constraint tab_master_pk
 primary key
 (id));

create table tab_detail (
  id         number(10),
  master_id  number(10),
  constraint tab_detail_fk
 foreign key (master_id)
 references tab_master (id));

Add a column to the master table which we will use to enforce the business rule:

alter table tab_master add (num_details number(10,0));

Now add appropriate triggers to both the master and detail tables to maintain the column in the master table:

-- Note: this is just a concept, and by no means production
-- grade code (e.g. you should also handle things like
-- updating the FK column)
create trigger tab_master_bir
before insert on tab_master for each row
begin
  -- always force the number of details to 0 on an insert
  -- even if the num_details is specified in the insert
  :new.num_details:=0;
end;
/

create trigger tab_detail_air
after insert on tab_detail for each row
begin
  -- increase the detail count in the master record
  update tab_master
  set num_details=num_details+1 where id=:new.master_id;
end;
/

create trigger tab_detail_adr
after delete on tab_detail for each row
begin
  -- decrease the detail count in the master record
  update tab_master
  set num_details=num_details-1 where id=:new.master_id;
end;
/

Put a deferred check constraint to check if the business rule is violated:

alter table tab_master add
constraint chk_num_details
check(num_details>0) initially deferred;

You can now test to insert a master row without a detail:

ENIS@dev01> insert into tab_master(id) values (1);

1 row created.

ENIS@dev01> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (ENIS.CHK_NUM_DETAILS) violated

Inserting a master row and a detail row in the same transaction does work:

ENIS@dev01> insert into tab_master(id) values (1);

1 row created.

ENIS@dev01> insert into tab_detail(id, master_id) values (2,1);

1 row created.

ENIS@dev01> commit;

Commit complete.

Now go to Oracle Forms to build a form on these tables. Create a default master-detail form using the Forms wizards. For table TAB_MASTER there is no need to include the NUM_DETAILS column in the Form. That’s just something for the database triggers and constraints. Make a relationship between the Master and Detail block. You should end up with a Form looking like this:

With this Form you can enter a new master record and some details and save without problems:

But now see what happens when we violate the rule and do not enter any detail records:

Forms first issues a FRM-40400 message informing the user that the record(s) were applied and saved. After dimissing that alert box, the user is confronted with a cryptic error message: “FRM-99999: Error 408 occured. See the release notes file (relnotes) for information about this error”. It looks like Forms is informing the user about a succesfull save before actually performing a COMMIT.

Unfortunately the release notes (of Forms 10.1.2.0.2) do not explain this error. It turns out that Forms wasn’t build to handle commit time errors. You can of course handle the FRM-99999 error in an ON-ERROR trigger and look at the dbms_error_code and dbms_error_text. Those will tell you that a deferred constraint was violated. You can then present a more informational message to the user.

However, this doesn’t change the fact that Forms first issues a FRM-40400 message to inform the user that the records were saved. If you then display an error after this, the user will be confused.

So, you really want to prevent the FRM-40400 message. Many thanks to the Oracle support Engineer that came up with the solution. You can fix this by overriding the ON-COMMIT trigger. In that trigger you check the deferred constraints. If that check fails you display an error. If the check does not fail, you can perform a normal forms_commit.

To check a deferred constraint, you can issue a:

set constraint constraint_name immediate;

So insert the followin code in the ON-COMMIT trigger of your Form:

declare
	v_dummy number;
begin
  -- check the constraint
  forms_ddl ('set constraint chk_num_details immediate');
  if not form_success and dbms_error_code=-2290 then
    -- constraint violated
    v_dummy := show_alert('master_detail_alert');
    raise form_trigger_failure;
  else
  	commit_form;
  end if;
end;

If you now enter a master without a detail and try to save:

If you acknowledge the error by clicking OK, the Forms commit is aborted. When you then enter some details and try to save again:

CPU Jan2006 for Database 10.2.0.1 on Linux re-issued

Here we go again! Just got an email from Oracle that Critical Patch Update January 2006 for Database 10.2.0.1 running on Linux x86 was re-issued. I think they re-issued some part of each Critical Patch Update since they started with the things. Is it so hard to test all known vulnerabilities after applying the patch sets in the final stage of QA?

Will they ever get it right the first time?

Dear Oracle Customer,

You are receiving this email because our records indicated you downloaded
the Critical Patch Update January 2006 (CPUJan2006) patch for Oracle
Database 10.2.0.1 (Patch 4751931)for Linux x86 before it was re-uploaded on
January 20, 2006.

These patches were re-uploaded because some files did not include all of the
changes required to fix the security vulnerabilities being addressed in the
January 2006 Critical Patch Update. No functional problems will be
encountered by applying an earlier version of these patches, but some
security vulnerabilities will not be completely fixed. Even if you have
successfully applied an earlier version of these patches, you should still
re-download and re-apply the latest version of the patches, dated
20-JAN-2006.

Please accept our apologies for any inconvenience you may have experienced,
and we thank you for your patience and cooperation in securing your Oracle
server products.

Regards,
Oracle Global Product Security

P.S. Please use MetaLink, https://metalink.oracle.com, to submit a Service
Request If you require further assistance. Please do not reply to this
email.

Unable to export char semantic 10.2 database

We’re in the middle of converting our 8.1.7.4 database to 10g release 2 (10.2). With this migration we’re also changing the character set of the database to AL32UTF8 and changing the database to char semantics. This gave us serious problems trying to export the database.

First a short introduction on the choice of character set and length semantics: Using Unicode as a character set will allow us to store any character and not just the western European characters we’re currently limited to. Unicode is a multi-byte character set which means that a single character could use more than a single byte. So what do you mean by VARCHAR2(10)? Is this 10 characters or 10 bytes? This used to be the same with our single byte character set, but these days are over. You can specify VARCHAR2(10 BYTE) or VARCHAR2(10 CHAR) if you want to be specific. However, all of our existing code doesn’t use this syntax. This is where the NLS_LENGTH_SEMANTICS parameter comes in. You can set this to BYTE or CHAR basically defining which one of the two is the default when not specifying it explicitly. A default installation will use BYTE semantics, but we decided to change it to CHAR. To me that is the more logical option.

So, we created a fresh database with NLS_LENGTH_SEMANTICS=CHAR. If you then run an export:

C:\>set ORACLE_SID=dev01

C:\>set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

C:\>exp '/ as sysdba' full=y file=full.dmp

Export: Release 10.2.0.1.0 - Production on Tue Nov 22 09:48:06

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Personal Oracle Database 10g Release 10.2.0.1.0
With the Partitioning, OLAP and Data Mining options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR
character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully

Searching MetaLink made me find Note 339938.1 which describes a situation where a 10.2 full export fails with an ORA-932 when exporting the cluster definitions. I figured I found the issue and a solution. But the note describes that some SYS objects should be invalid whereas all my SYS objects are valid. It also describes that a SELECT on sys.ku$_xmlschema_view should fail. This one does fail on me:

SQL> select url, local, stripped_val from sys.ku$_xmlschema_view;
select url, local, stripped_val from sys.ku$_xmlschema_view
                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR

Now it was time to open a Service Request (the new name for a TAR) on MetaLink. In the end the support engineer came with the solution. It all boiled down to two pieces of advice:

• only switch to char semantics after database creation (that has no impact on the “char semantics support”, any SYS objects cannot be CHAR and that is what happened here)
• use a “startup migrate” when running any patch or catalog scripts

So I shouldn’t have set NLS_LENGTH_SEMANTICS=CHAR before creating the database. The second advice to always use STARTUP MIGRATE is also a good tip. This will force the system of doing anything in byte semantics.

Here are the steps to “repair” my database. First check that the NLS_LENGTH_SEMANTICS is indeed set to CHAR. Then shutdown the database and restart as “startup migrate” You can see that the length semantics are now BYTE:

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 22 10:00:46

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> column parameter format a20
SQL> column value format a5
SQL> select * from nls_session_parameters
  2  where PARAMETER='NLS_LENGTH_SEMANTICS';

PARAMETER            VALUE
-------------------- -----
NLS_LENGTH_SEMANTICS CHAR

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup migrate;
ORACLE instance started.

Total System Global Area  469762048 bytes
Fixed Size                  1249608 bytes
Variable Size             146804408 bytes
Database Buffers          314572800 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> select * from nls_session_parameters
  2  where PARAMETER='NLS_LENGTH_SEMANTICS';

PARAMETER            VALUE
-------------------- -----
NLS_LENGTH_SEMANTICS BYTE

Now drop the XML DB stuff. After that exit SQL*Plus. There is some bug that screws up your session. This means you’ll have to close SQL*Plus at the end:

SQL> @?\RDBMS\ADMIN\catnoqm.sql

PL/SQL procedure successfully completed.

User dropped.

PL/SQL procedure successfully completed.

Table dropped.

SQL> exit
ERROR:
ORA-00600: internal error code, arguments: [729], [256],
[space leak], [], [], [], [], []

Disconnected from Personal Oracle Database 10g Release 10.2.0.1.0
With the Partitioning, OLAP and Data Mining options
(with complications)

There is a bug that says you have to restart the database after running catnoqm. Be sure to start it with “startup migrate” again:

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 22 10:05:17

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup migrate;
ORACLE instance started.

Total System Global Area  469762048 bytes
Fixed Size                  1249608 bytes
Variable Size             146804408 bytes
Database Buffers          314572800 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> column parameter format a20
SQL> column value format a5
SQL> select * from nls_session_parameters
  2  where PARAMETER='NLS_LENGTH_SEMANTICS';

PARAMETER            VALUE
-------------------- -----
NLS_LENGTH_SEMANTICS BYTE

Now run the CATQM.SQL script which requires three arguments (initial password and two tablespaces):

SQL> @?\RDBMS\ADMIN\catqm.sql change_on_install SYSAUX TEMP

Table created.

Table created.

[[ lot of succesfull statements removed for readability ]]

Package dropped.

Package dropped.

Function dropped.

Commit complete.

Now shutdown and start the database normally. You can check that the length semantics are back to its original (CHAR) and the query from sys.ku$_xmlschema_view now works fine:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  469762048 bytes
Fixed Size                  1249608 bytes
Variable Size             150998712 bytes
Database Buffers          310378496 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> column parameter format a20
SQL> column value format a5
SQL> select * from nls_session_parameters
  2  where PARAMETER='NLS_LENGTH_SEMANTICS';

PARAMETER            VALUE
-------------------- -----
NLS_LENGTH_SEMANTICS CHAR

SQL> select url, local, stripped_val
  2  from sys.ku$_xmlschema_view;

URL
----------------------------------------------------------------
     LOCAL
----------
STRIPPED_VAL
----------------------------------------------------------------
http://xmlns.oracle.com/xdb/XDBSchema.xsd
         0
<schema xmlns="http://www.w3.org/2001/XMLSchema" xmlns:xdb="
http://xmlns.oracle.

http://xmlns.oracle.com/xdb/XDBResource.xsd
         0

[[ lot of log removed for readability ]]
13 rows selected.

If you now retry the export it will succeed:

C:\>set ORACLE_SID=dev01

C:\>set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

C:\>exp '/ as sysdba' full=y file=full.dmp

Export: Release 10.2.0.1.0 - Production on Tue Nov 22 10:23:09

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Personal Oracle Database 10g Release 10.2.0.1.0
With the Partitioning, OLAP and Data Mining options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR
character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table               DEF$_AQCALL     0 rows exported
. . exporting table              DEF$_AQERROR     0 rows exported
. . exporting table             DEF$_CALLDEST     0 rows exported
. . exporting table          DEF$_DEFAULTDEST     0 rows exported
. . exporting table          DEF$_DESTINATION     0 rows exported
. . exporting table                DEF$_ERROR     0 rows exported
. . exporting table                  DEF$_LOB     0 rows exported
. . exporting table               DEF$_ORIGIN     0 rows exported
. . exporting table           DEF$_PROPAGATOR     0 rows exported
. . exporting table  DEF$_PUSHED_TRANSACTIONS     0 rows exported

[[ rest removed for readability ]]

The lessons learned from this experience:

• Switch to NLS_LENGTH_SEMANTICS=CHAR after creating the initial database
• Always use “startup migrate” when running catalog scripts

Also see MetaLink Note 144808.1 for more information and limits of using BYTE and CHAR semantics. This case will be added to this Note.

Update 17-dec-2005: Oracle filed bug 4886376 to have this problem fixed in a future release. The bug description also indicates that AL32UTF8 will be the default character set for a version 11.x database.

OpenWorld: Project Raptor – watch out TOAD

Oracle released an Early Access version of project Raptor. This made much of the following article obsolete as you can now see for yourself.

I just attended a session about new developments by the Server Technologies’ Database Division. One of the most interesting things (for me) was the short demo of project Raptor. This is the long awaited visual tool for database development. It could be a real competitor for the well known TOAD product from Quest. They did a short demo in which you could view and alter all the known database objects. The also showed some PL/SQL development features including code formatting and a powerful debugger. The session was also about other new developments in database 10gR2 and coming releases. This mean there was only a couple of minutes to demo project Raptor but it was really interesting. A lot of people approached the speaker afterwards to ask about Raptor.

It looked very familiar to me (knowing JDeveloper). It doesn’t require an Oracle client, just as JDeveloper. It’s just a matter of unzipping and start running. Looking at the screen it looks like it just makes a JDBC connection. It’s based on the JDeveloper framework but it is much more powerful then the existing database and PL/SQL editing features in JDeveloper. As I understood it will eventually replace the current database and PL/SQL editing in JDeveloper in some feature release. But before that, project Raptor will be offered as a standalone product on OTN. Due to all the new regulations Oracle people cannot give estimated delivery dates other then “this fiscal year”. For Oracle that means before May 31st 2006, but the speaker told me he expects it on OTN very soon.

I used my digital camera to make some photos of the demoed screens. The unfortunate thing is that I forgot to bring my USB cable to the Moscone center, so I can’t upload the pictures before this evening. Check back again to see the screenshots.

Some of the other things discussed in the session were Oracle Secure Backup, Patch distribution by Enterprise Manager Grid control 10gR2, TimesTen in-memory database, XQuery, Transparent data encryption, HTML-DB and two new security features: AuditVault and DataVault.

To me, TimesTen and AuditVault were the most interesting as they seemed to target actual business problems we are having right now. I’ll have to look into both technologies when I’m back at the office. I guess attending OpenWorld will take more then just the one week I’m here. I get the feeling I have a couple of weeks work when I get back in checking out all the new stuff I discover here.

PS. On a more personal note: congratulations to my wife for our second wedding anniversary. Couldn’t think of a better/more romantic way to celebrate it than to be on my own in San Francisco visiting a tech conference

Update 19-sep-2005 18:30: I’ve just returned to my hotel room to connect my digital camera to my laptop and upload the pictures. I didn’t want to use my flash when making the pictures during the session so I needed quite a long shutter time. Unfortunately that made the first picture kind of blurry. Nonetheless I will post all three pictures below. You can click on any of the pictures to get a bit larger view.

If you want to get the full size images, you can find them here, here and here.

The first screenshot is the blurry one but you can (hardly) see the tree control showing all database objects and the code editor for stored PL/SQL. The second shot shows the context menu including the Format SQL option for code formatting. The third and final shot shows the debugger in action. You can see all variables in the lower right corner. By the way: hovering over a variable in the code editor will show its value as a tooltip. You can also see the debug console at the lower end of the screen and the call stack in the lower left. I must say Raptor looked very promising for the five minutes we got to look at it. Let’s hope Oracle will show it at the demogrounds later this week and for the screenshots Brian has promised.

I’m off to the OTN underground event. Check back tomorrow for more updates on OpenWorld.

Update 21-sep-2005: Raptor was just briefly demoed during the keynote of Chuck Rozwat. Unfortunately I was watching the keynote in an overflow room where the screen is too small to make any good pics. If anyone attended the keynote in the big room and made some usable pics, you can contact me to put them up here.
I’ll have a look at Raptor at the HTML DB demoground myself, but I doubt they will let me near it with a camera

Update 22-sep-2005: Brian Duff has just published some real screenshots for project Raptor. These actually have some detail compares to mu blurry pictures.

Update 31-dec-2005: Added a comment at the top of the article about the release of an Early Access version of project Raptor.

Licensing policy change for multi-core processors

The IT-Eye weblog was just added to Orablogs. Unfortunately that was to late to pick up an entry about Oracle’s change of policy for multi-core processors. They refer to an interesting article in eWeek about this change.

I think it was about time that Oracle clarified/changed their policy on multi-core processors. The only thing that remains is a change in policy for grid computing. On the one hand Oracle is promoting use of multiple relatively small machines in a grid but on the other hand they keep charging by the processor. That forces us to minize the number of CPU’s and thus to not use grid.

Applying best practices from 3GL development

I’ve been looking at the tools used for 3GL development with some envy. There are tons of very productive tools available for Java, C++ and other 3GL developers. Wouldn’t it be great if we can use some of those for our Oracle Forms/Reports/Database development.

I’m planning on implementing some of those for our Oracle development in the rest of 2005 (and probably 2006). Hopefully this also closes the gap between our Oracle and Java developers. This is nice way for the Oracle developers to get to know some best practices from the Java development. Hopefully this makes a transition to Java a bit easier. I’ll keep reporting on this blog on my findings and perhaps publish a number of papers on how we implemented and integrated some tools.

The things I’m hoping to investigate (and perhaps implement) are:

Issue management
We could track bugs, features, tasks and improvements in a more professional way than we are doing now. This would also enable us to implement some workflow. I’m currently thinking about implementing JIRA.

Version control
Currently we do have some version control in place, but it is not as professional as I would like it to be. I will be evaluating both CVS and Subversion and we’ll probably implement one of these two. I tend to like Subversion a bit more but JDeveloper doesn’t support it yet.

Automatic builds
I would like to create automated builds as much as possible but at least daily. Refresh a database and an application server and apply all changes checked in to the version control system. During this automated process lots of checks and other tasks can be performed and monitored (also see other points). We might build this based on Ant and CruiseControl, since we’re already using these for our Java development.

Automatic tests
During the automatic builds I would like to perform as much automated tests as possible. This reduces the load on testers and can warn a developer early on that his change broke some tests. I’m looking at DBUnit and utPLSQL first.

Powerful editors
Currently we develop our PL/SQL code in Designer. The editor in Designer does offer color coding, but is not as powerful as other editors. Perhaps we should make JDeveloper or TOAD our primary editor. This would also enable us to offer all sorts of productivity enhancements to developers by using (custom build) plugins, templates and code snippets.

Formatting
If we would switch to another editor (JDeveloper or TOAD) I would also like to investigate code formatters. Using a uniform formatting of code makes it easier for developers to look at each others code. Also I found during Java development that Jalopy can save you a lot of time when writing code. It’s so nice to not bother about things like indentation and just run your code formatter to take care of it.

Quality Assurance
We have a lot of standards and guidelines for development. Wouldn’t it be nice if most of them could automatically be checked/enforced in your editor and during automatic builds/checks?

Documentation
If you’ve ever programmed Java, you must know JavaDoc. It’s a predefined way of adding documentation to every class and method and can generate quite useful HTML documentation for all your code automatically. Something similar exists for PL/SQL: PLDoc. It’s not much work for a developer to add comments using a fixed format but the generated HTML documentation can be a real treat. The editor should be able to help you here.

Logging
I want to have a look at the Log4PlSql framework. It’s designed after the Apache Log4J framework as being used by Java developers. It offers a way to add logging statements to your code which you can just leave in there for your production code. You can enable it at runtime and write the logging info to different outputs (database table, file, standard output, etc). No more hassling with adding dbms_output all the time and removing it again.

If you have any experience with these tools or methods and have some handy tips, please leave them as comment.