Feed aggregator


Vikas Jain - Wed, 2010-02-03 14:52
Secure Token Service (STS) typically have a SOAP endpoint with WS-Trust standard profiling the interactions. How about taking the complexity of SOAP away, and adding simplicity of REST interface to the STS? At the end of the day, STS is a token service that applications use to acquire tokens and should be accessible through different types of bindings - SOAP, REST, etc.

What would be the interaction pattern for such RESTful STS?
  1. Clients access RESTful STS using HTTP GET/POST method sending RequestSecurityToken (RST) as part of HTTP message.
  2. RESTful STS sends back the requested token as RequestSecurityTokenResponse (RSTR) in the HTTP response message.
  3. The STS endpoint could be secured similar to any HTTP resource using web access management products such as Oracle Access Manager (OAM) with username/password or certificate credentials.

RESTful STS can lead to wider adoption
Many languages/frameworks (such as Adobe Flex and Silverlight) doesn't support full capabilities of a SOAP stack. But, they support the basic HTTP interactions. Such frameworks could easily plug into a RESTful STS for their token needs.

Applicability of RESTful STS in the cloud
As cloud remains the innovation vehicle for 2010, I try to find applicability of any new concept into the cloud as well.
Today, Google, Amazon, Salesforce of the world provide RESTful APIs for all it's services. If they decide to broker trust using some sort of STS, then it makes perfect sense for them to provide RESTful STS with API keys and OpenId/OAUTH models to access it.

OER 11g released

Vikas Jain - Mon, 2010-02-01 02:23
Oracle Enterprise Repository (OER) 11g is released and generally available for download now. OER alongwith OSR (UDDI registry), OWSM and EM SOA Mgt Pack Plus comprise Oracle's SOA Governance offering. Of all the new features added in this release of OER, there's one feature around closed loop governance that I would like to discuss in this blog.

Closed loop governance allows architects to review at a high-level how the system and services they designed are behaving in production, and with this knowledge further enhance the services in their subsequent versions. It provides confidence and production assurance to business people that the investments they have put in SOA is actually being put to use.

In this release of OER 11g, high-level performance metrics from Enterprise Manager (EM) and 3rd party products such as Amberpoint are rolled up into OER.

Through the same pattern, do you see a need for rolling up policy attachment info from OWSM into OER?

See more of "What's New in OER 11g" here.

Oracle + Sun: Identity Management Strategy webcast

Vikas Jain - Fri, 2010-01-29 15:47
Watch Oracle + Sun identity management strategy webcast by Oracle executive Hasan Rizvi, Sr. VP

Oracle + Sun Strategy Webcast

Vikas Jain - Wed, 2010-01-27 18:57
Oracle + Sun Strategy Webcast was done by Oracle/Sun executives today.
Hope you got a chance to attend it live. If you missed it, check back the link in couple of days when the recording would be available for on demand viewing.

HowTo - OWSM 11g: Creating custom policy assertions

Vikas Jain - Wed, 2010-01-27 18:53
Similar to OWSM 10gR3, you can extend OWSM in 11g using custom policy implementations.
From terminology perspective, OWSM 10g custom policy is similar to OWSM 11g custom policy assertion.
Here are some quick links that may help if you plan to implement custom policies.
  1. Refer to Creating Custom Assertions section of OWSM product documentation
  2. Refer to Java API reference for available APIs
  3. Step by step How-To guide on building a sample custom assertion, deploy, and test it

One last entry

Siva Doe - Tue, 2010-01-26 19:32

Don't know if there will be another chance to blog as a Sun employee. So, here it is.
Even though I joined Sun in 1997, I have been using Sun products since 1991. Will miss you 'Sun'.

Some very interesting Web 2.0 Links that can help in Smart Marketing & positioning

Arvind Jain - Sun, 2010-01-24 17:53

