Author: tom

An external table is a flat file outside the Oracle database. If properly described in Oracle, it can be read as an Oracle table. The syntax consists of 3 elements: a create table part an ORGANIZATION EXTERNAL part that indicates that we are discussing an external table a set of attributes that discusses how the…

PL/SQL with the utl_file package allows us to manipulate strings in Oracle. One may read a string from a file, modify this and subsequently write it to another file. This opens nice possibilities. Let me show some code how to do this: DECLARE v_file_ptr utl_file.file_type; v_file_handler utl_file.file_type; v_line varchar2(2000); BEGIN v_file_ptr := utl_file.fopen(‘EXTERN’,’namen.txt’,’r’); v_file_handler:= utl_file.fopen(‘EXTERN’,’uitvoer.txt’,’w’);…

JSON stands for Java Script Object Notation. It is a way to describe and store data. It can be read and modified in any editor. It has components that describe the data and components that store the data. An example is subsequent line: {“job_id”:”AD_PRES”,”job_title”:”President”,”min_salary”:20080,”max_salary”:40000} Components like “job_id”, “job_title”, “min_salary”, “max_salary” describe the data, whereas “AD_PRES”,…

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…