Size on Oracle
tom
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…
Running totals in Oracle
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 –…
A completely useless PL SQL programme
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…
Ranking in Oracle
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()…
Top 10 rows in Oracle
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…
Oracle Aggregate and analytic functions
We all know aggregate functions in Oracle. Let me provide a simple example to recap what the aggregate function is. Let us firse this SQL on a 107 record table:…
Inserting a BLOB
Inserting a BLOB in Oracle is slightly different from inserting from inserting a value in an Oracle database. We first have to create a location with n empty BLOB. After…
Oracle create loader file
Creating a loader file is tedious. The syntax is complicated. Moreover, small errors lead to a rejection of records. Luckily, we have the possibility to generate them from SQL Developer.…
Oracle numerical data
Numbers can be stored in Oracle according to different formats. On one hand, we have the American way, whereby numbers are stored with a dot (.) as decimal indicator. On…
Copying data with data pump
Oracle has a brilliant feature to export / import data at great speed. The trick is to use the data pump facility. Let us first introduce the export via data…