Category: nice to know

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: select department_id, listagg( first_name,’-‘) within group (order by hire_date) from employees group by department_id; The result is a 12 record outcome, one record for each…

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 that the BLOB can be inserted. In a PL/SQL programme: which call be invoked by (example) exec UPLOAD_FILE_TO_DATABASE(‘TEMP’,’2019-11-15 12.46.30.mov’,’Nora’); Likewise, downloading a BLOB is done…

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 the other hand, we have a comma (,) as decimal indicator. Let me provide my own Oracle instance. I have the following language settings: select…

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 pump. We may export data via data pump as: CREATE TABLE stu_details_ext ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY EXTERNORA LOCATION (‘stu_details_ext_table.dmp’) ) AS SELECT…

The situation is as follows. We have a table with records that are added over time. The request is to produce a resultset that only contains values that are added latest. Let me first show the table: CREATE TABLE “RELATION_VALUES” ( “RELATION_ID” NUMBER, “STARTDATE” DATE, “VALUE” NUMBER ) ; Insert into RELATION_VALUES (RELATION_ID,STARTDATE,VALUE) values (‘1′,to_date(’11-FEB-2019′,’DD-MON-YYYY’),’80’);…

Oracle needs to have a set of context variables that allow Oracle to know where the programmes are located. Upon installation, you have to check if these variabled are set. If not, you have to set them yourself. In linux, such variables can ne set in the .bash_profile file that is stored as a file…

Let us assume we have a request to show results from a query that is too massive to run on-line. Let us also assume that we may distinguish in this query a series on countries / persons that must be run. In pseudo code, we have the situation that we may have to run: select…

This note provides a PL/SQL programme that writes the the content of a table to a file. It also uses a steering table (emp_ids) that contains a series of ids that can be used in a susequent query from which the content can be generated. Let me first provide the code: create or replace PROCEDURE…

I have played a bit with Oracle data miner. This feature is available in the enterprise edition of Oracle version 11 or above. It can be accessed from SQL developer. Before using the feature, one must first set up a user. This can be done with: CREATE USER dmuser IDENTIFIED BY dmpassword DEFAULT TABLESPACE users…

Today, I struggled a bit with a simple situation. I had a table with a primary key and an additional field that was partially filled. I also had a secondary table with a key and data were to be loaded in the first table. In a scheme, the situation looked like: In this example we…