This page contains links to some very interesting websites that I use as part of my Product Marketing SEO tasks, they help you gain strategic edge using IT (information technology). Anyone interested in Search Engine Marketing (SEM) must pay attention to these tools:

  • Google Trends - find temporal trends in search word usage on the internet
  • Google Insights for Search - estimate relative importance of search terms with trends by geographical regions

  • Google Analytics - web analytics solution that gives insight into your website traffic and marketing effectiveness

  • Quantcast - monitor website traffic and effectiveness of marketing communications to customers. This give Demographics info of Visitors. You can also use Microsoft AdCenterLabs to analyze demographics.
  • Hitwise - ISP data, can be used to analyze how people get to, spend time in and depart from websites, large sample size

  • Alexa - web traffic metrics based on voluntary anonymous tracking of people who have signed up for free, large sample size
  • Comscore - web traffic metrics based on voluntary tracking of people who have signed up for a fee, gives much more detailed information but sample size is small

  • CrazyEgg - Click density analysis, find out where people are clicking on your webpage (is your design driving people to the right place?)  
When you use Google AdWords be sure to use Ad Preview Tool at

Your ScrumMaster is a project manager in disguise

Peter O'Brien - Thu, 2010-01-21 02:34
In 6 attributes of a good ScrumMaster Mike Cohn repeats the common line that the ScrumMaster role does not always require a full-time, eight-hour-a-day commitment. Often the 'orchestra conductor' role of ScrumMaster is an unofficial one within your organisation even though it clearly has well defined functions and responsibilities. So many people do ask the question Is a ScrumMaster a full time position? As Boris points out, it is, and he explains why it is a 100% fulltime job.

The ScrumMaster has internal and external responsibilities. Even if the team is well disciplined with following the process, and they address most of their own impediments there is still the challenge of being a gatekeeper between the management and the team. This is being recognised in many organisations now and you can even see ScrumMaster as a recruitment position. It's interesting to note that many of these positions have Project Manager / ScrumMaster as the title.

What does a Project Manager do that a ScrumMaster does not (or vice versa)? A project manager is the person who has the overall responsibility for the successful planning and execution of a project. This title is used in the construction industry, architecture, information technology and many different occupations that are based on production of a product or service. While strictly speaking, the team, rather than the ScrumMaster has responsibility for the success of the project, a ScrumMaster does assume responsibility for the team’s adoption of Scrum and practice of it. A ScrumMaster takes on this responsibility without assuming any of the power that might be useful in achieving in it.

To boil it all down to it's essence, a ScrumMaster is a Project Manager who has realised that they don't really have the power to successfully deliver a project, and has adopted a framework to take advantage of that humbling position.

Interview questions

Oracle WTF - Thu, 2010-01-21 01:26

A friend recently had a telephone interview for an Oracle technical contract role. Here are the questions he was asked:

  1. What is the command to edit a crontab?
  2. What are the first and fourth parameters on the crontab?
  3. What is the command to email the list of files that are too big and need to be deleted to prevent a tablespace getting too big?
  4. Have you used the OLAP command? and who invented it?
  5. When do you set PCTFREE?
  6. When is the PGA in the SGA?
  7. Where is the Java pool?
  8. How do I stop a checkpoint when I commit?

APEX 4.0 - Learn more about Dynamic Actions

Anthony Rayner - Thu, 2010-01-14 20:33
Update: Please note, I have now updated my dynamic action sample application, the links in this post no longer work. Please see this blog post for details.

As many of you may know, APEX 4.0 Early Adopter's was released before Christmas. In this release, we introduce a new feature called 'Dynamic Actions' that provides a declarative way of defining client-side behaviour, without needing to know JavaScript. There is a simple wizard to create new dynamic actions, whereby you just specify 'When' the dynamic action will fire, the 'Action' itself and 'What' will be affected. As I said, you don't have to know JavaScript to do a fair amount with this feature, but there are also some hooks for JavaScript developers to extend the dynamic action framework to do a whole load more!!

