ODTUG 2009 – Jam Session

During the ODTUG conference a lot of interesting presentations and session were given regarding different aspects, such as:
  • Forms Modernization (Forms 2.0, Forms 11g, …)
  • Apex (Case studies, Apex & reporting, Apex for mobile applications, …)
  • ADF 11g (Fusion development using ADF 11g and rich client components, Package based development using ADF BC, …)
  • Integration (web services, ESB, BPEL, usage of a canonical datamodel, best practices when talking about security, development, …)
  • Finance (Hyperion, Essbase, …)
  • Data Integration (OWB, ODI, …)

Besides these sessions we also had the time to have our own little jam session …

In other words, the conference was a great learning experience and we had a lot of fun as well ;o)

Oracle 4 Newbies: thé Powerday

After our ‘Oracle for Newbies’ seminar, we invited all attendees for a Powerday. The participants would get the opportunity to try out everything we had shown in the seminar. Last Monday we started our Powerday with 11 curious attendees.

The ‘half a day’ – Powerday consisted of 3 parts: 1) the installation of Oracle Express Edition and an upgrade to the last Oracle Application Express version, 2) building a wizard driven application and 3) building and modifying manually some apex functionality.

For the installation part, we asked every attendee to bring his own portable. We planned to have the software successfully installed on every pc in one hour. And yes, they all did it. After 1 hour and 10 minutes, everybody had an Oracle database with Apex 3.1 up and running!

For the second part, we gave a demo on how to create a wizard-based application. Now it was up to them! We provided some excel sheets with the data. Starting from these excel sheets, thanks to the ease of Oracle Application Express, they managed to create the Oracle data model, fill the tables with the excel data and create a web application to do all possible data manipulation. And again… One hour later, they all had their first own Apex application up and running.

In the last part of our Powerday, we did some more advanced and ‘not so wizard-driven’ stuff like modifying or adding some functionality in the application, implementing blob functionality (upload and download of images), implementing security (authorization) and lots of other nice things. Not only ‘we’ did all this, at the end of the day, they all had these features implemented in their application.

All participants were enthusiast, and hopefully we convinced them to start using Oracle and Apex.

The developers’ voice

As you all (should) know, begin this month, there was the event of the year for Java developers, Devoxx. This year they changed their name from JavaPolis to Devoxx to strengthen the voice for Java developers. But I don’t think that the name changed any of its popularity. Instead, they had more attendees compared to last year.

As a rookie, I had the privilege to go to Devoxx and explore the Java world, looking at the new and shiny technologies that will conquer the world! The first university day, I was very interested in the web development sessions I had the chance to attend sessions about JPA and Flex. Each session lasted for 3 hours (although some people didn’t wait that long :) ). The day ended with a very interested JSF on Rails session, presented by Lucas Jellema.

On the second university day, I had the chance to look at Seam in action. The author of the book (with the same title), Dan Allen, presented in a very detailed way the possibilities of Seam. Sometimes he went too far in details that some of my fellow attendees began to dream of Seam (with their eyes closed). After an energetic lunch, I went to the Filthy Rich Clients session by Romain Guy and Chet Haase.

It was a very interesting session given in a very entertaining way. Both speakers really knew how to catch the audience’s attention. They explained us how we can improve the look and feel of our applications so that both user and developer can really enjoy themselves when using/building an application.

After the performance of a beatboxer that surely woke us up, the third and most astonishing day began! First, Stephan Janssen (the man behind Devoxx), gave us a brief summary of the previous days and passed the microphone to the Java FX team. This team presented a state of the art new technology and showed it to the crowd in an amazing demo! They made a video puzzle where the player had to put the pieces back in the right order, with the video still playing! Magnificent!

As the people of this beautiful presentation of JavaFx ended,the people of IBM were the next ones in line to tell us more about these strange portals at every entry. It was an RFID-project so that the Devoxx team was able to register statistics throughout the entire week. So next year, the speakers with the least amount of attendees will be removed from the list :)

At the end of a 10-minute break, it was our turn again. I went to a session about Software estimation presented by a passionate Italian, Giovanni Asproni. He was really able to keep everyone’s attention with his live examples and his enthusiasm. After a hacking session (by Sebastian Deleersnyder of OWASP) and the feel of Scala there was Oracle’s Frank Nimphius who presented the latest features of JDeveloper 11g in combination with Oracle’s ADF.

