Development

On Thursday I had a final meeting with the directors of OBUG (Oracle BeNeLux User Group) about setting up an APEX SIG.

Oracle Application Express is getting more popular in Belgium, the Netherlands and Luxembourg, so it made sense to bring all these people together and talk about it.

So from Thursday onwards in the diagrams of the OBUG you'll see an APEX SIG now. The OBUG has a big one-day event in March called "Benelux Connect", so probably on that day we'll do some APEX sessions there. Next to that, the goal is to have at least two meetings a year with the APEX SIG. Next to the technical presentations, the Use Cases and sharing knowledge, the networking and having fun is an important part.

We are working on a new website for OBUG, which will go live in January and on there you will find more information about the APEX SIG.

We have many more ideas and I hope all of the APEX enthusiasts and people working with this wonderful tool in the BeNeLux will join.

While we wait for the official website to be launched you can register your interest in the APEX SIG here. All registered people will be kept informed... so register now!

PS: For the people thinking the current OBUG site is made in ASP, you are right, but the register section is an iFrame to an APEX app ;-)

Next week is the annual UK Oracle User Group. This year I will be presenting "Modernizing Oracle Forms: GoogleMaps, Web Services, and Web 2.0" and "How to become a Fusion Developer with no Java".

The Forms sessions is aimed at anyone who has an investment in Oracle Forms and wants to know Oracle's official recommendation on modernizing that investment. As always, I try to make my sessions as open as possible, so if you have any questions about Forms and want the official position from the guys who knows ;o) then come along.

My second session is aimed at anyone who wants to get up to speed on Fusion development with JDeveloper/ADF in as short a time as possible. If you though ADF/JDeveloper was all about low level coding and technology then think again! It is a two hour masterclass and I'll be building a fully functioning ADF application from start to finish.

I'll also be involved in a number of roundtable sessions including "Development Tools" and "Fusion/SOA".

Current PL/SQL unit testing options

Unit testing and TDD (test driven development) practices are nowadays one of the key software development practices. It is especially important if you are doing agile software development in small iterations where you need to automate unit testing as much as possible, as you cannot do manual regression testing of all existing and new functionality at the end of each iteration.

In some languages (like Java, Ruby, Python, C# etc.) there is quite good tools and frameworks support for unit testing and as a result there is quite high testing culture among top developers in these communities. But unfortunately in PL/SQL community so far automated unit testing is not used very often. During recent Oracle OpenWorld conference in presentations about unit testing when it was asked who is doing automated unit testing then only few hands were raised.

Why is it so? And what are current options for doing automated PL/SQL unit testing?

The first unit testing framework for PL/SQL was utPLSQL which was created by Steven Feuerstein and based on API defined by many other xUnit style frameworks (like e.g. JUnit). But the issue with this approach was that PL/SQL syntax for tests was quite verbose and tests were not very readable (see example). As a result Steven stopped developing further utPLSQL and currently there are no other active maintainers of this project. There are some other alternative frameworks which tried to simplify writing tests in PL/SQL (OUnit, pl/unit, PLUTO etc.) but none of them are very actively used and maintained by PL/SQL community.

Because of the issues with utPLSQL Steven Feuerstein started development of graphical interface tool for PL/SQL unit testing which is now Quest Code Tester for Oracle. This tool is actively developed and maintained by Quest Software but there are several issues with it:

• It is a commercial tool and as a result it will not become widely accepted by all PL/SQL developers. There is also a freeware edition of it but the functionality of it is very limited.
• It is a graphical tool – it can help you with quick creation of simple tests but when you will need more complex logic you might get stuck that you cannot do it (or you need to do it again in plain PL/SQL and have the same issues as in utPLSQL).
• It stores tests in database repository – and it means that it might be hard to maintain unit tests in version control system like Subversion or Git.

And finally also Oracle started to do something in PL/SQL unit testing area and there is unit testing support in latest SQL Developer version 2.1 which currently still is in early adopter status. SQL Developer has very similar approach to Quest Code Tester – it is graphical tool which stores tests and test results in repository. So the benefit of SQL Developer over Quest Code Tester is that it is free :) But compared to Quest Code Tester it still has less features (e.g. currently not all complex data types are supported) and still is not released as final version and still has bugs.

Ruby as testing tool for PL/SQL

As you probably know I am quite big Ruby fan and always exploring new ways how to use Ruby to increase my productivity. And Ruby community has very high testing culture and has many good tools for testing support (I like and use RSpec testing framework). Therefore some time ago I started to use Ruby and RSpec also for testing PL/SQL code in our projects where we use Ruby on Rails on top of Oracle databases with existing PL/SQL business logic.

I have created ruby-plsql library which provides very easy API for calling PL/SQL procedures from Ruby and recent ruby-plsql version supports majority of PL/SQL data types.

So let’s start with with simple example how to use Ruby, RSpec and ruby-plsql to create PL/SQL procedure unit test. I will use BETWNSTR procedure example from utPLSQL examples:

CREATE OR REPLACE FUNCTION betwnstr (
   string_in   IN   VARCHAR2,
   start_in    IN   INTEGER,
   end_in      IN   INTEGER
)
   RETURN VARCHAR2
IS
   l_start PLS_INTEGER := start_in;
BEGIN
   IF l_start = 0
   THEN
      l_start := 1;
   END IF;
   RETURN (SUBSTR (string_in, l_start, end_in - l_start + 1));
END;

I took example tests from utPLSQL and wrote them in Ruby and RSpec:

describe "Between string" do
  it "should be correct in normal case" do
    plsql.betwnstr('abcdefg', 2, 5).should == 'bcde'
  end
  it "should be correct with zero start value" do
    plsql.betwnstr('abcdefg', 0, 5).should == 'abcde'
  end
  it "should be correct with way big end value" do
    plsql.betwnstr('abcdefg', 5, 500).should == 'efg'
  end
  it "should be correct with NULL string" do
    plsql.betwnstr(nil, 5, 500).should be_nil
  end