To help you understand this feature, I have put together a sample application containing lots of different uses of dynamic actions such as drag and drop, styling page items and interactive reports, retrieving data from the server via AJAX, responding to plug-in item events such as the 'Slider' sliding and more. You can either view the application running on the EA instance here or download it from here, so you can install it in your own EA workspace and have a deeper look. If you haven't yet signed up for the APEX 4.0 Early Adopters, take a look at David Peake's related blog post where he explains how to get started.

Note: If you are installing this application, there is one supporting object defined containing a simple PL/SQL function 'getCommission' used by a couple of the examples. During the install, please install this supporting object to get the full functionality. Also, the application requires that you have a copy of the standard 'EMP' table in the parsing schema for the application.

The application makes use of a number of native dynamic actions (that will be built-in to APEX), but also contains 5 dynamic action plug-in examples which you can look at, install and play around with. The plug-ins are:
  • Draggable - Define page elements as draggable, with various options such as restricting by vertical or horizontal axis, transparency during drag, containment and more.
  • Droppable - Define page elements as droppable, with various options such as restricting which draggables can be dropped, styling to guide the user where they can drop the element and more.
  • Execute PL/SQL Code - Define a PL/SQL snippet right from within the dynamic action that will be executed on the server, via AJAX. This is currently only coded for Theme 1.
  • Highlight - Patrick Wolf's plug-in that provides the ability to highlight elements on the page.
  • Stripe Report - Used for striping interactive report regions with alternate row colours.
I have ensured all the plug-in code is thoroughly commented to try and help you understand exactly what's going on and hopefully get you started in building your own dynamic action plug-ins!!

Plug-ins are 1 of the major components of APEX 4.0 and if you're interested to learn more I can thoroughly recommend taking a look at Patrick Wolf's 'How to create a plug-in' blog post and accompanying downloads.

I hope you like the application and let me know how you get on!!!


PS: Many thanks to Patrick Wolf for his invaluable help in reviewing these plug-ins.
Categories: Development

Follow me on Twitter

Tahiti Views - Wed, 2010-01-13 00:51
I'm Max Webster there.John Russellhttp://www.blogger.com/profile/17089970732272081637noreply@blogger.com0

Ten Years Gone

Tahiti Views - Wed, 2010-01-13 00:47
I've been pretty quiet lately, because I'm in a transitional period. After 10 years on documentation for Oracle Database and other enterprise server products, I'm switching to the InnoDB group that already works with MySQL. New development environments, new customers, it's an exciting time!A decade seems to be the right timeframe for me. It was 10 years at IBM before that. Check back in 2019, I'mJohn Russellhttp://www.blogger.com/profile/17089970732272081637noreply@blogger.com4

SOA Management - Sample Chapter from Middleware Management Book

Debu Panda - Tue, 2010-01-12 14:11
Oracle Technology Network published sample chapter SOA Management (Oracle Service Bus) of my Middleware Management book.

You can access the chapter at http://www.oracle.com/technology/books/pdfs/sample-soa-management.pdf

