Hello World with Oracle XE

I needed to develop one small application that communicates with external mobile devices, being like a central server where mobile clients send their data and receive configuration parameters. Reporting will be added later using Oracle APEX. It's a small application for a customer demo, so it sounded like a perfect opportunity to have my first look at the Oracle XE database :)

Oracle XE comes with a pre-configured XDB HTTP listener for APEX. I don't need the full APEX engine for my simple request server, just a PL/SQL package using PL/SQL Web Toolkit will do just fine and there is (more...)

High performance data unloading from Oracle to ODBC database

I got a task some time ago, to copy a-lot-of-millions of rows from Oracle to MySQL. As fast as possible.
In the last year I have used Oracle Heterogeneous Services (HSODBC) a lot to select data from different non-Oracle databases, but I had yet to try to unload data from Oracle with best performance.

My first thought was, of course, that the process needs to run in parallel. Here I'll first take a look at the overall HSODBC performance and then look how can I make this process run in parallel.

In this test I use Oracle Database 11.1. (more...)

Using linguistic indexes for sorting in open source databases

Here I'm following up my previous post Using linguistic indexes for sorting in Oracle. I don't much like the Oracle solution, that requires creating a special index to speed up sorting, but... at the same time its very powerful, allows to index in many languages and no database changes are needed.

In this post I’ll take a look at the two popular open source databases MySQL and PostgreSQL. I'll take a look only at features, that the database has included and that can be used without any special application changes.

PostgreSQL 8.4

Starting from 8.4, collation (sorting) rules (more...)

Refreshing test database from production using incremental backups

It's common to use a production database copy for testing. But how to keep it fresh? If production database is large and located on a remote site (even on another country/continent), then copying the full database backup over network may be too unreasonable and if the test database needs to be open for a longer time, then transporting and storing production archived logs to test site may be too unreasonable also.
In this situation Oracle has three great technologies that can help: physical standby database, flashback database and RMAN.

Here I'm using Oracle Database EE.

Initial (more...)

Using linguistic indexes for sorting in Oracle

In Estonia we have our own language and Estonian language has its own alphabet that is a little bit different from English :) And it would be good if a database application sorts text fields according to the correct language rules (I have seen many bug reports from clients, that "the sort order is wrong").

In Oracle database its quite easy to sort according to the correct language rules, you just need to set the correct database session language from the client. This can be done using ALTER SESSION SET NLS_LANGUAGE or quite often the database client picks up the (more...)