end

As you can see the tests are much shorter than in utPLSQL and are much more readable (also more readable than utPLSQL template which can be used to generate utPLSQL tests). And also you can create these tests faster than using GUI tools like Quest Code Tester or SQL Developer.

More complex example

Second more complex example I took from SQL Developer unit testing tutorial. We will create tests for PL/SQL procedure AWARD_BONUS:

CREATE OR REPLACE
 PROCEDURE award_bonus (
  emp_id NUMBER, sales_amt NUMBER) AS
  commission    REAL;
  comm_missing  EXCEPTION;
BEGIN
  SELECT commission_pct INTO commission
    FROM employees2
      WHERE employee_id = emp_id;
  IF commission IS NULL THEN
    RAISE comm_missing;
  ELSE
    UPDATE employees2
      SET salary = NVL(salary,0) + sales_amt*commission
        WHERE employee_id = emp_id;
  END IF;
END award_bonus;

I didn’t quite like the testing approach in SQL Developer unit testing tutorial – it was assuming that there is already specific data in employees2 table and was testing procedure using specific primary key values. As a result tests are not very readable as you cannot see all input data in the test case and tests could easily broke if initial data in table are different.

Therefore I created tests in Ruby using better approach that each test creates all necessary data that are needed for it and at the end of test there are no side effects which can influence other tests:

describe "Award bonus" do
  include CustomerFactory

  [ [1000,  1234.55,  0.10,   1123.46],
    [nil,   1234.56,  0.10,   123.46],
    [1000,  1234.54,  0.10,   1123.45]
  ].each do |salary, sales_amt, commission_pct, result|
    it "should calculate base salary #{salary.inspect} + sales amount #{sales_amt} * commission percentage #{commission_pct} = salary #{result.inspect}" do
      employee = create_employee(
        :commission_pct => commission_pct,
        :salary => salary
      )
      plsql.award_bonus(employee[:employee_id], sales_amt)
      get_employee(employee[:employee_id])[:salary].should == result
    end
  end
end

I am generating three different tests with three different sets of input values. When you run these tests you see result:

Award bonus
- should calculate base salary 1000 + sales amount 1234.55 * commission percentage 0.1 = salary 1123.46
- should calculate base salary NULL + sales amount 1234.56 * commission percentage 0.1 = salary 123.46
- should calculate base salary 1000 + sales amount 1234.54 * commission percentage 0.1 = salary 1123.45

In addition I am using factory pattern (create_customer method) for test data creation. When using factory pattern you create test data creation method which will create valid new record with default field values. If in your test you need some specific non-default values then you can pass just these values as parameters to factory method. Factory pattern also helps in the maintenance of tests. For example, if new mandatory columns will be added to employees table then it will be necessary to add new fields with default values in factory methods and nothing should be changed in individual tests.

Here is example of employee factory implementation:

module EmployeeFactory
  # Creates new employee with valid field values.
  # Pass in parameters only field values that you want to override.
  def create_employee(params)
    employee = {
      :employee_id => plsql.employees2_seq.nextval,
      :last_name => 'Last',
      :email => 'last@example.com',
      :hire_date => Date.today,
      :job_id => plsql.jobs.first[:job_id],
      :commission_pct => nil,
      :salary => nil
    }.merge(params)
    plsql.employees2.insert employee
    get_employee employee[:employee_id]
  end

  # Select employee by primary key
  def get_employee(employee_id)
    plsql.employees2.first :employee_id => employee_id
  end
end

And here is additional test for testing if procedure will raise exception if one input value is missing:

  it "should raise ORA-06510 exception if commission percentage is missing" do
    salary, sales_amt, commission_pct = 1000,  1234.55,  nil
    employee = create_employee(
      :commission_pct => commission_pct,
      :salary => salary
    )
    lambda do
      plsql.award_bonus(employee[:employee_id], sales_amt)
    end.should raise_error(Exception, /ORA-06510/)
  end

How to use it

I hope that if you are looking for PL/SQL unit testing tool then you will try this out :) You can get examples from this article together with necessary setup code and installation instructions at http://github.com/rsim/ruby-plsql-spec.

If you have any feedback or questions or feature suggestions then please comment.

I'm pleased to say that Oracle University have confirmed two 1-day masterclasses on Oracle ADF and Oracle JDeveloper to be delivered in Germany in Feb and the Czech Republic in April.
These courses will be aimed at rapidly getting developers up to speed in Oracle ADF and Fusion development using JDeveloper.

Other events are being lined up as well.

I have just released ruby-plsql gem version 0.4.0 which provides many new features. You can read about initial versions of ruby-plsql in previous blog posts.

Oracle complex data type support

Initial versions of ruby-plsql supported just simple Oracle types like NUMBER, VARCHAR2, DATE, TIMESTAMP, CLOB, BLOB as PL/SQL procedure parameters. Now support for many more complex data types is added. See examples below how to call PL/SQL procedures with these complex data types.

PL/SQL Record

Let’s assume you have PL/SQL procedure with PL/SQL record type parameter (which most typically will be in table%ROWTYPE format):

CREATE TABLE test_employees (
          employee_id   NUMBER(15),
          first_name    VARCHAR2(50),
          last_name     VARCHAR2(50),
          hire_date     DATE
        );
CREATE OR REPLACE FUNCTION test_full_name (p_employee test_employees%ROWTYPE)
RETURN VARCHAR2 IS
BEGIN
  RETURN p_employee.first_name || ' ' || p_employee.last_name;
END;

Then you can create Ruby Hash with record field values (specifying field names as Symbols), e.g.:

p_employee = {
  :employee_id => 1,
  :first_name => 'First',
  :last_name => 'Last',
  :hire_date => Time.local(2000,01,31)
}

