Saturday, 23 January 2016

APEX Dashboard Competition for English Locale

The other day Tobias announced a new competition where you can win Apple products, and what you need to do is build a dashboard in Oracle Application Express using some supplied data.


I'm about to go on a little trip so I thought I'd load up this data into my local VM so that if I had some down time, I could have a play around with it (not yet sure if I'll enter!). The only problem was, I started receiving errors during the data import, as per:




As from that screenshot, you will see the number is using a decimal point as a comma (as opposed to what I am used to, and what the session is expecting, a period). To be honest, I didn't even realise different locale's used different decimal marks - but after turning to Wikipedia, I see there are a large number of nations that do: https://en.wikipedia.org/wiki/Decimal_mark

After a bit of research, I discovered that you can change the session parameter NLS_NUMERIC_CHARACTERS to support the different locale's. With that I came up with the script:

@/home/trent/Downloads/APEX_DASHBOARD_COMPETITION_DATEN/DATEN_FINAL/DDL_EXPORT.sql
alter session set NLS_NUMERIC_CHARACTERS=',.';
@/home/trent/Downloads/APEX_DASHBOARD_COMPETITION_DATEN/DATEN_FINAL/DML_EXPORT.sql
alter session set NLS_NUMERIC_CHARACTERS='.,';

With those session modifications the data should (it did for me) load seamlessly.

The next task, was that all the data is in German. It's not a huge deal, as it's the numbers that matter. But, I feel it a lot easier to work with data I can at least interpret! So, I wanted to go ahead and come up with a way to tweak this so I can understand the information better. With a bit of google translate help, I made a series of CSV files to create a mapping. Check out this little project: https://github.com/tschf/dashtrans. In the raw folder is a series of tabular data with the word as it is in the data (original) and an English translation (not all perfect!). Once you have the project files, from the same directory, you can apply the updates as per the CSV files with:

sql dash_comp/dash_comp@//192.168.1.15/xe @to_en

Hope it's useful!

1 comment: