Feed aggregator

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

Quick Bazaar on Windows over SSH

Vattekkat Babu - Fri, 2010-01-08 22:05

Quick notes on how to set up a version control system using Bazaar with just SFTP and Windows client.

  • Get a Unix/Linux machine that you've an account and that supports SSH.
  • Create a directory, say /home/loginid/repository
  • On your Windows machine, download pageant.exe and puttygen.exe (at a minimum) from PuTTy site.
  • Start puttygen.exe, generate your private and public key pair. Save private key somewhere. C:\Documents and Settings\\.ssh\id_rsa is a good choice. Exit puttygen.exe.
  • In your server, make a .ssh directory, create a new file called authorized_keys, paste the public key into that file without any line breaks.
  • Start pageant.exe. Right click on the icon (terminal with a hat) on the system tray, add key and choose the private key file.
  • Download and install Bazaar Windows version.
  • Start it Bzr in command line.
  • Create the remote repository like bzr init sftp://username@remote/home/loginid/repository
  • Create a local directory, create a repository there too.
  • Add some files to local repository.
  • When you want to push it up to remote, do bzr push sftp://username@remote/home/loginid/repository

For more info, read Bazaar doc.

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.

Oracle VPN

Peter O'Brien - Sat, 2009-12-26 17:31
Virtual Private Networks have made remote working and access to critical corporate resources so much easier over the past few years. In fact, there is so much about the way we work today that is dependent on being able to get at data remotely yet in a secure manner. The VPN diagram to the left provides a simple representation of what a VPN does. It creates a secure tunnel (red lines) for the transfer of data (green line) between two points.

So what do you do when you need to get some work done, but you've got your corporate laptop safely and securely locked in a drawer in the office?

There is a website for Oracle employees that has software downloads, remote access tools, patches and so on. It is called Oracle Desktop External Support and can be accessed over the internet without the corporate VPN. Almost all downloads on the site are suitable for installation and are licensed for use on Oracle employee's personal systems. I have the site on my synchronised bookmarks and it comes in very handy.

Happy Xmas to everybody !!!!!

Bas Klaassen - Fri, 2009-12-25 05:40
Bas Klaassenhttp://www.blogger.com/profile/04080547141637579116noreply@blogger.com0
Categories: APPS Blogs

Making Merge, Revert and Branching Easier with SVN