and pass this Hash as a parameter which will be translated to PL/SQL record parameter by ruby-plsql:

plsql.test_full_name(p_employee) #=> "First Last"
# or
plsql.test_full_name(:p_employee => p_employee) #=> "First Last"

In the same way you can get PL/SQL function return values or output parameter values as Hash values.

Object type

In similar way also object type parameters can be passed as Hash values. In this case also nested objects or nested collections of objects are supported:

CREATE OR REPLACE TYPE t_address AS OBJECT (
  street    VARCHAR2(50),
  city      VARCHAR2(50),
  country   VARCHAR2(50)
);
CREATE OR REPLACE TYPE t_phone AS OBJECT (
  type            VARCHAR2(10),
  phone_number    VARCHAR2(50)
);
CREATE OR REPLACE TYPE t_phones AS TABLE OF T_PHONE;
CREATE OR REPLACE TYPE t_employee AS OBJECT (
  employee_id   NUMBER(15),
  first_name    VARCHAR2(50),
  last_name     VARCHAR2(50),
  hire_date     DATE,
  address       t_address,
  phones        t_phones
);
CREATE OR REPLACE FUNCTION test_full_name (p_employee t_employee)
  RETURN VARCHAR2
IS
BEGIN
  RETURN p_employee.first_name || ' ' || p_employee.last_name;
END;

and from Ruby side you can call this PL/SQL function as:

p_employee = {
  :employee_id => 1,
  :first_name => 'First',
  :last_name => 'Last',
  :hire_date => Time.local(2000,01,31),
  :address => {:street => 'Main street 1', :city => 'Riga', :country => 'Latvia'},
  :phones => [{:type => 'mobile', :phone_number => '123456'}, {:type => 'home', :phone_number => '654321'}]
}
plsql.test_full_name(p_employee) #=> "First Last"
# or
plsql.test_full_name(:p_employee => p_employee) #=> "First Last"

And also object type return values and output parameters will be returned as Ruby Hash values (with nested Hashes or Arrays if necessary).

There is one limitation that these object types should be defined as database types and not just inside PL/SQL package definition. Unfortunately you cannot access type definitions inside packages from OCI or JDBC drivers and as a result cannot call such procedures from outside of PL/SQL.

TABLE and VARRAY collections

TABLE and VARRAY collection parameters can be passed as Array values:

CREATE OR REPLACE TYPE t_numbers AS TABLE OF NUMBER(15);
CREATE OR REPLACE FUNCTION test_sum (p_numbers IN t_numbers)
  RETURN NUMBER
IS
  l_sum   NUMBER(15) := 0;
BEGIN
  IF p_numbers.COUNT > 0 THEN
    FOR i IN p_numbers.FIRST..p_numbers.LAST LOOP
      IF p_numbers.EXISTS(i) THEN
        l_sum := l_sum + p_numbers(i);
      END IF;
    END LOOP;
    RETURN l_sum;
  ELSE
    RETURN NULL;
  END IF;
END;

And from Ruby side:

plsql.test_sum([1,2,3,4]) #=> 10

CURSOR

You can get also cursor return values from PL/SQL procedures:

CREATE OR REPLACE FUNCTION test_cursor
  RETURN SYS_REFCURSOR
IS
  l_cursor  SYS_REFCURSOR;
BEGIN
  OPEN l_cursor FOR
  SELECT * FROM test_employees ORDER BY employee_id;
  RETURN l_cursor;
END;

can be called from Ruby in the following way:

plsql.test_cursor do |cursor|
  cursor.fetch #=> first row from test_employees will be returned
end

It is important to pass block parameter in this case and do something with returned cursor within this block as after ruby-plsql finishes PL/SQL procedure call it will close all open cursors and therefore it will not be possible to do anything with returned cursor outside this block.

It is also possible to use returned cursor as input parameter for another PL/SQL procedure:

CREATE OR REPLACE FUNCTION test_cursor_fetch(p_cursor SYS_REFCURSOR)
  RETURN test_employees%ROWTYPE
IS
  l_record  test_employees%ROWTYPE;
BEGIN
  FETCH p_cursor INTO l_record;
  RETURN l_record;
END;

plsql.test_cursor do |cursor|
  plsql.test_cursor_fetch(cursor) #=> first record as Hash
end

Note: you can pass cursors as PL/SQL procedure input parameter just when using ruby-plsql on MRI 1.8/1.9 with ruby-oci8, unfortunately I have not found a way how to pass cursor as input parameter when using JRuby and JDBC.

BOOLEAN

And finally you can use also PL/SQL BOOLEAN type – it is quite tricky data type as it is supported just by PL/SQL but not supported as data type in Oracle tables. But now you can also use it with ruby-plsql:

CREATE OR REPLACE FUNCTION test_boolean
  ( p_boolean BOOLEAN )
  RETURN BOOLEAN
IS
BEGIN
  RETURN p_boolean;
END;

plsql.test_boolean(true) #=> true

You can find more PL/SQL procedure call usage examples in ruby-plsql RSpec tests.

Table and sequence operations

I have been using and promoting to others ruby-plsql as PL/SQL procedure unit testing tool. As current PL/SQL unit testing tools are not so advanced and easy to use as Ruby unit testing tools then I like better to use Ruby testing tools (like RSpec) together with ruby-plsql to write short and easy to understand PL/SQL unit tests.

In unit tests in setup and teardown methods you typically need some easy way how to create some sample data in necessary tables as well as to validate resulting data in tables after test execution.

If you are Ruby on Rails developer then you probably will use ActiveRecord (or DataMapper) for manipulation of table data. But if Ruby is used just for unit tests then probably ActiveRecord would be too complicated for this task.

Therefore I added some basic table operations to ruby-plsql which might be useful e.g. in unit tests. Some syntax ideas for these table operations are coming from Sequel Ruby library.

INSERT

