Soa Suite 10.1.3.3 – ESB, BPEL – Nice-to-knows, pitfalls

I’ve been checking out the different capabilities and new features of the Adapter-framework in ESB and BPEL for some weeks now and came across some nasty pitfalls, nice-to-knows, … which I would like to share with you.

Of course I would like to share thoughts, opinions and start discussions on these topics.

ESB:

  • How to define xsd-validation on file-adapter (validate payload at runtime-option isn’t available in file adapter) : In the ESB console, select the routing service which is invoked after the inbound file adapter. See the “Definition” tab. The validation option is in the “Operation Details” section. (with thanks to Ronald)

BPEL:

  • Inserting master-detail data using DB Adapter functionality : Referring to my experiences so far it’s best best way to make use of stored procedures instead of the toplink mappings file. I am mainly using the stored procedures because the tooling support in Jdeveloper (wizards, toplink ui), I still miss a good ui for the toplink support. Also it is easy to give the task to create an PL/SQL api to the PL/SQL developers that are working on a certain application. (with thanks to Orjan)

  • [Error ORABPEL-10007]: unresolved messageType for “{http://schemas.oracle.com/bpel/extension}RuntimeFaultMessage”: When you’ve defined an empty bpel process (which is best practice to do the brain-work) you will face this issue when defining fault-handling inside your bpel-process. To solve this error you need to import the RuntimeFault.wsdl inside the adapter you’re using. Following import statement needs to be added:

  • Best practices when invoking Bpel Processes from different UI’s (Flex, JSF, …) : Many thanks to Hajo for his explanation: It is best practice to use the default ways to invoke a BPEL process – create a WSDL that maps to WSIF binding in a controlled environment and to a SOAP/HTTP binding in a more B2B type of scenario. A call to the BPEL API would be a “custom” solution that needs way more governance to communicate with fellow developers and to maintain properly, when compared to the straightforward standard way. . For more details see OTN Thread: http://forums.oracle.com/forums/thread.jspa?messageID=2329384&#2329384

  • Use multiple sources in transform-activity: In 11g a new feature has been added to be able to use multiple sources using bpel 2.0 (bpel:doXslTransform(string, node-set, (string, object)*)). The workaround in 10.1.3.3 is by using the params-approach => http://blogs.oracle.com/rammenon/2007/05/07. Or by using an assign-activity with append-functionality to add the variable inside your source-target and in the same assign-activity add the process-xslt functionality to call your xsl to populate the source with the target-information.

Invoking Web Services from Database:

  • Call an esb service using the UTL_HTTP package => ORA-29266: end-of-body reached => make sure to pass variables using String-notation instead of Character-notation

Interesting New Features in 10.1.3.3 :

  • Controlling the Size of a Rejected Message (10133technotes.pdf):
    You can now control the size of a rejected message by specifying the following
    endpoint property for the inbound File/FTP adapter partner link.
    In this example, you reject 100 lines from the file since the actual file is too large.
    oracle.tip.adapter.file.debatching.rejection.quantum=”100”

  • ESB Endpoint Properties : e.g. ability to add RejectedMessageHandler to file adapter services

Enhancement Requests:

  • Ability to validate xml payload at runtime on Adapter-level instead of on domain level or routing service level
  • Ability to add xsi:nil attribute using xsl-functionality in transform-activity
  • File-adapter: Ability to skip columns besides skipping rows + ability to use special characters in column headers

Well that’s it for now … feel free to share thoughts, comments, etc.

Column Aliases in BI Publisher


Problem:

If you create a dataset and build your SQL-query by Query Builder or type it manually, you have to watch out that you do not use column aliases like ‘FEED’.

See example below:

SQL-query:

select BI_TABLE.FEED as FEED,
      BI_TABLE.FEEDBACK as FEEDBACK,
      BI_TABLE.APPLICATION_ID as APPLICATION_ID,
      BI_TABLE.NAME as TYPE_NAME,
      BI_TABLE.DESCRIPTION as TYPE_DESCRIPTION
from HR. BI_TABLE;

XML-file:

Internet Explorer seems to perform a strict XML validation. He gives you following error:


Solution:

Replace all column aliases with names like ‘FEED’ and your problem is solved. See solution below:

SQL-query:

select BI_TABLE.FEED as COLUMN_01,
      BI_TABLE.FEEDBACK as COLUMN_02,
      BI_TABLE.APPLICATION_ID as APPLICATION_ID,
      BI_TABLE.NAME as TYPE_NAME,
      BI_TABLE.DESCRIPTION as TYPE_DESCRIPTION
from HR. BI_TABLE;

XML-file:


Note: You can use ‘FEED’ as name for database tables but remember, not for column aliases!!!

Oracle BI Publisher Date-type

You can only format dates when they are in the canonical date format. This format looks like: YYYY-MM-DDTHH24:MI:SS.

You have two options:
1. adapt the XML generation process to generate the date in the canonical format and use the format date functions of BI Publisher in the template.

2. adapt the XML generation process to generate the date in the format of the client (example: DD-MM-YYYY) and print this directly on your report.


Solution 1:

First, we create a new dataset. We give it a name and select SQL Query as Type. Then choose a ‘Data Source’ and check ‘Cache Result’.

We build a query by Query Builder in BI Publisher or type it manually. See below to our ‘Data Set’ -query.

Note: XML-Date is in the canonical format and can be imported directly in the template. Example below is based on a ‘sysdate’ and stored in a field called ‘FULL_DATE’.

DS_Query:

select BI_DATE.FULL_DATE as FULL_DATE
from HRM.BI_DATE BI_DATE

After we create a dataset, we generate an XML to build our RTF-template.

XML-type:

&lt?xml version=”1.0″ encoding=”UTF-8″ ?&gt
&ltROWSET&gt
   &ltROW&gt
      &ltFULL_DATE&gt2008-02-19T13:24:37.000+01:00&lt/FULL_DATE&gt
   &lt/ROW&gt
&lt/ROWSET&gt

Properties:

Load XML into MS Word (with Oracle BI Publisher Desktop). Import the field ‘FULL_DATE’ and edit the properties. We can choose different format-types in the drop-down box. Choose one for example: ‘d-MMM-yy’ or type your own format-type like ‘dd-mm-yyyy’.

Note: Field ‘FULL_DATE’ is a canonical format.

Results in Word:

Solution 2:

First, we create a new dataset. We give it a name and select SQL Query as Type. Then choose a ‘Data Source’ and check ‘Cache Result’.

We build a query by Query Builder in BI Publisher or type it manually. See below to our ‘Data Set’ -query.

DS_Query:

select BI_DATE.FULL_DATE as FULL_DATE,
   to_char(BI_DATE.FULL_DATE) as CHAR_DATE,
   to_char(BI_DATE.FULL_DATE,’dd-mm-yyyy’) as FORMAT_DATE,
   to_char(BI_DATE.DAY_DATE,’DD’) as DAY_DATE,
   to_char(BI_DATE.MONTH_DATE,’MM’) as MONTH_DATE,
   to_char(BI_DATE.YEAR_DATE,’YYYY’) as YEAR_DATE
from HRM.BI_DATE BI_DATE

After we create a dataset, we generate an XML to build our RTF-template.

XML-type:

&lt?xml version=”1.0″ encoding=”UTF-8″ ?&gt
&ltROWSET&gt
   &ltROW&gt
      &ltFULL_DATE&gt2008-02-19T13:24:37.000+01:00&lt/FULL_DATE&gt
      &ltCHAR_DATE&gt19-FEB-08&lt/CHAR_DATE&gt
      &ltFORMAT_DATE&gt19-02-2008&lt/FORMAT_DATE&gt
      &ltDAY_DATE&gt19&lt/DAY_DATE&gt
      &ltMONTH_DATE&gt02&lt/MONTH_DATE&gt
      &ltYEAR_DATE&gt2008&lt/YEAR_DATE&gt
   &lt/ROW&gt
&lt/ROWSET&gt

Load XML into MS Word (with Oracle BI Publisher Desktop). Because we have set our date types in the query, we just can import the available fields without editing the properties.

Results in Word:

Solution 3:

XML-Date is not a canonical date format. Second solution for this problem is similar to solution 2. Except that we don’t edit the SQL-query and generate XML.

Load XML into MS Word (with Oracle BI Publisher Desktop). Import necessary fields and edit properties.

Example field ‘Full Date’:

Choose ‘Word Properties’:

Then ‘Add Help Text…’

Type following statement by ‘Help Key (F1)’: &lt?format-date: FULL_DATE;’dd-MMM-yyyy’;’Europe/Brussels’?&gt

Note: ‘Europe/Brussels’ is the time-zone of your region.

And click ‘OK’ -> ‘OK’.

Disadvantage: you have to apply this solution to every field on the report. If you do it on query-level, you can import fields without editing them in the template.

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

Configure Proxy Authentication for Web Services

The previous week a customer had problems during my bpel course training of 3 days where we weren’t able to design and deploy external web services due to proxy authentication problems.

We were able to create a web service proxy client inside Jdeveloper 10.1.3.3 but when deploying the web service we were always getting the HTTP Proxy Exception 407, meaning problems with username and password authentication.
This was the first time I ran into this issue, and it’s quite cumbersome to get around this one.

Steps to create a web service client for an external web service using proxy authentication:
1. Create a new application and new project
2. Choose to create a new Web Services, from the Business Tier Categorie and choose to create a Java Web Service from WSDL

3. Click ‘OK’ 4. Click ‘Next’, if you don’t want to see this page in the future, check the checkbox ‘skip this page next time’

5. Copy/Paste the following URL inside the WSDL URL, the first field http://www.oorsprong.org/websamples.countryinfo/CountryInfoService.wso?WSDL


6. Now press enter. The wsdl document will be fetched and read, and the next step in the wizard will be shown. Define a package name and a root package name for the types that will be generated for this webservice.

7. Go through the next steps and at the last screen you can see which operations are being provided in the web service and which classes will be generated, using the package names you’ve defined earlier.

The different classes are now being generated and you’re ready to deploy this web service to your application server 10.1.3.3 installation.


You can already deploy and run this web-service on your application server by performing the following steps:

1. Create a new Application Server Connection in the ‘Connections’-tab

2. Go back to the Applications Navigator-tab and choose to deploy your web service to this connection:

3. The next screen shown, is the deployment plan-screen where you can make adjustments before deploying the application:


4. In the deployment log window you can follow the different steps which are performed to deploy the web service client on our application server instance:

In the deployment log window you can follow the different steps which are performed to deploy the web service client on our application server instance

After succesfull deployment you can test your web service using Enterprise Manager:

1. Go to WebServices-tab in your Enterprise Manager console:

2.Click on the ‘CountryInfoServiceSoap’, the service we’ve deployed earlier:

3.Click the ‘Test Service’ link to test your service:

4.Click ‘invoke’ in the test window

As you can see the following exception is thrown ‘unexpected null value for literal data’

As you can see the following exception is thrown ‘unexpected null value for literal data’, this is due to proxy authentication failure. OK, this isn’t very clear in the exception message, but let’s configure proxy authentication.

To configure proxy authentication we need to go back to our jdeveloper environment and configure authentication for our web service.

1. Right-click on the web-service proxy client in your ‘Application Navigator’ and choose ‘properties’

2. Go to the Security-categorie in the tree structure and enable global security settings, by checking the checkbox. Only choose the ‘text password’ as authentication options:

3. Authentication: Define the authentication settings, as in the screen below (nonce and creation time aren’t required)


Click OK and deploy the web-service again.

When testing the web service you just need to fill in the ws-security headers, and no exceptions will be thrown anymore:

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.

SYSMAN Account is Locked

This morning I opened Enterprise Manager and instead of the regular “login”-screen, I found following screen:

 

Everything is up and running but Enterprise Manager is not able to connect to the database instance. Sounds like a connection problem, not?

These are the steps I followed to solve this issue and to reset the “sysman”-account:

STEP1: Check EM log file:

[oracle@myserver log]$ pwd


/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD
/sysman/log

 [oracle@ myserver log]$ tail -50 emoms.log

2008-02-11 23:12:04,968 [ApplicationServerThread-11]
ERROR app.SessionObjectManager sessionDestroyed.128 -
java.sql.SQLException: ORA-28000: the account is locked

java.sql.SQLException: ORA-28000: the account is locked

STEP 2: Check database users:

SYSTEM> select username, account_status from dba_users;
USERNAME ACCOUNT_STATUS
------------------------------ -------------
MGMT_VIEW OPEN
SYS OPEN
SYSTEM OPEN
DBSNMP OPEN
SYSMAN LOCKED(TIMED)

 

STEP 3: Stop the Database Console

[oracle@myserver dbascripts]$ emctl stop dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.1.0.6.0

Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.

https://myserver.mydomain:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ...

 ...  Stopped.

[oracle@myserver dbascripts]$

STEP 4: Reset the SYSMAN-account


[oracle@myserver log]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Feb 12 09:51:59 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS> alter user sysman identified by <new_password> account unlock;
User altered.

STEP 5: Reset the SYSMAN-account in the “targets.xml”-file ($ORACLE_HOME/myserver.mydomain _mySID/sysman/emd) and modify the “Username”-Property and the “Password”-property as follows:

[oracle@myserver emd]$ pwd
/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD/sysman/emd
[oracle@myserver emd]$ cp targets.xml targets.xml.ori
[oracle@myserver emd]$ vi targets.xml

<Property NAME="UserName" VALUE="SYSMAN" ENCRYPTED="FALSE"/>
<Property NAME="password" VALUE="<new_password>" ENCRYPTED="FALSE"/>

STEP 6: Reset the SYSMAN-account in the “emoms.properties”-file ($ORACLE_HOME/myserver.my_domain_mySID/sysman.config) and modify the “oracle.sysman.eml.mntr.emdRepPwd”-parameter and the “oracle.sysman.eml.mntr.emdRepPwdEncrypted” in the following way:

[oracle@myserver config]$ pwd
/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD/sysman/config
[oracle@myserver config]$ cp emoms.properties emoms.properties.ori
[oracle@myserver config]$ vi emoms.properties

oracle.sysman.eml.mntr.emdRepPwd=<new_password>

oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE

STEP 7: Start the Database Console

[oracle@myserver dbascripts]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.6.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
https://myserver.mydomain:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ...... started.
------------------------------------------------------------------
Logs are generated in directory
/opt/oracle/product/11.1.0/db/myserver.mydomain_DWHD/sysman/log

 

STEP 8: Test