Category: Allgemein

The issue about roll ups in Oracle is that roll up only calculate sub totals in one direction. So if you have a country, city combination, subtotals are calculated per city in a country, but not in a country per city. In case of a hierarchy, this is ok. One is only interested in having…

Oracle has a nice feature that allows to show a grand total. I assume you want to show a line with a grand total when a grouping by SQL is executed. Something that shows the number of employees per department and the total number of employees. That can be done by a simple change in…

The idea behind real time loading a data warehouse is to propagate data directly after an event has happened in an underlying source database. Let us think of an invoice database. In most circumstances, the invoice database is loaded with records that represents invoices that are sent. During the night, a set of records in…

ODI has a feature that allows to read XML data. I really enjoyed working with this feature. It allowed to directly insert values from an XML file into different fields in an Oracle table. The idea is very simple. One has to set up a technical and logical schema that is based on XML. With…

Oracle has a very nice ETL tool (or in Oracle parlance ELT tool). Its purpose is clear: make a easily maintainable set of workflows that show clearly the lineage from source to target. To use ODI, we must download a zip file from Oracle. It can be found if we look for “download ODI”. We…

Oracl has a nice feature to add a rank to a row. Let us assume you have a table with 4 values: some rows have an A, others have a B, others have a C and some are D. Oracle is able to provide a rank to this series by providing a 1 to A,…

The idea is actually quite simple. Whenever a record in a table (here: employee) is updated with a new value in a field (here: salary), a trigger gets fired. This trigger stored the old and new value of salary in a seperate table. I like this trigger. It is simple. It is straightforward. create or…

This note provides an example on a trigger. I like this example as it shows how additional business logic can be implemented. It is an example of a statement trigger: whenever a statement is issued, the trigger fires once, no matter how many records are affected. CREATE OR REPLACE TRIGGER security_time_check BEFORE DELETE OR UPDATE…

Oracle has a package that allows you to write to a file. Oracle uses the concept of a directory (here “External”) that acts as a logical description of a directory. One of the properties of such directory is the physical location. That file can be opened, written to and closed. Subsequent programme provides an example.…

This programme shows you we may retrieve the results from a query. It works with a cursor that contains another cursor. The example below is a cursor that retrieves data from employee table, along with data from a cursor that is linked to the employee table. Doing so, we may add data to the employee…