# insert one record
employee = { :employee_id => 1, :first_name => 'First', :last_name => 'Last', :hire_date => Time.local(2000,01,31) }
plsql.employees.insert employee # INSERT INTO employees VALUES (1, 'First', 'Last', ...)

# insert many records
employees = [employee1, employee2, ... ]  # array of many Hashes
plsql.employees.insert employees

If primary key values should be selected from sequence then you can get next sequence values with

plsql.employees_seq.nextval # SELECT employees_seq.NEXTVAL FROM dual
plsql.employees_seq.currval # SELECT employees_seq.CURRVAL FROM dual

SELECT

# select one record
plsql.employees.first # SELECT * FROM employees
                      # fetch first row => {:employee_id => ..., :first_name => '...', ...}
plsql.employees.first(:employee_id => 1)  # SELECT * FROM employees WHERE employee_id = 1
plsql.employees.first("WHERE employee_id = 1")
plsql.employees.first("WHERE employee_id = :employee_id", 1)
# select many records
plsql.employees.all                       # => [{...}, {...}, ...]
plsql.employees.all(:order_by => :employee_id)
plsql.employees.all("WHERE employee_id > :employee_id", 5)

# count records
plsql.employees.count                     # SELECT COUNT(*) FROM employees
plsql.employees.count("WHERE employee_id > :employee_id", 5)

UPDATE

# update records
plsql.employees.update(:first_name => 'Second', :where => {:employee_id => 1})
                      # UPDATE employees SET first_name = 'Second' WHERE employee_id = 1

DELETE

# delete records
plsql.employees.delete(:employee_id => 1) # DELETE FROM employees WHERE employee_id = 1

Other SQL statements

Any other SELECT statement can be executed with

plsql.select :first, "SELECT ..."
# or
plsql.select :all, "SELECT ..."

or any other non-SELECT SQL statement can be executed with

plsql.execute "..."

And also COMMIT or ROLLBACK could be executed simply with

plsql.commit
plsql.rollback

I plan to write a separate blog post about how I recommend to create PL/SQL unit tests using Ruby and ruby-plsql and RSpec.

Install

As always you can install latest version of ruby-plsql with

gem install ruby-plsql

Latest gem version is just on Gemcutter but now it should be available as default gem source for all Ruby installations.

And as always ruby-plsql is supported both on

• Ruby 1.8.6/1.8.7 or Ruby 1.9.1 with ruby-oci8 gem version 2.0.3 or later (some specific issues with complex data types will be fixed in later versions of ruby-oci8)
• JRuby 1.3/1.4 with Oracle JDBC driver (testing mainly with ojdbc14.jar but also ojdbc5.jar or ojdbc6.jar should be fine)

Please try it out and tell me if there are any issues with some particular data types or if there are still some unsupported PL/SQL data types that you would like to be supported in ruby-plsql. And also I encourage you to try ruby-plsql out for PL/SQL unit testing if you had no PL/SQL unit tests previously :)

A frequent question we get from beginners is how to I choose a value on one page and pass it to another page to show a specific record or set of records. In previous blog posts I showed how easy... shay.shmeltzer http://blogs.oracle.com/shay

I just posted the latest version of logger, my PL/SQL logging utility to https://logger.samplecode.oracle.com/.  There are a number of new features as well as bug fixes, many of which were suggested by other people in the comments of my previous blog post on logger.  You can view the new features and fixes in the “Change [...]

I spent last week in Europe, a very nice trip in many ways. I was invited by the German Oracle User Group (DOAG) to do a keynote presentation at their annual conference in Nurnberg (Nuremberg). So I built a week of activities around that event.

First of all, I flew out to Germany from North Carolina, where I'd spent the weekend watching my nephew, Brian Daniels, play in his last college football game (actually in Danvilla, Virginia, at Averett University). Now he will continue to train hard, attend the Spring 2010 Combine, and hopefully be invited by an NFL (or other professional football league) team to actually make a living from football. Talk about tough jobs.

I have discovered that it's quite a lot fun hanging out with college football players. In my experience, these massive, heavily muscled, incredibly disciplined young men are also unfailingly polite and very sweet. Big teddy bears. Until they get on the field. Then it's all business.

Well, so from Raleigh, NC, my wife Veva went home on Sunday - and I took a flight to Heathrow. Then after a few hours layover (spent comfortably in the British Airways premier lounge in Terminal 5), I took another plane to Frankfurt, and from there a 2.5 hour train ride - and finally I was in Nurnberg. I was up for over 30 hours, but I must say my body is a strange machine. It doesn't really object too much to such situations. In fact, when I got to the hotel after being awake for some 25 hours, I went down to the fitness and exercised for an hour.

In any case, the DOAG conference was incredibly well-organized and well-attended. I did a short technical talk on Tuesday, organized at the last moment, and still had an overflow crowd of developers show up. DOAG asked me for a "head shot", so I sent my standard zip file with a wide variety of photos, mostly just to be entertaining. For example, it contains this photo:

But that doesn't mean they are supposed to use such photos, correct? Well...you never know. So DOAG (and Quest Germany) apparently decided that they wanted a somewhat "formal" appearance for me. So I arrived at the conference center and found variations of the following photo appear on agendas and signs and so forth:

I haven't seen me in a tuxedo in a long time! I sure didn't wear a tux at the conference. Heck, I didn't even bother with a suit. Lots of people still wear suits in Germany, I have found.

On Tuesday night, the DOAG board invited me (and all other English speakers) to dinner at a traditional German restaurant (the Golden Posthorne). That was fun, though I definitely ended up eating way more meat than I am used to.

On Wednesday, I did my keynote talk: Guarantee Application Success. I was determined to improve over my last keynote performance for DOAG several years ago. I really didn't think I did a very good job back then. I mostly presented the ideas of someone else (Lawrence Lessig - Code as Law) and I talked way too quickly. A number of people came up afterwards and said "That was a great presentation, Steven, but I could only understand half of what you said." That made me feel really bad.

