Building a Conference Session Recommendation engine using Neo4J Graph Database

This article describes a use case for which a traditional SQL-powered relational database approach can provide a solution – but for which that traditional approach is not the optimal solution. SQL is jack of all trades – you can make it do almost anything you need. And therefore it is easy to become your hammer and every challenge a nail. This article is if anything meant to open my and maybe your eyes to the (more...)

Polymorphic Table Functions

I have been working on a presentation on Polymorphic Table Functions. During this time I was looking for a real use case for Polymorphic Table Functions. I came up with an example which is not very useful in real life, but very useful to explain the technique.
At my current job I came across a piece of code that I had to copy and adjust to fit the needs for that specific case. The idea (more...)

Chart your SQL direct with Apache Zeppelin Notebook

Do you want a notebook where you can write some SQL queries to a database and see the results either as a chart or table? 

As long as you can connect to the database with a username and have the JDBC driver, no need to transfer data into spreadsheets for analysis, just download (or docker) and use Apache Zeppelin notebook and chart your SQL directly! 


I was impressed by the ability of Apache (more...)

Ranges with NULLs 04: Pack, Merge

In this post, I'm going to treat two closely related but distinct requirements:
  1. "Pack": in a SELECT, return contiguous or overlapping ranges as one range.
  2. "Merge": modify a table to reduce contiguous or overlapping ranges to one range.
These operations should take into account other data associated with each range: ranges are packed or merged only when the other data is the same.

Preprocessing External Tables

A question that comes up now and again is there a way in Oracle Database 11g Express Edition to mimic some behavior in the Oracle Standard or Enterprise editions. Many of these questions arise because developers want to migrate a behavior they’ve implemented in Java to the Express Edition. Sometimes the answer is no but many times the answer is yes. The yes answers come with a how.

This article answers the question: “How (more...)

External Tables

Oracle Database 9i introduced external tables. You can create external tables to load plain text files by using Oracle SQL*Loader. Alternatively, you can create external tables that load and unload files by using Oracle Data Pump. This article demonstrates both techniques.

You choose external tables that use Oracle SQL*Loader when you want to import plain text files. There are three types of plain text files. They are comma-separated value (CSV), tab-separated value (TSV), and position (more...)

Getting Crazy with Analytic Functions and Group By

This topic has been sitting in my backlog for a long time and I finally decided to write it. Analytic functions are not so new anymore (they’ve been around since Oracle 8i), but they are still a very powerful tool. This is based on a real case I had quite a few years ago. A … Continue reading "Getting Crazy with Analytic Functions and Group By"

Ranges with NULLs 03: Gaps

When I wrote "Gaps in Date Ranges: when are you free?", I handled NULLs using "magic" values. This time I'll try not to cheat.

Querying and Publishing Kafka Events from Oracle Database SQL and PL/SQL

IMG_6351One of the session at CodeOne 2018 discussed an upcoming feature for Oracle Database – supported in Release 12.2 and up – that would allow developers to consume Kafka events directly from SQL and PL/SQL and – at a late stage – also publish events from within the database straight to Kafka Topics. This article briefly walks through the feature as outlined in the session by Melli Annamalai, Senior Principal Product Manager at Oracle.

(more...)

Ranges with NULLs 2: test cases

When I write SQL there is a bit of trial and error (laughter), so I need as complete a set of test cases as possible. Here's what I came up with.

Rapid generation of Oracle DDL scripts for Tables, PL/SQL APIs, Sample Data

imageYesterday, at the Oracle ACE Directors Product Briefing, I received a gift. It is called QuickSQL. And it is a free online service that generates SQL DDL and DML scripts. The gift in this case was the knowledge about this service – I was not aware of it.

image

Go to http://quicksql.oracle.com. Try it out. If you have a need for a quick set of database tables for a demo or a prototype – (more...)

Ranges with NULLs 1: starting over

I have written a lot about ranges, mostly based on dates, and I have tried my best to avoid NULLs in "from" and "to" columns. I give up: NULLs are a fact of life and must be dealt with. This means revisiting all my previous work!

ODC Appreciation Day : Pattern Matching in SQL

Here’s my contribution to the ODC Appreciation Day.

Pattern Matching in SQL, using the MATCH_RECOGNIZE clause, is one of my favorite features, but only recently I’ve used it “for real”.
MATCH_RECOGNIZE allows us to perform enhanced analysis of row sequences, and to detect sequences that match complex patterns.
This feature gave a significant boost to the analytical capabilities of SQL. It enables solving various types of problems in a simpler way than before, in much (more...)

List of Bank Holidays For England in SQL Format

First off, create a table to store the bank holiday values. You may need to adjust this slightly depending on your SQL server technology being used (this was tested on MySQL Server).  [crayon-5bb4e0b3b1755979540269/] Insert the bank holiday values below. This table is currently for 2012 up to 2019 for England and Wales.  [crayon-5bb4e0b3b175d512369946/] For a

MySQL/ MariaDB Error Code: 1329. No data – zero rows fetched, selected, or processed

The above error can occur when calling a cursor results in no rows, or no more rows if called in a loop. Whilst the error message is descriptive about what has happened physically, you may wish to catch the error so that you can do something else, or simply replace the generic database error with

How to exit from a MySQL/ MariaDB Stored Procedure/ Function Prematurely

MySQL and MariaDB enable you to define your own error conditions and to report back to the SQL client both a return code and an error message. As soon as you raise the condition then MySQL/ MariaDB will halt any further execution of the code and report the error back to the client. This can

Remove duplicate from APEX collection

One of my favourite SQL analytic functions is row_number(), and I've used it in the past to identify, then remove duplicates.

In this case, I have an APEX collection that represents a session based view history of products/people/events, or whatever your users might be browsing.

I've created an option to consolidate that view history, and remove any record you might have opened more than once.

Collections are a little hard to play with outside (more...)

ORA-01723 für DATE-Angaben

Nach langer Zeit mal wieder etwas Selbsterlebtes, das einen Eintrag zu verdienen scheint - wie man eine CTAS-Operation über das nls_date_format torpedieren kann:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> create table t as select to_date(null) col1 from dual;
create table t as select to_date(null) col1 from dual
*
ERROR at line 1:
ORA-01723: zero-length columns are not allowed

SQL> alter session set nls_date_format = 'DD-MON-RR';

SQL> create table (more...)

JSON, BLOB column and Check Constraint

Last week I attended Neil Chandler's session on JSON during the POUG conference in beautiful Sopot, Poland.
The JSON Developer's Guide recommends using BLOB for storing data, and this is what Neil also recommended.
I was under the (FALSE!) impression that it was not possible to put an IS JSON check constraint on a BLOB column, simply because I tried once and got an exception. After the session I asked Neil if this was (more...)

Many happy birthdays

Last year I *(not pictured) celebrated my 42nd circuit around the sun. In accordance with time-honoured tradition, it has been celebrated some time around the same day each September with variations on the following theme:

  • a get-together with friends and/or family
  • my favourite meal (usually lasagne)
  • my favourite cake (usually a sponge coffee torte, yum)
  • a gift or two
  • the taking of photographs, to document how much I’ve grown since the last one

Each year, (more...)