Logging Triggers

Oracle Logging Trigger Results

This article demonstrates how you can write log files from triggers on different tables to the same logging table. This approach leverages Oracle’s object types and column substitutability features. It also eliminates the requirement to create a unique logging table for each logging trigger. The trick to accomplishing this requires mastering two skills.

The first skill requires you to learn how to create user-defined types (UDTs) and subtypes. The UDT stores (more...)

Critical Triggers

Oracle Critical and Non-critical Triggers

This article demonstrates how you can write critical and non-critical row-level triggers. You may ask yourself, what are critical and non-critical triggers? That’s a great question. A critical trigger stops processing and raises an exception within the scope of an Application Programming Interface (API). An API is typically a series of end-user forms that help you solve business problems. A non-critical trigger either allows users to perform undesired behavior or (more...)

Oracle Trigger Basics

Oracle Trigger Basics

Once you master the basics of inserting, updating, and deleting data from the database, you typically learn about database triggers. Database triggers are coding elements that let you manage events beyond the limit of database constraints.

Before you can appreciate the power of database triggers, you need to understand what database constraints can and can’t do. Then, together we’ll explore how you can implement database triggers.
Database constraints let you manage events. (more...)

Types & Column Substitutability

Object Types and Column Substitutability

This article shows you how to use extend parent (or superclass) objects. You extend parent classes when you implement specialized behaviors (or methods) in subtypes. That’s because SQL statements can’t work with specialized methods when a table’s column stores subclasses in a superclass column type.

Substitutability is the process of storing subtypes in a super type column. It is a powerful feature of the Oracle database. The “type evolution” feature (more...)

Types & Subtypes

Object Types and Subtypes

This article teaches you how to use subtypes or subclasses. You can define an object type with or without dependencies. Object types can have two types of dependencies. The simplest case occurs when you define an object attribute with an object type instead of a data type. The more complex case occurs when you define an object subtype because it inherits the behavior of the base object type. The base object (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...)

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"

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...)

Index Hints and Distributed Queries

There are two ways to specify indexes in optimizer hints: by the index name, or by the index’s (leading) columns:

The latter is usually preferred, as the writer’s intention is clearer, and it’s immune to changes of the index name.

Note: one can present the opposite argument, that specifying the index name is immune to changes of column names

Recently I’ve used the option that I prefer – specifying the column names and not the (more...)

Translating Chinese to English in SQL with Microsoft Translator

In Oracle, I had a table of data which had some Chinese words that I needed to translate into English on the fly, in SQL…this is how I did that…

Microsoft have a translator facility here with the Translator Text API v3.0 to allow you to call it programmatically. I’m using Microsoft as I’m currently working on Azure – of course, there are other translation facilities available.

The API has a translate method which one (more...)

The importance of being aliased

| Jul 20, 2018
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates. I was writing a query returning the indexes to be monitored for a list of schemas for those tables having fewer than 5 indexes (not counting LOB indexes) and I was surprised to see that it returned no rows. After checking what's wrong I realized that I had specified the wrong column for

Index Monitoring in Oracle Database

Applies to ONLY Oracle Database 10gR1 through to 12cR1.
In Oracle Database 12cR2, this feature is replaced.  A new blog post of this feature will be posted in due course.

Since Oracle 10g, you can monitor indexes to see if they are being used or not.  Which is very useful as indexes, consume unnecessary CPU and I/O on DML activity if not used.  Therefore, it’s recommended to monitor indexes and any unused (more...)