Generate JOINs among related tables

After finding the shortest "foreign key" path between two tables, I'll try to generate a SELECT statement that joins all the related tables.

Creating an Oracle Database Vagrant box

Oracle recently launched a new GitHub repository for providing people with Oracle software inside Vagrant boxes. If you have ever setup an Oracle database inside a VirtualBox VM, whether it is as your sandbox environment, to explore Oracle database, or to use it as a full-on development environment, things have just gotten a lot easier … Continue reading "Creating an Oracle Database Vagrant box"

Split a string again and again

I recently needed to gather multiple columns from multiple rows into one string, then split them out again. JSON_TABLE did both splits at the same time!

Neue Oracle VM Appliance

Nur damit ich es irgendwo verlinkt habe: Jeff Smith weist darauf hin, dass im OTN eine neue VM mit Oracle 12.2, SQL Developer 18.1, Oracle REST Data Services 18.1 etc. zur Verfügung steht.

Foreign keys between two tables

In a comment on my post about getting all the foreign key relationships, I was asked how to get the shortest path between two tables. Not too easy...

Splitting Strings: a New Champion!

My last post on splitting strings conceded that "a combined SQL + PL/SQL solution beats the best pure SQL solution." Wrong! I didn't try JSON_TABLE.

Wrong Results with IOT, Added Column and Secondary Index

I found a “wrong results” bug yesterday, easily reproduced in 11g, 12c and 18c.

In short, we may get wrong results under the following circumstances:

  • We have an Index-Organized Table (IOT) with multi-column primary key, populated with rows
  • The table has a secondary index on part of the primary key columns
  • We add another column to the existing IOT
  • We select from the IOT while accessing it via the secondary index

Following is a simple (more...)

The Single Responsibility principle

The Single Responsibility principle is the foundation of modular programming, and is probably the most important principle in the SOLID set. Many of the other principles flow from it.

It is quite simple: a program unit should do only one thing. A procedure should implement a single task; a package should gather together procedures which solve a set of related tasks. Consider the Oracle library package UTL_FILE. Its responsibility is quite clear: it is for (more...)

Database Requirements for Modern Development

A while ago I wrote a paper talking about what key capabilities databases should provide for today’s software development world. The title of the paper is “Database Requirements for Modern Development“. It got published originally on the Oracle Technology Network and I just republished it here on my website. You can find it under: https://geraldonit.com/articles/database-requirements-for-modern-development/

EBR – Part 9: Adding a New Column

This is part 9 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

In part 7 (“Editioning Views”) I introduced our third development use case – adding a new column that represents a new business logic.

I emphasize the fact it’s a new business logic, because sometimes we add new columns that come instead of existing columns, for replacing an existing business logic. (more...)

EBR – Part 8: The Last Planned Downtime

This is part 8 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

I concluded the previous post by stating that the application code should never reference tables directly; instead, every table should be covered by an editioning view and the application code should reference the views.

Starting from Scratch?

If you start developing a new system from scratch, I strongly recommend to follow (more...)

EBR – Part 7: Editioning Views

This is part 7 of a post series about EBR.

Visit the index page for all the parts of the series

Introduction

Our next use case is adding a column that represents a new logic to the PEOPLE table, and making the corresponding changes in the PEOPLE_DL and APP_MGR packages. Of course, as we speak about EBR, the upgrade from the previous version to the new one should be online.
An online upgrade means that (more...)

Ruby GEM Mongo

While trying to use the Ruby gem utility to install the MongoDB gem, I encountered an error on a new Fedora 27 instance. This is the error message:

Fetching: bson-4.3.0.gem (100%)
Building native extensions.  This could take a while...
ERROR:  Error installing mongo:
        ERROR: Failed to build gem native extension.

    current directory: /usr/local/share/gems/gems/bson-4.3.0/ext/bson
/usr/bin/ruby -r ./siteconf20180517-49401-1htl7zc.rb extconf.rb
mkmf.rb can't find header files for ruby at /usr/share/include/ruby.h

 (more...)

Index Skip Scan bei führender Spalte mit vielen unterschiedlichen Werten

Ja, ich habe schon weniger sperrige Titel für meine Einträge verwendet. Leider ist mir nichts Griffigeres eingefallen - und vor allem nichts, was dann noch zum Sachverhalt passen würde: Jonathan Lewis zeigt, dass der INDEX SKIP SCAN manchmal an Stellen auftreten kann, an denen man ihn nicht erwarten würde. Wo würde man ihn also erwarten? Dort, wo ein mehrspaltiger Index existiert, dessen führende Spalte (oder Spalten) wenige distinkte Werte enthält, so dass es für den (more...)

Cassandra on Fedora 27

The last time that I installed Cassandra was on a version of Fedora 20. So, I new the first thing to check was the installation of Java. You can check the Java installation with two statements on a fresh installation of Fedora 27. You need to check the Java runtime and then the Java Software Development Kit.

You check the Java runtime with this command:

java -version

It should return:

openjdk version "1.8.0_171"
 (more...)

MongoDB on Fedora 27

I was very pleased to see MongoDB 3.4 in the repo for Fedora 27; however, I was quite frankly severely disappointed by the lack of concrete installation steps in the MongoDB in Action, Second Edition. The installation is straightforward as long as you remember that you don’t simply install a mongodb package. You need to install the mongodb and mongodb-server packages as well as some dependents.

As the root user, you can use yum (more...)

MySQL on Fedora 27

While updating my class image to Fedora 27, I noticed that it installed the Akonadi Server. The documentation on the Akonadi server lacked some straightforward documentation. It also offered a bundled set of software that limited how to approach MySQL development.

So, I removed all those packages with the following syntax:

dnf remove `rpm -qa | grep akonadi`

After removing those Akonadi packages, I installed the MySQL Community Edition from the Fedora repo with this (more...)

Transposing data using UNPIVOT

A couple of years ago I posted a method to remove nulls from a report using the Value Attribute Paris - Column template.

Here's an example of how we might utilise the region, within the breadcrumb region position.

Note - some values may have been adjusted from this screenshot for their protection.

Any nulls were shown as a tilde, then hunted down and eliminated with some jQuery that executes after refresh of the region, and/or (more...)

Filtering outliers from Oracle APEX activity logs

Last year I described a simple test case that described how to remove outliers from a fictional dataset using the STDDEV() analytical function .
http://www.grassroots-oracle.com/2017/06/removing-outliers-using-stddev.html

I want to follow this up with a practical case using one of my favourite data sets - the apex_workspace_activity_logs that record who opened what page, in what context, and how long it took to generate.

I've been keeping an eye on the performance of a particular page, (more...)

Dynamische Linesize-Einstellung für sqlplus

Die Welt hat vielleicht nicht darauf gewartet, ich aber ganz gewiß: wie Laurent Schneider erläutert, gibt es mit Oracle 18.1 eine dynamische linesize für sqlplus. Diese orientiert sich an der Größe des Shell-Fensters. Hätte ich diese Option von 15 Jahren bekommen, hätte ich dadurch in Summe mehrere Wochen einsparen können, nehme ich an.