Category: Allgemein

Below, I provide a snippet to read data from an Azure SQL Server database. Subsequently, it is written to another table in the same database. The trick here is to read the data, store them in a matrix which will be written out to a second table. Elements within the matrix can be accessed with…

Below, you find a nice example of an hierarchical query in Oracle. The idea is that you see the top-down hierarchy with some indications on the level that is shown. First the query: select level, case when level=1 then first_name||’ ‘||last_name when level>1 then lpad (first_name||’ ‘||last_name,length(first_name||’ ‘||last_name)+level-1, ‘-‘) end tree from employees start with…

What is the size of an Oracle table? A very rough approximation is to multiply the number of rows with the average row length in bytes. Fortunately, Oracle directly provide you with such information. Take this query: select table_name , num_rows , avg_row_len ,to_char((num_rows * avg_row_len)/1000000,’9999999D999′,’NLS_NUMERIC_CHARACTERS=”. ”’) as sizeMB , blocks from dba_tab_statistics where owner…

Oracle has a nice possibility to calculate running totals. Assume we have a sales system where transactions are stored over time. We would like to know the running total – what is sold until a certain transaction. The code to calculate this is: SELECT trans_id,to_char(trans_date,’dd-mm-yyyy hh24:mi:ss’ ) trans_date , product_code ,qty* price amount, sum(qty* price)…

Once, I wrote a completely useless programme – see below. I like to retain it as I spent quite some time on it. But it is absolutely useless. The idea it that we have some kind of a counter that is incremented by 1 if a subsequent record has the same value for a particular…

Oracle has 3 analytical functions to rank data. They provide approximately the same results, but with little deviations. First the code: select salary, rank() over (order by salary) rank, dense_rank() over (order by salary) dense_rank, row_number() over (order by salary) row_number from hr.employees order by salary; Inspecting the results shows the deviations. Look at the…

In previous versions of Oracle, we had to retrieve a top10 rows via a sub query. In the sub query, we ordered and retrieved the data whereas in the main query, we could retrieve 10 top rows. Something like: select * from (select * from hr.employees order by salary desc) where rownum <=10; Oracle 12…

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”,…