This book covers management of both Oracle Fusion Middleware (WebLogic/OC4J, SOA Suite, IDM, Coherence, Forms/Reports, etc. and non-Oracle Midddleware such as JBoss, Tomcat, Apache Http Server IBM WebSphere and Microsoft .Net/IIS, etc

You can purchase the book at Amazon at http://www.amazon.com/Middleware-Management-Enterprise-Manager-Control/dp/1847198341.

Also see details at http://www.packtpub.com/middleware-management-with-oracle-enterprise-manager-grid-control-10g-r5/book

Retainage and Retainage Release in Or...

Krishanu Bose - Thu, 2010-01-07 21:28
Retainage and Retainage Release in Oracle Payables for a Complex Purchase Order

Retainage represents funds withheld from payment to ensure that the contractor finishes work as agreed. The buying organization releases these funds only after verifying that the contractor has fulfilled all contractual obligations. Retainage is also called "retention" or "contractual withholds".
With Oracle's Complex Work feature, contract administrator can negotiate retainage terms with the contractor and capture these as part of the contract. These terms include Retainage Rate and Maximum Retainage Amount.

Retainage Rate
The Retainage Rate determines the percentage of the amount requested that will be withheld before releasing payments to the contractor. This attribute appears on the PO Line if the Document Styles enables the use of Retainage. The Retainage Rate specified on the PO Line is applied to all Standard Invoices billed to the Pay Items of the respective Line. Based on the Retainage Rate, a certain percentage of the Standard Invoice Amount is with-held as a Retained Amount.

Maximum Retainage Amount
The Maximum Retainage Amount defines maximum amount of Retainage that can be withheld on a Contract Line. The Maximum Retainage Amount attribute appears on the PO Line if the Document Style enables the use of Retainage. Every time Retainage is withheld on an Invoice, the application checks that the total retained amount does not exceed this value.


Set Up Steps for Complex Purchase Orders and Retainage
Following three mandatory setups steps are required if the complex purchase orders and retainage feature will be used.

1. Create a new document style in Oracle Purchasing for handling Complex PO.
Responsibility: Purchasing
Navigation: Setup > Purchasing > Document Styles > Create
Define whether the complex PO can include advances, retainage, and progress payments


2. Create a retainage account in Financials Options setup.
Responsibility: Payables
Navigation: System Administrator > System Profile Options
Set the value for POR: Amount Based Services Line Type profile option to ‘Fixed Price Services’


3. Create a retainage account in Financials Options setup.
Responsibility: Payables
Navigation: Setup > Options > Financials Options
Enter the retainage account to use during accounting



Entering a Complex PO

1. Create a Purchase Order using Buyer Workbench. Select the Document style ‘Complex PO’ just created earlier
Responsibility: Purchasing
Navigation: Buyer Work Center > Orders


2. Enter the PO Header and Line details


3. Click on ‘Update’ icon on the PO line and enter the Maximum Retainage Amount and the Retainage Rate


4. Enter the additional pay items for which a payment should be made 


5. Enter the PO Charge account on PO Distribution and Save and Approve the PO



Entering Payable Invoices

1. Once supplier sends the invoice after the first milestone is met, raise a PO matched invoice


2. Oracle automatically creates a Retainage line based on the Retainage Rate defined earlier on the Purchase Order



3. Supplier sends the invoice after the second milestone is met. Raise a PO matched invoice



4. Oracle automatically creates a Retainage line based on the Retainage Rate defined earlier on the Purchase Order



5. At the end of the project the supplier can submit a Retainage Release Invoice for release of retainage withheld on the Contract. If the contractual clauses or other conditions governing the release of the retainage are met, the buyer can release the retainage amount applicable. Oracle has introduces a new Invoice type called ‘Retainage Release’ for this purpose



6. Enter the PO number for which you want to release the Retainage



7. Enter the Retainage Amount that you want to release and click on ‘Release’. You cannot enter an amount greater than the Retained Amount



8. An invoice of Invoice Type ‘Retainage Release’ is created for the Retained Amount



9. The Retainage details will be visible at invoice Lines



10. The Retainage Account will then be offset to Supplier Liability account for subsequent Payment


My predictions for 2010

Khanderao Kand - Wed, 2010-01-06 14:26

Screencasts of Oracle PL/SQL unit testing with Ruby

Raimonds Simanovskis - Tue, 2010-01-05 16:00

In my previous post I already described how to do Oracle PL/SQL unit testing with Ruby. I now have named it as ruby-plsql-spec unit testing framework. But probably you didn’t want to read such long text or maybe it seemed for you too difficult to try it out therefore I prepared two screencasts to show how easy and fun it is :)

Testing simple function

The first example is based on classic BETWNSTR function example from utPLSQL tutorial.

Testing procedure that changes tables

Second example is based on Quest Code Tester for Oracle testing tables demo screencast. So you can see both unit testing frameworks in action and can compare which you like better :)

Test driven development

In both these screencasts I demonstrated how to do test driven development of PL/SQL

  • Write little test of indended functionality before writing code.
  • Write implementation of new functionality until this test passes and verify that all existing tests pass as well.
  • Refactor implementation when needed and verify that all tests still pass.

