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.

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.

Using AJAX within Oracle Application Express (part 2)

Most of the AJAX calls will require some parameters. Instead of using an application item for every parameter, I wrote some javascript and PL/SQL functions that will store all AJAX parameters as XML in 1 application item!

The same javascript as in my first blog, but some extra parameters and functions are added.

This javascript function allows you to add an extra parameter to the AJAX call

This PL/SQL function allows you to read out the same parameter in the AJAX call’s PL/SQL code

We can simply add some parameters to our AJAX call. The first parameter of the AddParam function, is the name of the parameter (you must use the same name in your AJAX call’s PL/SQL code to read the parameter’s value), the second parameter is the parameter’s value.

In the PL/SQL code of the AJAX call, you can use the GetParameter function to read out these parameters

The result would be…

Data Driven Application using Adobe Flex – Part 3

Today I’m having a closer look at the Data Features provided by Adobe Flex using the Adobe Developer Guide and the forum for more information.

In the Flex Developer Guide, CHAPTER 37 Representing Data, an explanation is given about the different possibilities you have to define a data driven application.

In the first example you can check out how to define a model with some data fields and show these fields in your application. Note ! You need to enclose the inputfields in an mx:form-container to be able to view the fields or set the lay-out different from absolute because this renders the fields inapproprialty as well.

In the next example you can check out the validation-framework in Flex SDK where you can add validation to specific fields. Flex includes a set of validators for common types of user input data, such as ZIP codes, phone numbers, and credit card numbers.

If you have a closer look at the 2nd example you will notice the ZIPCodeValidator is used which is one of the default validators included in Flex. When you run the application a red box is shown when a validation exception occurs. Note : When you have a look at chapter 40 you will notice that the first example on page 1282, the PhoneValidator, doesn’t work properly. You need to add the listener- and trigger-attribute into the PhoneValidator to let the code work.

A view on the validation exception and message box is shown in the following screenshot:

As you can see it’s fairly easy to create a form-based application with validation defined on the input fields. If you want to have more information regarding the functionality that’s behind these validators you can check out the class-files by going to the source-tab of your application and clicking ‘go to definition’ on one of the validators.

If you want to change the error-message you can override the resource-bundle which is used in Adobe and you can even override the style of the message. E.g. if you want the message to appear in your favorite color, or house-style you can provide the following int ‘mx:style’-tag:

.errorTip
{ borderColor: #0000FF}

In the previous examples we’ve used curly braces to bind our data, you can also use the mx:Binding-tag, see page 1253 for an example.

Now that we’ve seen the different functionalities to create data bound applications, let’s have a look at the different Flex Data Management Features.

What if we want to store data and synchronize data in client-server applications or in MVC-applications? The following screen can give us a clearer view on the different features we can use:

As you can see there are 3 different components we can use to store and retrieve managed data, which are HTTPService, WebService and RemoteObject.

And I need to look into an example using RemoteObjects, more on this next time!

Oracle Fusion Middleware Regional Director

It’s official now: Orqcle Fusion Middleware Regional Directors

I’m very pleased to be able to participate with the Regional Director initiative and to get to know everyone that’s working with Fusion Middleware.
My aim is to get every consultant as enthousiastic as myself about Fusion Middleware to be able to share knowledge and experience with my team.

I can proudly present to you … The One and Only Oracle Fusion Regional Director for Belgium

I can proudly present to you … the finest … the greatest … the smartest … Oracle Fusion MiddleWare Regional Director for Belgium !

To be honnest I can’t be modest because I’m in seventh heaven wright now ;o)

Secretly – without informing my parents, my firm, my husband … because it’s so important to me – I’ve send a nomination form to Oracle regarding becoming an ‘Oracle Fusion Regional Director’.

After waiting for an enormous long time – 3 weeks, but that feels like ages when you’re awaiting acceptance – I’ve got the big news:

I am pleased to inform you that we have reviewed and accept your nomination for Oracle Fusion Middleware Regional Director.

What I felt when I received this e-mail was … ‘If you’re happy and you know it clap your hands …’ I’m clapping away, clap clap clapperdiclap … in other words an appreciation and acknowledgement of the commitment of the non-Oracle consultants which spend a lot of time promoting the Oracle Products and Services and helping others on OTN, such as myself.

As we would say in Belgium ‘Ik ben in de 7de hemel’ / ‘I’m in seventh heaven’ !!!

Dba: Capturing a time frame from the ALERT.LOG

A thing I did not know about my database….

The alert.log file chronologically records messages and errors about database operations such as startups and shutdowns, internal errors (ora-00600), links to trace files and much more.
This often makes the alert.log-file, the primary file that needs to be consulted in order to identify and solve issues concerning your database.

Unfortunately there’s no default “auto-cleanup” of this file and so the file can grow to an unmanageable size (e.g. impossible to edit using VI) and that makes it hard to search for message within a certain time frame.
By using Enterprise Manager, it is very easy to browse through this alert.log file and to capture/select only those message you’re interested in by filtering on a start and end date.

An example:

STEP 1: Connect to the EM Database Console:

STEP 2: Click the “Alert Log Content” hyperlink (on the Home-tab)

. . .

STEP 3: Fill in search criteria and push “Go”-button

STEP 4: Result

Reset Oracle Single Sign-On password

As of Oracle Portal Release 10.1.4, there’s a standard procedure to allow users to reset their password.

In order to get this thing working, users will have to provide a “Password Reset Hint” and an “Answer to Password Reset Hint” in their profile. This can be achieved through http://infra_host/oiddas/ui/oracle/ldap/das/mypage/ChgPwdMyPage.

When this information is submitted to the LDAP server, users can use http://infra_host/oiddas/ui/oracle/ldap/DASStep1ResetPwd to reset their password.