Apex Unreachable after Database Upgrade from 9.2 to 10.2

Recently we were performing a database upgrade from release to 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] [ecid: 1221626419:,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 ( (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


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


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


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


  • 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
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
[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
[oracle@lnx-srv-dwh-dev freetds]$ ls
[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
[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; \
if test ! -f /usr/local/freetds/etc/locales.conf; then \
/usr/bin/install -c -m 644 ./locales.conf /usr/local/freetds/etc/locales.conf; \
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:
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
[root@lnx-srv-dwh-dev etc]# cp odbcinst.ini odbcinst.ini.ori
[root@lnx-srv-dwh-dev etc]# vi odbcinst.ini
Description = ODBC for PostgreSQL
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1

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
[oracle@lnx-srv-dwh-dev etc]$ cat odbc.ini
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 |
| |

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:

{ 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!