So this year, the keynote contained all and only my own ideas, which meant I could be a bit more comfortable in my presentation. From what I can tell, it was well accepted. I certainly had a good time doing a talk in a hall that probably held 400-500 people, and offered not only a projection of my Powerpoint on a giant screen, but also threw up an image of me speaking that must have been 50 feet high.

Afterwards, I also sold and signed a bunch of my Oracle PL/SQL Programming, 5th edition. Then a hurried and truncated "Ask Steven" session, then back to the train station, and off to Prague.

So on Wednesday night I took a late flight (10 PM) to Prague and then spent the next two days teaching my Best of PL/SQL class to 25 students. This was my third class in Prague and each time I have been impressed by the quality of the students. Lots of really excellent questions, critiques of my code, new ideas for me to pursue....and I got to enjoy Prague for a little while.

It is a delightful city, especially the sidewalks and especially because I have had the very good fortune to become friends with some current and former employees of Oracle, and they seem to be perfectly to take me out for excellent meals. I had a wonderful dinner with Roman and Lenka at Trattorio Cicala on Thursday (lots of photos on the wall of the owner with big movie starts - Sean Connery, Johnny Depp, Brad Pitt, Vin Diesel - but he didn't ask to take a picture with me. How insulting! Didn't he know who I was? Guess not...), and then on Friday at La Finestra with Petra. Oh and a fine but quick lunch with Roman, Lenka and Martin, who manages the Oracle University team there in Prague. Thanks for organizing the class, Martin!

Then on Saturday morning it was time to go home, or so I thought. I got to the Prague Airport 1.5 hours in advance - and found the entire airport shut down. There had been a fire, apparently. All systems were down, no one could check in for their flight....within an hour, my British Airways flight was cancelled and I was starting to contemplate having to stay an extra night in Prague - or London/Heathrow, but then I got onto a Czech Airlines flight, and was able to catch the third and last American Airlines plane back to Chicago. We are, in fact, starting our descent into my home city right now. [At which I found out that my suitcase was not on the plane; a day later, though, it had been delivered to my house.]

Unfortunately, I go back to an empty house. About 10 days ago, my son, Chris, fell while skateboarding and broke his left leg - badly. Now he has a titanium pin in his left leg and needs lots of help. So my wife, a former nurse, flew down a few days ago to assist.

Well, my house may be empty, but it's still home and I very much look forward to sleeping in my own bed, making my own bowl of old-fashioned oatmeal for breakfast, and so on.

And in six days, I fly back to Europe, to Birmingham, UK for the UKOUG conference!

See some of you there....
SF

I extended the APEX Blogs aggregator to also include Twitter messages.

A few years ago blogs were hot to exchange knowledge, but writing blog posts takes time. These days people use Twitter messages to post very short messages. But I find it very useful as you know what people are doing e.g. Patrick Wolf tweeted he was working on a Required Flag for an item in APEX 4.0.

As I think a lot of people miss out on these short (but interesting) messages I decided to include it on the site.


If you want to appear on the site you only need to put #apexblogs in your twitter message and in real-time your message will appear on the site.

I used some code of OraTweet to make this happen. Thanks to Noel Portugal to share that nice app.

Have fun with the tweets! Hope you like it.

At Oracle Openworld I was part of the SOA & BPM Partner Advisory Council ( in this well organized meeting by Jürgen Kress). In this meeting David Shaffer showed us the Soa Suite roadmap. I didn't know Oracle published this document on OTN but here are the highlights of this document.

First the release calendar, Patch Set1 and Soa Suite 10.1.3.5 for OC4J and WLS are already out, So we can expect the coming 4, 5 months BPM 11g , OSB 11g and off course at last Patch Set 2
The new features of PS1, like Soa composer, Spring preview support, invocation api

PS2 will probably bring us composite folders ( domains ) , BPEL 2.0, Spring support in production, Direct binding between OSB and Soa Suite

And maybe Soa Suite 11G release 2 will have a light soa console, Rest / JSON & Debugger support

For more info see the original document on OTN.

Download and See David Shaffer Soa Suite roadmap yourself

I must admit that I wasn't aware of this and I'm guessing that if this is news for me it might be news for other people so I wanted to blog about it. Turns out that the af:inputDate components in... shay.shmeltzer http://blogs.oracle.com/shay

We did a lot of enhancements in the visual editor in the new JDeveloper 11g 11.1.1.2. I recorded this short 3 minute demo to show you a couple of the things that are a bit hidden, but can be useful... shay.shmeltzer http://blogs.oracle.com/shay

TaskFlows (or the ADF controller) is one of the more powerful features of ADF - but also one that many people find hard to grasp. We've just published a new tutorial about TaskFlows on OTN that shows some of the... shay.shmeltzer http://blogs.oracle.com/shay

I want to find and expand all nodes of an ADF Tree and I saw an Oracle Forum post of Kenyatta which gave me a nice solution.
First some usefull methods.

private void expandTreeChildrenNode(  RichTree rt
                                   ,  FacesCtrlHierNodeBinding node
                                   ,  List<Key> parentRowKey) {
     ArrayList children = node.getChildren();
     List<Key> rowKey;

     if ( children != null )    {
       for (int i = 0; i < children.size(); i++) {
          rowKey = new ArrayList<Key>();
          rowKey.addAll(parentRowKey);
          rowKey.add(((FacesCtrlHierNodeBinding)children.get(i)).getRowKey());
          rt.getDisclosedRowKeys().add(rowKey);
          if (((FacesCtrlHierNodeBinding)(children.get(i))).getChildren() == null)
            continue;
            expandTreeChildrenNode(rt
                                  ,(FacesCtrlHierNodeBinding)(node.getChildren().get(i))
                                  , rowKey);
          }
     } 
}

 // find a jsf component