From my experience TDD style of development can improve design and testability of code and also make you think before coding what you actually want to implement. But existing visual PL/SQL testing tools (Quest Code Tester, SQL Developer 2.1) do not quite support TDD style of development, they expect that there is already existing code that should be tested. Therefore this is one more ruby-plsql-spec advantage if you would like to do TDD style development in PL/SQL.

More information

Examples shown in screencasts are available in ruby-plsql-spec GitHub repository. And if you want to see more examples how to use ruby-plsql library for PL/SQL unit testing then you can take a look at ruby-plsql own RSpec tests or read previous posts about ruby-plsql.

Categories: Development


Charles Schultz - Tue, 2010-01-05 15:36
Daniel Morgan alerted me to an index rebuild package he worked on; as I was reading up on sys_op_lbid (which is incredibly interesting, btw), I came across Richard Foote's "Index Internals", another awesome read.

Getting past the humurous myth busters (does anyone escape his righteous wrath?!? *grin*), I was delighted to learn about treedump:
alter session set events 'immediate trace name treedump level &index_object_id'; -- smartquotes removed

I had been experimenting with block dump on index blocks, trying to slug my way through the various header and pagetable blocks. While that is eye-opening in itself, the treedump really paints a human-understandable picture. As Richard has stated in other documents, indexes rarely ever look like the typical pyramid scheme (the one that everyone on the Planet uses, including himself *smile*); instead, more often than not, the "trees" get really wide very fast, and are usually rather shallow (not too many levels deep). The treedump not only exposes this commonality, but succinctly demonstrates why. Namely, each branch block of size DB_BLOCK_SIZE can contain hundreds or thousands of references to children blocks (be they further branch blocks or leaf blocks).

Mr. Foote's "Internals" presentation goes on to divulge other useful tidbits and I intend to revisit it to reinforce my learning. He is one smart dude.

My only request now is a live, dynamic graphical representation of the index "tree"; when teaching students (for example) how indexes are built, having a visual component really helps to emphasize what is going on. When I do them by hand (whiteboard and marker), I find it also goes a long way to demystify otherwise confusing (and thus prone to myths) concepts as deleted index entries, "fragmentation", "unbalanced" and index block splits. What can I say, I am visually oriented.

Thanks to Dan Morgan for vigilantly working to improve Oracle for us lower-lifeforms (ie, the index rebuild/coalesce package is really helpful). Without his nudging me, I would not have yet found sys_op_lbid, nor explored the internals a bit more.

ruby-plsql 0.4.1 - support for package variables, views, dbms_output and more

Raimonds Simanovskis - Sun, 2010-01-03 16:00

Based on feedback from using ruby-plsql for PL/SQL unit testing I have release new version 0.4.1 with several new features. You can read about initial versions of ruby-plsql in previous blog posts.

Package variables

When you call methods on plsql Ruby object then ruby-plsql uses all_procedures and all_arguments data dictionary views to search for procedures and their argument metadata to construct corresponding PL/SQL block for execution. Unfortunately there are no corresponding data dictionary views for package variables (sometimes called “global variables”) that are defined in package specifications. Therefore there was no support for package variables in initial ruby-plsql versions.

But as there is quite frequent need in PL/SQL tests to set and get package variable values then I created the following solution for accessing package variables. I assume that typically package variables are defined in one line in package specifications and I scan PL/SQL package specification source in all_source data dictionary view for potential package variable definitions.

As a result if you have the following example of package specification:

  varchar2_variable VARCHAR2(50);
  number_variable NUMBER(15,2);
  string_constant CONSTANT  VARCHAR2(10) := 'constant';
  integer_constant CONSTANT INTEGER := 1;

then you can access these package variables in the same way as procedures:

plsql.test_package.varchar2_variable = 'test'
plsql.test_package.number_variable = 123
plsql.test_package.varchar2_variable # => 'test'
plsql.test_package.number_variable # => 123
plsql.test_package.string_constant # => 'constant'
plsql.test_package.integer_constant # => 1