Susan Duncan - Wed, 2009-12-16 06:04
In the latest release of JDeveloper ( we've added new declarative UIs to help simplify those most crucial and complex of SVN tasks - Merging and Branching. These SVN operations can be tricky. They involve ensuring that you pick the correct location in your repository, the correct revision, and mistakes can be costly and time consuming to fix.

Take a look at the wizard below. This is available whether you choose Merge Working Copy or if you want to Merge a single file. My top tip is that you should almost always use Merge Working Copy, especially if you are working with ADF (Application Development Framework) as many of the XML metadata files for your application will have dependencies on Java files and ending up with missed or out of sync files on a merge of any sort can lead to problems.

In previous releases we simply gave you the ability to enter your URLs and revisions, almost as you would if using the command line to access SVN. But in JDeveloper we pride ourselves on providing declarative help for developers. This wizard is invoked from a checked out application context - so you are always working within your working copy.

In this first step you choose the type of merge you are undertaking, for instance Merge Selected Revision Range is used if you want to update a branch with changes from the trunk. Let's take a common scenario: User 1 is working on Branch 123_susan. She knows that primarily her work will not cause any conflict with other work on other branches or the trunk so she has not worried too much about merging the latest trunk code into her branch. However, she comes across a problem and asks User 2 to fix something in the trunk so she can progress. She is told the revision that contains the specific fix she needs so she uses Merge Selected Revision Range to get that revision into her branch. The dialog below shows the path to trunk/blues as the source and her working copy - her branch - is the destination for the merge.

The revision to be merged - 275 - was picked using the List Revisions dialog. This dialog is used whenever revisions need to be browsed or selected. Notice that selecting 275 in the top panel shows the details of that revision - the comments, the files, the action and the author
Now the user can complete move to step 3 of the wizard, choose other merge options and carry out a test merge to check her work and finally complete the merge of that one revision into her working copy. Finally, she will commit her working copy back to its branch on the repository as normal.

Once she has completed the work on her branch she will be ready to use the Re-integrate A Branch merge to get it back into the trunk of the development. As always, the way to do this is to carry out any merge into a local working copy and then commit. So the first step for the user is to do a final commit of her branch. At this stage she can remove her branched working copy as it is finished with. She checks out the latest trunk revision and now she is ready to merge her branch to her new (trunk) working copy. Let's look at that another way:

1. A branch is created from trunk
2. User 1 checks out working copy from branch and edits code
3. Commits back to branch periodically
4. User 2 commits revision 200 to trunk and User 1 merges that revision to the working copy
5. Finishes coding working copy and commits to branch
6. Checks out new working copy from trunk
7. Merges branch into working copy
8. Commits working copy back to trunk

Before Subversion 1.5 the merge of a single revision in step 4 would have caused problems when the final merge of the branch back to the trunk was done (step 7). But SVN now has merge tracking so it keeps track of the revisions that have already been merged so that the final merge of all revisions goes ahead without error.

The third merge option is Merge Two Different Trees. This allows you to compare two different branches and merge and differences back into your working copy. This is the most complicated and, I would say, the least used of the merges.

One final tip - what if you want to revert your working copy to a specific revision? Use the Merge Selected Revision Range option. In List Revisions select the range of revisions from the latest to the revision you want to revert to and click OK. This will enter the range. But the range list will be ascending - eg 258-359. To revert to revision 258 simply reverse the range order and hey presto! a revert back rather than a merge up will be done - try it out in the test merge panel to check it - and you're done!

Connecting to DB2 using JDeveloper

Peeyush Tugnawat - Tue, 2009-12-15 13:53


Get the following Jar files from the DB2 server and store them in a local folder




Follow the following steps











Browse to where you have these three jar files












Other References:



Connecting to DB2 using JDeveloper

Peeyush Tugnawat - Tue, 2009-12-15 13:53


Get the following Jar files from the DB2 server and store them in a local folder




Follow the following steps











Browse to where you have these three jar files












Other References:



Working with Versioned Database Models

Susan Duncan - Tue, 2009-12-15 08:47
What? 3 posts in 2 days? After months of silence? Isn't it amazing how time flies. Here in the JDeveloper Product Management group we've been busy all year with releases and conferences and many opportunities to get out and talk to people about the great features in our tool. But as the year draws nearer to a close and all the traveling is over I've finally got time to get out on my blog some of the things that I've been evangelizing about all year. As you'll have noticed, one of those is the great new functionality that we've introduced in database modeling.

Periodically I'm asked why we don't introduce a database repository to store database models. One that can be queried and can store versions of the modeled objects? We've been down that road (those of you who've been around for a while know what I'm talking about!) but think that what we have now in JDeveloper is much more flexible. What is it that you want from a repository? Amongst the most important answers to that question is the ability to store multiple versions of your database objects, to query and to compare them.

In previous posts I've demonstrated how you can use our Database Reporting to query your database models and output the results. In this post I want to show you how you can use JDeveloper's integrated versioning system capabilities not only to maintain multiple versions of your database model but how you can resolve any conflicts that arise when multiple users update the model, using a declarative interface.

In this example I am using Subversion (SVN), one of the versioning systems that is integrated in JDeveloper. It is an open source system, widely used in the application development world. I am not going to step through a complete process for versioning in this post, there are various tutorials, how-to, demos. white papers available on OTN and if you want more information on SVN there is a very good online book.

Here is a part of my application. Note that it contains an offline database model (Blues) and one schema (GOTTHEBLUES) containing a number of database objects. This is a project that I'm currently working on. It is stored in an SVN repository - each of the objects has a revision number next to it. Notice that the PERSONS table is at version 378. In SVN the repository revision number is advanced each time a check in of files is done. So this does not mean that the PERSONS table has been checked in 378 times, it shows that the last time PERSONS was checked in the repository moved to version 378. Likewise the last revision of PUBLICATIONS is 258, so PUBLICATIONS has not been changed since revision 258.

If I open PERSONS from the navigator - I get the declarative UI that allows me to edit that object. But that is not how the information is stored. JDeveloper stores its database models by object - in XML. Below is an extract of what you would see if you opened the underlying file PERSONS.table in a browser.

It's not impossible to read - the extract shows the detail for column LAST_NAME, a VARCHAR2 of length 25, with a comment that it was created via a DB Transform (from a class model). However, the declarative UI abstracts you from the raw XML.

But imagine if you needed to compare two versions of the PERSONS table - or you had two users who were both working on the PERSONS table and checked in their changes to the SVN repository? Comparing the raw XML files is possible, and JDeveloper does recognize XML formating, but in the latest version of JDeveloper ( there is a new declarative UI for that too.

Imagine this scenario: two users have checked out the latest version of the application. SVN uses a copy-modify-merge versioning approach. This means that when an application is checked out a copy of that application is created on the user's local machine. SVN does not keep a record of who and where copies are checked out. It is only interested when something is commited back to the repository. So, as a user I can check out a working copy, make changes to it and if I never commit those changes back to the repository, so be it. I could do some 'what if' type coding and then decide to discard the whole copy.

In my scenario the two users have been discussing the PERSONS table and agree that the length of the LAST_NAME column is too short at 25. Unfortunately, they both decided to modify the column length in their working copy. User 1 happens to be the first to commit her changes back to the repository - so it now has LAST_NAME with a length of 50.

Now user 2 modifies her working copy, editing LAST_NAME to length 40. As good practice dictates, she Updates her working copy with the latest revision from the repository - so she can resolve any code conflicts in her working copy prior to merging her copy back in.

In this case she finds that she has a conflict between her code and the repository that JDeveloper cannot resolve automatically. She sees this in her Application Navigator. It shows the PERSON table with a conflict overlay and the differing versions of the table are also listed (see below)

But help is at hand. Double-clicking on PERSONS 379 opens the three-panel Merge window. On the left is her local version of PERSONS - with length 40, on the right is the repository version with length 50. After reviewing this she has shuffled her version into the middle Result of Merge panel. As that was the only conflict in the two files the Save and Complete Merge icon is enabled in the toolbar.
Once she saves this merge and refreshes the Application Navigator the extra conflict files will disappear and the updates will be applied to her working copy. Now she is ready to commit her working copy back to the repository so that it reflects the latest code position.

Not all changes made by multiple users cause conflicts. If the changes are complimentary - for instance one user adds a new column LOCATION and another uses changes the type of PERSONS_TYPE these changes will be added to the repository as SVN and JDeveloper recognize that there is no conflict. This is how SVN works by default, copy-modfy-merge in action. Many systems work this way, and others use the lock-modify-unlock paradigm.

If you want to review changes to a database object prior to updating your working copy, you can use one of the Compare options (Compare with Latest, with Previous, with Other Revision) before you update your working copy with the repository contents. In the example below, user 1 has used Compare with Latest to check her changes: A change to the size of FIRST_NAME and a new column COUNTRY - as shown in the right hand panel with the latest version in the repository (as shown in the left hand panel).

Finally, in this post, a short list of other tips
  • Using Compare with Other Revision don't forget to scroll to the right - and see not only revision numbers but the commit notes. Also use the filters optimize the revisions listed

  • If you make changes to a file but want to revert back to the version you checked out from the repository - use menu Versioning - Revert

  • Use the graphical Version Tree to review branches and versions of your objects

Visualizing the DB Reporting Metamodel

Susan Duncan - Tue, 2009-12-15 05:31
In my last post I gave examples of using both the pre-built reports and building custom reports on your database models.
As a P.S. to that post I want to point you to the JDeveloper Help system that comes with diagrams to help you define and understand the Database Reporting metamodel. Below is the JDEV table of contents - open at Working with Database Reports

and one small part of the Tables Report Metamodel to give you the idea. Have fun!


Subscribe to Oracle FAQ aggregator