private UIComponent getUIComponent(String name) {
     FacesContext facesCtx = FacesContext.getCurrentInstance();
     return facesCtx.getViewRoot().findComponent(name) ;
}

private UIComponent getUIComponent(UIComponent component,String name ){
     List<UIComponent> items = component.getChildren(); 
     for ( UIComponent item : items ) { 
       UIComponent found = getUIComponent(item,name);
       if ( found != null ) {
         return found; 
       }
       if ( item.getId().equalsIgnoreCase(name)  ) { 
         return item;
       }; 
     }
     return null;
} 

Now find the ADF tree in a region and expand the main and child nodes of this tree

// get the dymamic region of the main page
RichRegion region = (RichRegion)getUIComponent("dynam1");

if ( region != null) {
   // find tree 2 and expand this tree 
   RichTree rt = (RichTree)getUIComponent(region,"t2");
   if ( rt != null ) {
      int rowCount = rt.getRowCount();
      List<Key> rowKey;
      for (int j = 0; j < rowCount; j++) {
         // expand the main nodes
         FacesCtrlHierNodeBinding node = (FacesCtrlHierNodeBinding)rt.getRowData(j);
         rowKey = new ArrayList<Key>();
         rowKey.add(node.getRowKey());
         rt.getDisclosedRowKeys().add(rowKey);
         rt.setRowKey(rowKey);
         // expand the child nodes of the main nodes
         expandTreeChildrenNode(rt , node, rowKey);
       }   
    }
}

When you want to find an UIComponent (like a Tree) inside an ADF Region you can not use findComponent on the ViewRoot because this will only search for the component in the JSF page and not in the JSF page fragments ( Task Flows). And off course you can use a backing bean to make a binding but I want to find the component by first searching for the right Region in the JSF page and then searching the component inside the Region.

First I need to have some common methods.

// find a jsf component inside the JSF page
private UIComponent getUIComponent(String name) {
     FacesContext facesCtx = FacesContext.getCurrentInstance();
     return facesCtx.getViewRoot().findComponent(name) ;
}

// find a UIComponent inside a UIComponent 
private UIComponent getUIComponent(UIComponent component,String name ){
  List items = component.getChildren(); 
  for ( UIComponent item : items ) { 
     UIComponent found = getUIComponent(item,name);
     if ( found != null ) {
        return found; 
      }
     if ( item.getId().equalsIgnoreCase(name)  ) { 
        return item;
     } 
  }
  return null;
}

Now we can find the right Region and then search inside the Region for the ADF Tree

// get the dymamic region of the main page
RichRegion region = (RichRegion)getUIComponent("dynam1");

if ( region != null) {
  // find tree 2 
  RichTree rt = (RichTree)getUIComponent(region,"t2");
  if ( rt != null ) {
  // do your thing
  }   
}

A couple of days ago the RSS feed of APEXBlogs.info started to behave weird. Although you could still read the message, the link at the bottom to redirect to the website suddenly didn't work anymore.

I didn't change anything but Feedburner is now under the Google flag and apparently that added statistics to the url.
e.g. &utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+OracleApexBlogAggregator+%28Oracle+APEX+Blog+Aggregator+%28by+AE%29%29

If you took that bit off it worked just fine.

I found the answer in the FeedBurner forum. You can disable all the statistics (not sure why they got enabled suddenly) so the url should become "normal" again, so that is what I did. Let's hope the new posts through RSS are correct again.

I've some new ideas for the website to include also the APEX related twitter messages and do a general update of the site to make it cleaner, but I didn't find the time to finish it yet.

If you have other ideas or recommendations for the site, feel free to add a comment. I'll take it into consideration for the new release.

We promised in an earlier post to cover some of the questions from the Optimizer round table discussion at Oracle Open World. Here's our first in a series of posts that will address these questions.

Q: When moving from 10g to 11g, should hints in existing SQL be removed?

A: I was glad to see this question at the round table, since I think this is something people often overlook when adding hints to an application. Over time, hints can become stale. You may have added a hint to a query because of a weakness in an old version of the optimizer, but the hint might no longer be necessary to get the best plan. Even worse, the hint may force a plan which is suboptimal, for a couple of reasons:

• Depending on how your data change over time, the plan choice that the hint enforces might no longer be a good choice. This problem can occur even without a database upgrade. For example, if the distribution of values in a column change over time, an old access path hint may no longer be appropriate.
• The hint might prevent new optimizations from taking place. So after an upgrade, you will be stuck using the old hint-enforced plan, when the optimizer could have chosen something better. For example, you may have hinted a plan for a query with bind variables, to avoid a bad plan choice due to bind peeking in the pre-11g optimizer. In 11g, you will not get the benefits of adaptive cursor sharing because of the hints in the query.
  

These are just some of the reasons why it is good to re-evaluate the hints in your application from time to time. You can test this out using the _optimizer_ignore_hints parameter. Setting this parameter to TRUE will cause the optimizer to ignore the hints embedded in queries. You can set this on the session level, run your workload, and compare the performance to your baseline performance (with the hints). I recommend using SQL Performance Analyzer (SPA) to do this. Read the SPA white paper for more information on how to do that.

If you find that some of your queries really do still require hints to get an optimal plan, you should consider creating a SQL plan baseline, and allowing SPM to manage the plan for you. If you do this, then you will get the hinted plan for now, but if a better plan comes along later (for one of the reasons mentioned earlier), you will eventually get the benefits of the new plan, using the SPM evolve process. There are two ways that you can create a SQL plan baseline based on your hints that will be applied to the query without hints. The first way requires that a SQL plan baseline already exist for the unhinted query. That's described in an earlier post. Here are the steps to use if you don't already have a SQL plan baseline for the unhinted query.

1. Run the query with hints, and confirm that the plan is what you want:

var pid number
exec :pid := 100;

select /*+ leading(t) */ p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid; 

PROD_NAME                                          AMOUNT_SOLD CALENDAR_YEAR
-------------------------------------------------- ----------- -------------
...