Other basic data types as well as %ROWTYPE, %TYPE and schema object types are also supported for package variables. Only custom types defined in package specification are not supported (they are not supported for procedure parameters as well). As there are no data dictionary views for types defined in package specifications I don’t feel very enthusiastic about parsing package sources from all_source to get information about types defined inside packages :)


In previous post I described how to use ruby-plsql to perform basic table operations. Now these operations can be performed also with views:

insert_values method

Additional insert_values method is added for tables and views which can be helpful in PL/SQL tests for test data preparation. You can specify with more compact syntax which data you would like to insert into table or view:

plsql.employees.insert_values [:employee_id, :first_name, :last_name],
    [1, 'First', 'Last'],
    [2, 'Second', 'Last']

# => INSERT INTO employees (employee_id, first_name, last_name) VALUES (1, 'First', 'Last')
# => INSERT INTO employees (employee_id, first_name, last_name) VALUES (2, 'Second', 'Last')

If you use DBMS_OUTPUT.PUT_LINE in your PL/SQL procedures to log some debug messages then you can use plsql.dbms_output_stream= method to set where these messages should be displayed. Use the following to display DBMS_OUTPUT messages in standard output:

plsql.dbms_output_stream = STDOUT

Or write DBMS_OUTPUT messages to file:

plsql.dbms_output_stream = File.new('debug.log', 'w')
STANDARD package procedures

Procedures from SYS.STANDARD package can be called without sys.standard prefix, e.g.:

Other improvements

See History.txt file for other new features and improvements and see RSpec tests in spec directory for more usage examples.

And also this version of ruby-plsql requires ruby-oci8 gem latest version 2.0.3 (if you use MRI / standard Ruby interpreter 1.8.6, 1.8.7 or 1.9.1) so please upgrade it as well if you do not have it. But as previously you can use ruby-plsql with JRuby and Oracle JDBC driver as well.

Categories: Development

Oracle R12.1.2 HCM New Functionality Part 1

RameshKumar Shanmugam - Sat, 2010-01-02 22:35
EBS R12.1.2 is released and much awaited gap in the OTL and Absence Management product is closed in the current release.
OTL timecard is integrated with the SSHR Absence Management, with this new functionality Absence entered in the Oracle Core HR/ ESS or MSS will be populated in OTL Timecard.Similarly Absence time entered in OTL can be viewed in Core HR and SSHR. This new functionality helps to maintain the data integrity and this new functionality also eliminates much of the custom work that need to be done by the consultants to validate the time entered in OTL against the Absence Management

To understand how to setup Absence Management refer the blog http://ramesh-oraclehrms.blogspot.com/2007/07/leave-management.html
Categories: APPS Blogs

Google Maps in APEX on XE with spatial data

Jornica - Mon, 2009-12-28 06:55

A picture says more than thousand words is well known saying, well let's have a look.

Choose your favorite country from the list, and the Google Map will pan to your country. As a bonus the selected country is colored red, the country shape is based on spatial data. You can zoom and move the map, or change the map type.

What do you need to build this application:

  • An Oracle XE database or better. The good news is that XE already contains the MDSYS schema full of spatial utilities. The bad news is that XE has no spatial data included. But ...

  • NAVTEQ provides sample data for Geocoder. Download, import and use it.

  • Apex, included with XE.

  • A Google Maps API key.

And it is all free, as in beer.

Just a remark about using the term spatial. As you can read here XE does not have the Spatial option included nor does it include Oracle Locator with or without Oracle Mapviewer, but XE contains the MDSYS schema and that is all we need.

Include Google Maps

Sign up for a Google Map API key here. For XE use the following URL to sign up. Take a look at the sample page shown after generation of your key.

Now create in APEX a page (in a new or existing application) and navigate to the page attributes.

In the Display Attributes section set the cursor focus to Do not focus cursor. In the HTML Header section insert the following code, replace ... with your Google maps API key.

<script src=http://maps.google.com/maps?file=api&amp;v=2&key=...&sensor=false

