Saturday, February 24, 2007

Oracle Object Types in Oracle ADF

While browsing through OTN JDeveloper forum, I have found a question about how to use Oracle Spatial MDSYS.SDO_GEOMETRY object type with ADF BC. MDSYS.SDO_GEOMETRY object type is described in Oracle Spatial documentation - SDO_GEOMETRY Object Type. So, I have tried to implement support for it in ADF BC using ADF BC Domains - Creating Domains for Oracle Object Types When Useful. It seems, there is enough to generate Entity object from database table - ADF BC domains for spatial column are generated automatically.

I have developed sample application, that shows how to use MDSYS.SDO_GEOMETRY object type in ADF BC applications - However, all principles described in this sample, can be applied when using other Oracle Object Types in your applications. Developed sample use standard MVDEMO schema CITIES table, this schema can be downloaded from - Oracle MapViewer Demo Data Set. Model layer is developed using ADF Business Components, in View layer ADF Faces components are used.

When Entity object for CITIES table is created, type for spatial column is generated - SdoGeometry domain. Additionally, SdoPointType domain is generated. SdoPointType is a subdomain for SdoGeometry.

If we will look deeper, and review Java classes generated by Oracle ADF Business Components Design Time, we could find initStructureDef() method in both - and Code in this method represent object type structure. initStructureDef() method code for SdoGeometry domain:

initStructureDef() method code for SdoPointType domain:

There is no any modifications in ADF BC View object and Application Module, everything is created using default properties.

Ok, now we can switch to the View layer and review how users can edit and update data represented by ADF BC domains. Let's start from Data Control Palette - MDSYS.SDO_GEOMETRY object type is included into Data Control, we can explore and use it. Data Control with domain for object type looks like:

Developed sample application contains two pages - cities.jspx and sdoPointEdit.jspx. First page displays attribute cities data, in sdoPointEdit.jspx spatial data for selected city can be updated. Cities table content:

Let's select San Diego city and press 'Edit Geometry' button. Edit form for spatial information (X, Y) is displayed:

For example, we can change Y value to 33.815 and update SanDiego city geographical location ;-).

Ok, how to be sure, and check that spatial data is really updated. I think, we can use good old SQL*Plus and execute a query - 'SELECT C.LOCATION.SDO_POINT.Y FROM CITIES C WHERE C.RANK90 = 6', on CITIES table. Returned result is as we have expected: 33.815. So, operations on Oracle Object Types in ADF BC application can be implemented really easy. SQL*Plus shell window:


Gareth said...

Is it ok not to use Java Library for Oracle Spatial when you are using ADF BC. For example instead of using the distance function in Java Library, is it ok to use SDO_DISTANCE and embed it in View Object?

Can we use ADF BC in querying some SDO_GEOMETRY without the help of Java Library?

Andrejus Baranovskis said...


Sure, you can do this. Just put let's say SDO_DISTANCE into SQL statement for View object. And it will work Ok.


Sabry said...

Hi Andrejus,
I'm really trying to put the spatial operator SDO_WITHIN_DISTANCE into a query, but the problem is that when I created the distance parameter as bind variables into a view object, I don't have any view criteria to associate with it. Any suggestions?

Thanks and regards

Andrejus Baranovskis said...


You could use ExecuteWithParams method, it allows to assign values to Bind Variables.


P Nikhilkumar Shrikant said...

Hi I want to keep single selection in one of the table columns so that when user want to enter ex department in company table hi/she prompted only department so that he cannot enter the else text in that field.