Weblog for the AMIS Technology corner
Archive for February, 2007
First patch for Oracle E-Business Suite R12
Feb 27th
This morning I downloaded my first patch for the new Oracle EBS R12. Already there are 2 high priority patches for the new EBS, and I downloaded one of them (patch p5856453_R12[1].AD.A_R12_LINUX.zip). I could not help feeling a little bit excited, because patching is changed in EBS R12.
The manual steps to be taken before patching are removed from the readme and placed into an xml file. Before patching you are supposed to run a new perl script called $AD_TOP/bin/admsi.pl
This script creates an install_{patch_number}.html. After performing the steps in the html file, you have to run $AD_TOP/bin/admsi.pl –done (to let the system know you have performed the manual steps….). The admsi.pl also creates a custom readme which you have to read before patching.
I was a little bit disappointed, because the patch I downloaded is maybe to small. Nothing about admsi.pl in the readme…Tried to run admsi.pl anyway
Going to SF, to meet SF
Feb 27th
At 04:30 this morning my alarm went off. I don’t like mornings, but today is different from other days. Today I flew into San Francisco for the Oracle PL/SQL Programming Conference. After struggling over the armrest for nearly 11 hours I finally made it.
The conference starts on wednesday, that gives me a day to adjust to the time and language.
Looking at the Agenda, I think I will attend these sessions:
SOA Suite Build, Deployment and Test Automation – part 1
Feb 23rd
In the latest SOA Suite release Oracle has improved the automated build capabilities. Instead of the Oracle BPEL specific Ant tool (obant, a customized version of Ant) plain vanilla Ant is now used for building and deploying BPEL suitcases. Let’s put it to the test: subject of this blog post is the set up of an automated build and deployment environment for SOA Suite. The goal is to automatically build Oracle’s SOA Order Booking demo application and deploy it to an Oracle Application Server that runs the SOA Suite. Oracle’s demo application is selected because it uses all components in the SOA Suite: the brand new ESB, BPEL and Business Rules. The demo application also includes a number of Web Services that are implemented in Java.
This is the first in a small series of posts. Focus of part 1 is on describing the nuts and bolts of the automated build environment and subsequently on building the Web Services and BPEL components in the SOA demo application. Goal of part 2 is to automatically build and deploy the ESB components. Finally, in part 3, I will focus on the new unit test capabilities of Oracle BPEL processes. 
Read the rest of this entry »
Running Webforms without Oracle JInitiator
Feb 21st
A few weeks ago one of my customers told me that he lost a sales opportunity because the customer who he was trying to sell his software too had problems convincing his system administrator to install JInitiator. And as we all know it’s often quite a challenge to convince a system admin to install something he does not know and doesn’t want to know. In the real world it is much easier to convince them that the SUN JPI is needed to be installed for your application. In my opinion a loss which was not needed since you can run Webforms without JInitiator using the SUN JPI. How did I know? Well I had read the blog of Wilfred van der Deijl. He has a good blog site called OraTransplant in which he had posted an article on how to use dynamic versioning of Sun JPI and Oracle Forms.
My goal was to use the static version of the Sun JPI version 1.5.0.11 My customer sells validated software which complies with FDA regulations therefore I did not think it would be a good idea to use dynamic versioning since JPI 1.6 is not supported yet by Oracle.
So what to do: 
Read the rest of this entry »
The Java Server Faces generic “garble button” – how to become wildly popular again…
Feb 20th
Somewhat akin to the dbms_advanced_rewrite package application I discussed some time ago, I have now another piece of functionality that will make you once again the light of the party. Introducing: the JSF Garble Button! This is a very simple CommandButton that you can inject into any page in any application. It only requires the configuration of a single managed bean. This powerful button – when pressed – will randomly reorganize the container it is part of – as well as all of its siblings’ child-components and their children etc. The value of this piece of functionality is absolutely nothing – beyond a pretty good joke if you dress up this button as a Help or Send Me Money button. Of course instead of a button you can use a commandlink.
A simple demonstration of the utter uselessness of what this article discusses: initially our grantedly already horrible page (for want of a proper stylesheet) looks like this:
After pressing the Garble button, the page is refreshed as:
Testing, One, Two….
Feb 16th
Just in time for the Oracle PL/SQL Programming Conference, OPP2007, the Code Tester for Oracle is released. I am very excited about this tool and more posts will follow.
Removing double records (and keeping one of them)
Feb 15th
Assume you have a table with contaminated data (in this example: non-unique values on col1 and col2), for instance because no unique key was implemented.
col1 col2 value
2 9 Smith 2 4 Jones 2 9 Andersen 8 6 Nicholson 8 6 Parker
If you want to prevent this from happening in the future, you can of course create a unique key or check constraint:
SQL> ALTER table TAB1 add (constraint tab1_uk UNIQUE ( col1, col2 ) deferrable enable novalidate); Table altered. SQL> INSERT INTO tab1 VALUES ( 2, 4, 'Ray' );ERROR at line 1:ORA-00001: unique constraint (TAB1.TAB1_UK) violated
This in fact works fine, every new record wil be validated and existing records which fail the rule will be ignored during creation of the constraint. However, on update the constraint will be enforced.
But what if you want to clean up the doubles? The statement which I find the most easy and reusable is this one:
DELETE from TAB1 where rowid NOT IN ( select min(rowid) from TAB1 group by col1, col2 );
As you noticed, I use the rowid because this is the only column which distinguishes two records with the same key. It’ll always keep one unique record, even if there was only one record to begin with. It also takes care of the situation where you have more than 2 doubles, as opposed to this solution:
DELETE from TAB1 where id IN (select (max(id) from TAB1 group by col1, col2 HAVING COUNT(‘x’) > 1 ) –> too much work!
Installing Oracle E-Business Suite R12
Feb 13th
So, I finally installed the new EBS 12i release. I had to install with an empty database, because of disk space limits…
But the important thing is that the installation was successful.
Some first impressions…
- the look and feel changed (we had already seen some previews..)
- some strange options when logging in the application ??
- the database is twice as big (10g vs 9i)
- new technology stack
- new instance top
I also faced a first problem. I could not startup the application in my Microsoft explorer..The java console showed the login was hanging on the appslogo_new.gif ??
I found out that when I deactivated my Oracle Dba Toolbar in the explorer, I could enter the application without any problem. But when activating the toolbar again..the login process was hanging again on the same gif. Strange…the toolbar was downloaded from OTN and is something Oracle brought to my attention… I wonder if this is a known issue….
I also saw the dbc file is now in the instance top directory. Strange, because the path to the dbc file seems the same, /appl/fnd/12.0.0/secure….but this is not in the APPL_TOP, but in the INSTANCE_TOP (inst/apps/PROD_vamisux34).
A simple ls command in the INSTANCE_TOP shows..
[oracle@vamisux34 PROD_vamisux34]$ ls
admin appltmp conf_PROD.txt logs ora pids rgf
appl certs conf_PROD.txt.bac ocm out portal temp
The admin directory contains the scripts directory, where all the start/stop scripts are located.The appl directory shows us
[oracle@vamisux34 appl]$ ls
admin fnd
where fnd holds the dbc file and the admin directory contains the xml file.
More to come….
Writing a pure SQL based String Tokenizer
Feb 8th
A quite common concept in many programming langiages, though perhaps strongest in Java, is the String Tokenizer. This is a utility class or function that takes a String as input, and optionally one or more delimiters, and breaks up the string in individual elements, returning those as an appropriate collection. As an example: a String Tokenizer could split the string "red,blue,yellow" into a collection of the strings red, blue and yellow – if the comma had been specified as delimiter or no delimiter had been defined and the comma was the default. With the "e" as delimiter, the String Tokenizer would have produced: "r", "d,blu",",y" and "llow".
PL/SQL has something akin to the String Tokenizer, in the DBMS_UTILITY.COMMA_TO_TABLE function. By the way, my colleague Alex discussed this function in quite a bit of detail in his recent article Never Assume, Always Test.
While preparing for our session "SQL Puzzles" (next Tuesday, 13th February) I needed a String Tokenizer in SQL, ideally nothing but SQL. Again, with a different objective in mind, I wrote a SQL Query that indeed acts as a Tokenizer. I am quite sure it can be done much better – Anton: please share with me your thoughts on this – but I will share my attempt with you anyway. Read the rest of this entry »
Creating XML Data Services for Reporting and ESB integration using DBMS_EPG (the internal HTTP/PLSQL Gateway) and XMLType
Feb 7th
Today I all of a sudden found myself investigating the creation op PL/SQL based Data Services, returning XML documents with data from SQL queries to HTTP Clients. The direct motivation behind this investigation was my initial introduction to Oracle BI EE, the use of HTTP Data Sources and my brainstorming on SOA in general and ESB Services in particular. Although the BI EE can directly access Oracle databases, executing its own SQL queries, it feels much better architecturally speaking to have separate, decoupled services query the data and publishing them as service, for example through Http. In this article I will create such a data service, with minimal effort: leveraging SQL XML and DBMS_EPG, the Oracle 10gR2 embedded PL/SQL Gateway, part of XML DB. Read the rest of this entry »