In the HTML Body Header (section Header and Footer) insert the following code:

<script type="text/javascript">
function load() {
if (GBrowserIsCompatible()) {
var map = new GMap2(document.getElementById("map"));
map.setCenter(new GLatLng(37.4419, -122.1419), 13);

In the Page HTML Body attribute (section HTML Body attribute) insert the following code:

onload="load()" onunload="Gunload()"

Add a HTML Text region to the page with the following region source:

<div id="map" style="width: 500px; height: 300px"></div>

For Internet Explorer a small change of the page template is needed otherwise Internet Explorer stops responding. The first line of header (section Definition) should look like this:

<html lang="&BROWSER_LANGUAGE." xmlns:htmldb="http://htmldb.oracle.com" xmlns:v="urn:schemas-microsoft-com:vml">

Run the page and if everything is alright you will see a map of Palo Alto. This is the 'hello world' example of Google Maps. Note that it is required to issue the SetCenter statement otherwise only a grey pane is shown on the place of the map. You can also add some bells and whistles: map controls and polylgons (points connected by lines with a fill color as well).

Prepare APEX

Let us finish the web front end first before retrieving the geodata from the database. Modify the page created in the previous section.

Change the pagetype in PL/SQL (anonymous block). Insert the following in Region Source (section Source):


The package p_gis_data is discussed later.

Add two items to the page: P1_COUNTRY (select list with submit) with the following dynamic list of values query:

SELECT initcap(country) display_value,
country return_value
FROM m_world

And add a placeholder for the map P1_DIV (Display as text, does save state) with the following value for Source value of expression (section Source):

<div id="map" style="width: 500px; height: 300px"></div>

In order to initialize the Google Map add a before region process. This process sets the default value of the select list when there is no item selected.

:P1_COUNTRY := 'Netherlands';
Load sample data

Download the sample data and unzip. Create a separate user and tablespace for the sample data:


Only execute steps 1 and 2 from the README. You do not need to install Mapviewer. After some time all the data is loaded.

Retrieve spatial data

It's time to retrieve spatial data from the database. Take a look at the M_WORLD table, there is a column named GEOMETRY with datatype SDO_GEOMETRY which stores all the spatial data. The GEOMETRY column stores the position (property GEOMETRY.SDO_POINT), more or less the center, of a country and one of more polygons. A polygon consists of points connected by (straight) lines. Remember the Google polygons?

Our task is to retrieve all polygons from the GEOMETRY column. Property GEOMETRY.SDO_ELEM_INFO tells us how many polygons the property GEOMETRY.SDO_ORDINATES stores and of which kind they are. The function SDO_UTIL.GETNUMELEM retrieves the number of elements for a given geometry, in our case the number of polygons. The SDO_UTIL.EXTRACT function extracts the nth element from a geometry returning a geometry object as well. And the function SDO_UTIL.GET_VERTICES retrieves all points of geometry. Combining these two functions will result in a number of geometry objects, each object corresponds with one polygon.

We can retrieve this information in one SQL statement:

SELECT country.geometry.sdo_point.x x,
country.geometry.sdo_point.y y,
FROM TABLE(sdo_util.getvertices(sdo_util.EXTRACT(country.geometry, country.element)))
ORDER BY id) area
(SELECT iv.*,
LEVEL element
FROM m_world
WHERE country = cp_name) iv
CONNECT BY LEVEL <= sdo_util.getnumelem(geometry)) country

The inline view iv selects one row from the M_WORLD table and for each element of the corresponding geometry a copy of this row is generated. In the country select each element is stored in a cursor expression. The next thing is to write some PL/SQL code to generate javascript based on this query i.e. p_gis_data.

Further reading
The idea for this blog entry came after reading Creating Thematic Google Mapping Applications (for Business Intelligence) using Oracle Locator/Spatial and Application Express and Auf den Ort kommt es an: Geodaten in Application Express-Anwendungen nutzen (in german) and of course Oracle Spatial User's Guide and Reference.


Subscribe to Oracle FAQ aggregator