select sql_id from v$sql where sql_text like 'select /*+ leading(t) */ p.prod_name%';

SQL_ID
-------------
2qtu6hy4rf1j9

select * from table(dbms_xplan.display_cursor(sql_id=>'2qtu6hy4rf1j9', 
                                              format=>'basic note'));

EXPLAINED SQL STATEMENT:
------------------------
select /*+ leading(t) */ p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t where s.prod_id = p.prod_id and
s.time_id = t.time_id and p.prod_id < :pid

Plan hash value: 2290436051

---------------------------------------------------------------
| Id  | Operation                            | Name           |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |
|   1 |  HASH JOIN                           |                |
|   2 |   HASH JOIN                          |                |
|   3 |    TABLE ACCESS FULL                 | TIMES          |
|   4 |    PARTITION RANGE ALL               |                |
|   5 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |
|   6 |      BITMAP CONVERSION TO ROWIDS     |                |
|   7 |       BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |
|   8 |   TABLE ACCESS BY INDEX ROWID        | PRODUCTS       |
|   9 |    INDEX RANGE SCAN                  | PRODUCTS_PK    |
---------------------------------------------------------------

2. Load SQL plan baseline from cursor cache, and associate it with the unhinted query:

var sqltext clob;

begin
:sqltext := 'select p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id > :pid';
end;
/

exec :pls := dbms_spm.load_plans_from_cursor_cache( -
              sql_id => '2qtu6hy4rf1j9', -
              plan_hash_value => 2290436051, -
              sql_text => :sqltext);

3. Run the query without hints, and check that the SQL plan baseline was used.

select p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid;

PROD_NAME                                          AMOUNT_SOLD CALENDAR_YEAR
-------------------------------------------------- ----------- -------------
...

select * from table(dbms_xplan.display_cursor(sql_id=>'a1ax3265pq8x7',
                                              format=>'basic note'));

EXPLAINED SQL STATEMENT:
------------------------
select p.prod_name, s.amount_sold, t.calendar_year from sales s,
products p, times t where s.prod_id = p.prod_id and s.time_id =
t.time_id and p.prod_id < :pid

Plan hash value: 2290436051



---------------------------------------------------------------
| Id  | Operation                            | Name           |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |
|   1 |  HASH JOIN                           |                |
|   2 |   HASH JOIN                          |                |
|   3 |    TABLE ACCESS FULL                 | TIMES          |
|   4 |    PARTITION RANGE ALL               |                |
|   5 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |
|   6 |      BITMAP CONVERSION TO ROWIDS     |                |
|   7 |       BITMAP INDEX RANGE SCAN        | SALES_PROD_BIX |
|   8 |   TABLE ACCESS BY INDEX ROWID        | PRODUCTS       |
|   9 |    INDEX RANGE SCAN                  | PRODUCTS_PK    |
---------------------------------------------------------------
Note
-----
  - SQL plan baseline SQL_PLAN_4rw2dhryc2w5h888547d3 used for this statement 

Inspired by Eric Elzinga , who was wondering how MDS can work in Soa Suite 11g , I made some screenshots how you can use a XSD from a central MDS repository in your composite application. Clemens already blogged about re-using common metadata and he made a great ant utility to import or delete MDS files. For 11G R1 PS1 or higher use this instead of the Clemens utility
First I make a local MDS repository. If you install the Soa plugin you already have a seed folder in the integration folder. Under this folder create an new folder called apps. ( this have has to be apps else you will get a permission denied error ) . Under this apps folder we can create our own definitions.


To use my local SOA-MDS repository I create a new MDS File Connection

I want to re-use these common objects in every Soa project so I choose for the resource palette option

select the seed folder in the integration folder


Here we can see our common application objects.
Open the application resources window and open the adf-config.xml

Here we define a new metadata namespace with apps as path. And use the integration folder as metadata-path value.


We are ready to use these common objects in a mediator.. Here I will use a schema from the local MDS as input parameter for the mediator.

Import a new schema
Select the resource browser and here we can select our schema from the local MDS

I uncheck the Copy to project option, because this XSD already exists in the MDS

Our Project is ready but If we want to deploy this Soa project, we will receive a error, it can't find the schema. So we need to export the local MDS files to the SOA Suite database MDS.
To do this we have 2 options , the first option is to create a MAR deployment ( Application properties ) or do this with Ant.
I stripped the Clemens ant project so this ant build file has only two tasks , add and delete. It uses the adf-config.xml ( config folder) for the location of the target MDS and I use the local MDS as source.

Here is the target adf-config.xml which is located in the config folder

Change the build.properties so it matches your environment

This will import your local MDS object to the remote MDS. After this you can deploy your Soa Suite project.
Here you can download my ant project. Thanks to Clemens.

With the release of Soa Suite 11g R1 Patch Set 1 Oracle improved the standard ant scripts for MDS deployment and removal. Before PS1 we had an ant example of Clemens.
Basically this is how my ANT scripts works. First add your own metadata folders under the apps folder ( do this in jdeveloper\integration\seed\apps ).

My ANT script will do the following steps for every metadata folder under apps

• optionally remove the metadata folder from the remote Soa Suite Database MDS repository
  
• Make a zip file of the metadata files ( Local MDS file repository) .
• Make a new Soa Bundle zip with this metadata zip
• Deploy this soa bundle to the Soa Suite Server, The server will add this to the Database MDS
  

When you want to use the MDS files in your own project read this blog


To make this work copy the antcontrib jar to the jdeveloper\ant\lib folder ( because of the foreach and the propertycopy fucntion )
Here is my build.properties

# global
wn.bea.home=C:/oracle/MiddlewareJdev11gR1PS1
oracle.home=${wn.bea.home}/jdeveloper
java.passed.home=${wn.bea.home}/jdk160_14_R27.6.5-32
wl_home=${wn.bea.home}/wlserver_10.3