These three days were to most amazing days for me as a rookie Java developer. I could never imagine that there were so many different products on the market that are all Java based. A special thanks to the whole Devoxx team! If you have any interest in Java development, I hope to see you next year @ Devoxx!

Bjorn Ongenae
iAdvise consultant

Apex Unreachable after Database Upgrade from 9.2 to 10.2

Recently we were performing a database upgrade from release 9.2.0.7 to 10.2.0.4. After the successful upgrade we were issuing a problem to reach APEX. We’ve tried to browse to the APEX URL ( :/pls/apex”>http://:/pls/apex ) and the only thing we were receiving was a blank page. The strange thing is that the APEX URL has been rewriten to :/pls/apex/apex”>http://:/pls/apex/apex
There were no errors in the error.log of the Apache and only HTTP 200 entries were written to the access.log of the Apache.

When trying to reach an existing APEX application ::/pls/apex/f?p=120″>http://::/pls/apex/f?p=120 it wasn’t possible to reach the application but the error.log did write some error entries in the error.log of the Apache.

[Wed Sep 17 06:40:19 2008] [error] [client 130.130.203.210] [ecid: 1221626419:193.53.194.211:5604:7448:145,0] mod_plsql: /pls/apex/f HTTP-404 ORA-06550: line 1, column 35:\nPLS-00201: identifier ‘SYS.DBMS_CRYPTO’ must be declared\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\nORA-06512: at “FLOWS_030100.F”, line 279\nORA-06512: at line 31\n

The problem we were facing after the upgrade was a missing grant on the SYS.DBMS_CRYPTO to FLOWS_030100. DBMS_CRYPTO came into the picture from 10G. In previous releases such as 9i the DBMS_OBFUSCATION package is used. The grants to those packages were giving at the APEX install. Upgrading the database after APEX install needs a manually grant of this DBMS_CRYPTO package.

Maarten Fuchs

How to find the Oracle Application Server version number

I remember that I was searching a few years ago for a way to find the exact version of the Oracle Application Server, but I didn’t found it.

Today I had to search for this again and now I found a file that contains the exact version and even will be updated when the iAS is patched.

The file to look for is ias.properties, in this file you have to look for the line that begins with Version, next to this you will find the correct number.

You can find this file in iAS-home\config

ESB error [TOPLINK-3001]

This is an error where I have been working on for too long.
And in fact it has a very easy solution.
I was building my application that I already mentioned in a previous post.
When I tested the xsl file(in the xsl file right click and choose test), everything looked fine and no problem occurred.
But when I deployed the application into my application server and I wanted to upload the content of the csv file into the database, I always received an error.

For the solution read more…

This was the error I received:
nested exception is: ORABPEL-11616 DBWriteInteractionSpec Execute Failed Exception. insert failed. Descriptor name: [DBTank.PersonFuelCosts]. Caused by Uitzondering [TOPLINK-3001] (Oracle TopLink – 10g Release 3 (10.1.3.3.0) (Build 070608)): oracle.toplink.exceptions.ConversionException Omschrijving uitzondering: Het object [50,1], van klasse [class java.lang.String], kon niet worden geconverteerd naar [class java.math.BigDecimal]. Interne uitzondering: java.lang.NumberFormatException. at oracle.tip.adapter.fw.wsif.jca.WSIFOperation_JCA.executeRequestResponseOperation

Reading this error message I believed that there was something wrong with the translation from my csv content into the database, and that this was caused by a declaration in one of the xsd files. The type of the variable in the xsd file in the csv file was a ‘String’, but for the xsd file for the database adapter the type was ‘decimal’.
I replaced the types in the xsd files with different kinds of other types, but this didn’t changed a thing, I still received the error message.
After a lot of searching and testing I found out that, when I changed the value in the csv from 56,2 into 56.1 everything worked fine.
Strange thing is that my DB reads ‘,’ and not ‘.’ .
Ones I found this, the workaround was rather easy to find.
On my xsl file I used a function to replace the ‘,’ by a ‘.’
This function can be found in the ‘Component Palette’ where you have to choose ‘String Functions’ and select the ‘translate’ function.
Ones this was done I managed to get the data correct into my database.

Flex – Rules of Thumb

When you’re building JEE Applications, you always need to apply to a given set of rules, best practices.

When you need to take a decision in which technology to use to build your new application with, you need to be able to deliver each rule of tumb with this technology.

Rules of thumb that need to tackled:
- Isolation
- Simplicity of UI and Model
- Clear separation of concerns
- Responsive & intuitive User Interface

Delivering Rules of Thumb using FDMS

The rules of thumb mentioned for Adobe Flex Integration, can be delivered using Flex Data Management Services.

1. Isolation :

Isolate handling of state, presentation, action and communication

State:

  • Assembler Class holds all necessary information regarding state-handling
  • Pull-mechanism of Data Services assures all clients have up-to-date information

Presentation:

  • Java DTO (Data Transfer Objects) are automatically mapped to Action Script DTO’s

Action:

  • Actions are immediately called on the Assembler Class that holds all needed business logic and CRUD-methods

Communication:

  • Communication between client and server are handled on server-side using the Assembler Class

2. Simplicity of UI and Data Model:

Keep UI and data model code as simple as possible

  • The User Interface only defines the lay-out of the different objects and data management is performed by the Data Services
  • Data Model is provided using Web Service Stubs created on the deployed Bpel Processes and Data Transfer Objects

We still need to manually create an Action Script DTO object for each POJO we’re using in our Web Services.

3. Clear separation of concerns:

Decouple and make programming tasks parallel so they can be carried out by different people

  • Assembler Class will be defined for each designated web service stub
  • Data Transfer Objects will be defined in ActionScript for each used object-definition in the Assembler-class

4. Responsive & intuitive User Interface:

Provide a very responsive user experience with clear feedback and zero latency

  • Provided using Data Services Management using push-mechanism
  • Use managed DTO-components so each event is known to the Data Services
  • Use validation-framework for client-side validation
  • Use conflict resolution API for exception handling

Connect from RHEL5 to a MSSQLServer Database using ODBC

For a data warehouse project I’m working on, I like to add a MS SQLServer database as a datasource to OWB. So the first thing I need is an ODBC Datasource. No problem, but there just one small issue which makes the creation of this ODBC datasource a bit less “default”: My database is running on a linux machine. J
So, A few more steps are needed:

  • Check the unixodbc packages are installed
  • Install the KDE driver manager components for ODBC
  • Install and configure FreeTDS
  • Add the FreeTDS Driver to the ODDBC Configuration Files
  • Add the MSSQLServer Datasource


STEP 1: Check the unixodbc-packages are installed

[oracle@lnx-srv-dwh-dev ~]$ odbcinst -j
unixODBC 2.2.11
DRIVERS…………: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /home/oracle/.odbc.ini
[oracle@lnx-srv-dwh-dev ~]$

If the unixODBC packages aren’t installed, you can install them using yum or using the “rpms” located at the RHEL5 installation disks.

STEP 2: Install the KDE driver manager component for ODBC

This package will install the graphical “ODBCConfig” tool which we can use to add ODBC drivers and Data Sources. You can compare this tool with the “ODBC Data Source Administrator” in MS Windows.

[root@lnx-srv-dwh-dev rpm]# yum install unixODBC-kde-2.2.11-7.1.x86_64
Loading “installonlyn” plugin
Loading “rhnplugin” plugin
Setting up Install Process
Setting up repositories
rhel-x86_64-server-vt-5 100% |=========================| 1.4 kB 00:00
rhn-tools-rhel-x86_64-ser 100% |=========================| 1.2 kB 00:00
rhel-x86_64-server-5 100% |=========================| 1.4 kB 00:00
Reading repository metadata in from local files
primary.xml.gz 100% |=========================| 1.3 MB 00:02
################################################## 4235/4235
Parsing package install arguments
Resolving Dependencies
–> Populating transaction set with selected packages. Please wait.
—> Downloading header for unixODBC-kde to pack into transaction set.
unixODBC-kde-2.2.11-7.1.x 100% |=========================| 12 kB 00:00
—> Package unixODBC-kde.x86_64 0:2.2.11-7.1 set to be updated
–> Running transaction check

Dependencies Resolved

=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
unixODBC-kde x86_64 2.2.11-7.1 rhel-x86_64-server-5 594 k

Transaction Summary
=============================================================================
Install 1 Package(s)
Update 0 Package(s)
Remove 0 Package(s)

Total download size: 594 k
Is this ok [y/N]: y
Downloading Packages:
(1/1): unixODBC-kde-2.2.1 100% |=========================| 594 kB 00:02
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: unixODBC-kde ######################### [1/1]

Installed: unixODBC-kde.x86_64 0:2.2.11-7.1
Complete!
[root@lnx-srv-dwh-dev rpm]#

STEP 3: Install and configure FreeTDS

FreeTDS is a set of libraries that allows your programs to natively talk to Microsoft SQL Server and Sybase databases. Those libraries will used to create an ODBC driver used to connect to our SQLServer database. You can download the software (freetds-stable.tgz) from ftp://ibiblio.unc.edu/pub/Linux/ALPHA/freetds/stable/
Following log shows the install and configuration steps:

[oracle@lnx-srv-dwh-dev freetds]$ pwd
/tmp/freetds
[oracle@lnx-srv-dwh-dev freetds]$ ls
freetds-stable.tgz
[oracle@lnx-srv-dwh-dev freetds]$tar zxvf freetds-stable.tgz

[oracle@lnx-srv-dwh-dev freetds]$ cd freetds-0.64/
[oracle@lnx-srv-dwh-dev freetds-0.64]$
[oracle@lnx-srv-dwh-dev freetds-0.64]$ ./configure –with-tdsver=7.0 –prefix=/usr/local/freetds

config.status: creating include/config.h
config.status: executing depfiles commands
[oracle@lnx-srv-dwh-dev freetds-0.64]$ make

make[1]: Entering directory `/tmp/freetds/freetds-0.64′
make[1]: Nothing to be done for `all-am’.
make[1]: Leaving directory `/tmp/freetds/freetds-0.64′
if test ! -f PWD; then cp ./PWD.in PWD; fi
[oracle@lnx-srv-dwh-dev freetds-0.64]$ su
Password:
[root@lnx-srv-dwh-dev freetds-0.64]# make install

make[2]: Nothing to be done for `install-exec-am’.
/bin/sh ./mkinstalldirs /usr/local/freetds/etc
if test ! -f /usr/local/freetds/etc/freetds.conf; then \
/usr/bin/install -c -m 644 ./freetds.conf /usr/local/freetds/etc/freetds.conf; \
fi
if test ! -f /usr/local/freetds/etc/locales.conf; then \
/usr/bin/install -c -m 644 ./locales.conf /usr/local/freetds/etc/locales.conf; \
fi
make[2]: Leaving directory `/tmp/freetds/freetds-0.64′
make[1]: Leaving directory `/tmp/freetds/freetds-0.64′

STEP 4: add the FreeTDS driver to the ODBC Drivers-file

Edit the “/ect/odbcinst.ini” file and add following lines:
[FreeTDS]
Description = v0.64 with protocol v8.0
Driver = /usr/local/freetds/lib/libtdsodbc.so
Setup = /usr/local/freetds/lib/libtdsodbc.so
FileUsage = 1

[root@lnx-srv-dwh-dev etc]# pwd
/etc
[root@lnx-srv-dwh-dev etc]# cp odbcinst.ini odbcinst.ini.ori
[root@lnx-srv-dwh-dev etc]# vi odbcinst.ini
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1

[FreeTDS]
Description = v0.64 with protocol v8.0
Driver = /usr/local/freetds/lib/libtdsodbc.so
Setup = /usr/local/freetds/lib/libtdsodbc.so
FileUsage = 1

As an alternative you can add the driver using the “ODBCConfig”-tool;

STEP 5: Add an MSSQLServer Datasource:

Start de” ODBCConfig” Administration Tool
Click the “System DSN”-add
Click the “Add”-button
Select the “FreeTDS”-driver
Click “OK”

Fill in the datasource properties

As an alternative, you can add the datasources to the “/etc/odbc.ini”-file

[oracle@lnx-srv-dwh-dev etc]$ pwd
/etc
[oracle@lnx-srv-dwh-dev etc]$ cat odbc.ini
[mySQLTest]
Description = my SQLServer Test Database
Driver = FreeTDS
Servername = mySQLTestServer
Server =
Address =
Port = 1433
Database =
TDS_Version = 8.0
Language = us_english
TextSize =
Domain =
PacketSize =

STEP 6: Test the brand new datasource!

Use the “isql”-tool to connect to our database.

[oracle@lnx-srv-dwh-dev etc]$ isql -v mySQLTest myusername mypassword
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+
SQL>

STEP 7: What’s next

From now on we can use this ODBC datasource in an Oracle Heterogeneous Service which we need when adding the SQL Server as a Source in Oracle Warehouse Builder.