Thursday, December 27, 2007

11g database installation and native webservices

Downloaded and installed an 11g database on a spare Windows XP machine that was floating around, while our local DBA finishes up installing our real playground on a Solaris box.

The first thing I noticed was the size of the download - Whoa! 1.8 Gb for the 11g database setup, expanding out to 2.9 Gb for the real installed database. Compare this to the download size of a 10g setup which was a smidgeon under 700Mb, expanding out to 1.6 Gb for a final installed database. I don't know what is included in the new features to take up that much more space, but *insert expletive here*, it seems to have taken quite a leap.

Once the Windows installation completed (after re-starting a couple of times due to a very invasive piece of currently installed software on the machine that had taken over the JVM - thanks Mercury!), I ensured the standard SCOTT/TIGER schema was there (Ah, EMP and DEPT, will I never forget thee), and then used the Enterprise Manager to setup a new schema. Oh, and I notice that the EM address is now accessed over https - good to see, although I kept getting certificate errors - probably an IE setting gone awry. Yes, yes, I should be using Firefox, move on...

One of the driving forces behind getting the database up and running was to test out a new capability of 11g - native Web Services for database PL/SQL code.
Ok, so new schema gets the XDB permissions granted (XDB_WEBSERVICES, XDB_WEBSERVICES_OVER_HTTP, XDB_WEBSERVICES_WITH PUBLIC) which are required for various methods of accessing web services, I assume. Then a new package is created. Something simple first - GET_TEXT_F: a function that returns some text; should we use the tried and tested "Hello World", or try something new and exciting?

create or replace
function get_text_f return varchar2 is
return ('Marc was here');

Spectacular! So, in theory with all of that in place, we should be able to call that little function as a web service. First, determine the WSDL:


Have to ensure schema and function name are in UPPERCASE! Also had a bit of a stumble with the first run, because the port for webservices had not been set! To fix that, it can simply be set manually, although I'm not sure if you are restricted to certain ports (topic for investigation later...).

execute dbms_xdb.setHttpPort(8080);

Trying the wsdl retrieval again after that is set gives a nice auto-generated WSDL document based on the function.

Use SOAP UI ( to do some quick tests of the simple function (you must also remember to enter your database credentials in the SOAP request attributes) before trying something a bit more meaty!

Complex data types.
OK, say I have a database table called customers, structured something like

CREATE TABLE customers (

Fairly simple, yes? Could I expect a table ROWTYPE return to be automatically translated by the native 11g webservice to WSDL without any extra coding? Lets see....

create or replace function get_customer_f (p_customer_id in number) return customers%ROWTYPE is
lvt_customer_rec customers%ROWTYPE;
select *
into lvt_customer_rec
from customers
where customer_id = p_customer_id;
return (lvt_customer_rec);

Yes, I know, I don't have any exception handling, move on...
Meanwhile, lets test to see what the WSDL comes out as


Bzzzzzzzzzzzz, you basically get a 'Error processing input' error.
What we need to do is transform the return data into a native database data type, which means you are going to have to create TYPEs based on what your functions are going to accept and return. So, in this instance, we would need to create a customer_type

ID number(10),
NAME varchar2(50)

then, we need to change our function to cope with the new type:

create or replace function get_customer_f (p_customer_id in number) return customer_type is
lvt_customer_type customer_type;
lvt_customer_type := customer_type(null,null);
select id, name
from customers
where customer_id = p_customer_id;
return (lvt_customer_type);

Now, since the database can trace the CUSTOMER_TYPE back down to primitive database types, the WSDL can be auto-generated. I would like to see if Oracle could manage to make this just a little bit easier for us kids who don't want to go around making up database types left, right and center.
Time for a post to
That's all for now...

Monday, December 24, 2007

ADF Beginnings

Ok, I have started the ascent into the learnings of Oracle's ADF technology stack.

A bit of background first: I am mainly an Oracle Forms/Reports and PL/SQL developer, and have been for the past 10+ years, but its been a bit of a static learning path, and I have only picked up a few new things here and there since I have been on basically the same project during all of that time. I started my first real-world project with Forms 4.5 (16-bit) and when it was in a production state, helped migrate it up to a 32-bit version. Then I moved interstate (from the Australian West Coast, to Sydney) since the project I originally worked on was sold as an application package to an interstate related organisation.

This move saw the same app being migrated and enhanced to a 6i/8i app, and I also had the chance to code a subset of it into a VB6/Access application that could be used during network/database downtime.

Then the application code was picked up by another interstate related organisation in Melbourne, so I packed my bags and arrived there on Boxing Day of 1999. Things started to get a little more interesting here as the team was interested in making a a more robust application with more emphasis on visual navigation, so among other things, I spent some time helping to put together a Hierarchical Tree control for navigation between Forms. This has worked out very well, and continues to be a great way for the users to jump between application areas.

At the end of 2005, we started the migration to 10g (both Forms and the Database), and successfully deployed the 're-stumped' application in early 2007. The migration gave us a few headaches - since our users had a high reliability on the visual and navigational aspects of the application, sorting out the JVM idiosynchrasies required close (intimate?) interaction with Oracle support. Neither JInitiator nor Sun Java 1.5 gave us 100% satisfaction, but JInitiator turned out to be the lesser of two evils, so we have gone the route for now.

I also spent some time creating a build and deployment tool (using Forms) that comes in two flavours: One that developers could use to easily transfer and compile Forms code to our development unix application server and allow them to run it in their own isolated environment; and another that the Build Administrator can use to extract modules from our Source Control application and transfer and compile it in a series of easy steps to our Test application server.

Meanwhile, we have started looking at expanding requirements and the need to ensure we are working on a supported development base. Since the wafting tendrils of the fear of Forms being de-supported seems to be looming, we have started looking at alternatives for a future front-end for our application. Management has also been throwing around the 'SOA' acronym, and we all know what that means! (If any does actually know what that means, please feel free to enlighten me...)

So, a couple of months ago (hey, I've been a busy man), I downloaded JDeveloper 10g ( AND and I've been using it for a bit of Java development that we have gradually been implementing on both the Application Server, and on the Client.
Now, I have downloaded JDev 11g (Tech Preview 2) and have noticed a few annoying bugs with it (Refactoring is a nightmare), as well as the complete absence of SOA and web servce functionality.

My intention now is to have a bash at ADF development as well as looking at APEX and whatever it has to offer.

*UPDATE* JDev 11g Tech Preview 3 has just been made available. Stay tuned for more excitement as the story unfolds...