# temp
tmp.output.dir=c:/temp

mds.reposistory=C:/oracle/MiddlewareJdev11gR1PS1/jdeveloper/integration/seed/apps/
mds.applications=usarmy
mds.undeploy=true

deployment.plan.environment=dev

# dev deployment server weblogic
dev.serverURL=http://laptopedwin:8001
dev.overwrite=true
dev.user=weblogic
dev.password=weblogic1
dev.forceDefault=true

# acceptance deployment server weblogic
acc.serverURL=http://laptopedwin:8001
acc.overwrite=true
acc.user=weblogic
acc.password=weblogic1
acc.forceDefault=true

The build.xml

<?xml version="1.0" encoding="iso-8859-1"?>
<project name="soaDeployAll" default="deployMDS">
   <echo>basedir ${basedir}</echo>

   <property environment="env"/>
   <echo>current folder ${env.CURRENT_FOLDER}</echo>

   <property file="${env.CURRENT_FOLDER}/build.properties"/> 
   <taskdef resource="net/sf/antcontrib/antcontrib.properties"/>
   <import file="${basedir}/ant-sca-deploy.xml"/>

   <target name="unDeployMDS">
       <echo>undeploy MDS</echo>
       <foreach list="${mds.applications}" param="mds.application" target="undeployMDSApplication" inheritall="true" inheritrefs="false"/>
   </target>

   <target name="deployMDS">
       <echo>undeploy and deploy MDS</echo>
       <if>
         <equals arg1="${mds.undeploy}" arg2="true"/>
         <then>
           <foreach list="${mds.applications}" param="mds.application" target="undeployMDSApplication" inheritall="true" inheritrefs="false"/>
         </then>
       </if>
       <foreach list="${mds.applications}" param="mds.application" target="deployMDSApplication" inheritall="true" inheritrefs="false"/>
   </target>

   <target name="deployMDSApplication">
       <echo>deploy MDS application ${mds.application}</echo>

       <echo>remove and create local MDS temp</echo>
       <property name="mds.deploy.dir" value="${tmp.output.dir}/${mds.application}"/>
    
       <delete dir="${mds.deploy.dir}"/>
       <mkdir dir="${mds.deploy.dir}"/>

       <echo>create zip from file MDS store</echo>
      <zip destfile="${mds.deploy.dir}/${mds.application}_mds.jar" compress="false">
       <fileset dir="${mds.reposistory}" includes="${mds.application}/**"/>
     </zip>

       <echo>create zip with MDS jar</echo>
      <zip destfile="${mds.deploy.dir}/${mds.application}_mds.zip" compress="false">
       <fileset dir="${mds.deploy.dir}" includes="*.jar"/>
     </zip>

       <propertycopy name="deploy.serverURL"    from="${deployment.plan.environment}.serverURL"/>
       <propertycopy name="deploy.overwrite"    from="${deployment.plan.environment}.overwrite"/>
       <propertycopy name="deploy.user"         from="${deployment.plan.environment}.user"/>
       <propertycopy name="deploy.password"     from="${deployment.plan.environment}.password"/>
       <propertycopy name="deploy.forceDefault" from="${deployment.plan.environment}.forceDefault"/>

       <echo>deploy MDS app</echo>

       <echo>deploy on ${deploy.serverURL} with user ${deploy.user}</echo>
       <echo>deploy sarFile ${mds.deploy.dir}/${mds.application}_mds.zip</echo>

       <antcall target="deploy" inheritall="false">
            <param name="wl_home" value="${wl_home}"/>
            <param name="oracle.home" value="${oracle.home}"/>
            <param name="serverURL" value="${deploy.serverURL}"/>
            <param name="user" value="${deploy.user}"/>
            <param name="password" value="${deploy.password}"/>
            <param name="overwrite" value="${deploy.overwrite}"/>
            <param name="forceDefault" value="${deploy.forceDefault}"/>
            <param name="sarLocation" value="${mds.deploy.dir}/${mds.application}_mds.zip"/>
       </antcall> 
   </target>

   <target name="undeployMDSApplication">
       <echo>undeploy MDS application ${mds.application}</echo>

       <propertycopy name="deploy.serverURL"    from="${deployment.plan.environment}.serverURL"/>
       <propertycopy name="deploy.overwrite"    from="${deployment.plan.environment}.overwrite"/>
       <propertycopy name="deploy.user"         from="${deployment.plan.environment}.user"/>
       <propertycopy name="deploy.password"     from="${deployment.plan.environment}.password"/>
       <propertycopy name="deploy.forceDefault" from="${deployment.plan.environment}.forceDefault"/>

        <echo>undeploy MDS app folder apps/${mds.application} </echo>
        <antcall target="removeSharedData" inheritall="false">
             <param name="wl_home" value="${wl_home}"/>
             <param name="oracle.home" value="${oracle.home}"/>
             <param name="serverURL" value="${deploy.serverURL}"/>
             <param name="user" value="${deploy.user}"/>
             <param name="password" value="${deploy.password}"/>
             <param name="folderName" value="${mds.application}"/>
        </antcall>
   </target>

</project>

and at last the deployMDS.bat file

set ORACLE_HOME=C:\oracle\MiddlewareJdev11gR1PS1
set ANT_HOME=%ORACLE_HOME%\jdeveloper\ant
set PATH=%ANT_HOME%\bin;%PATH%
set JAVA_HOME=%ORACLE_HOME%\jdk160_14_R27.6.5-32

set CURRENT_FOLDER=%CD%

ant -f build.xml deployMDS -Dbasedir=%ORACLE_HOME%\jdeveloper\bin

The current survey on the OTN forum asked people whether they used the visual editor or code editor when developing their JSF. Well not surprisingly most people answered "Both". So if you are using the code editor while developing JSF... shay.shmeltzer http://blogs.oracle.com/shay