Things I learned at a user group event

Uncategorized
| Nov 12, 2019
Yesterday I attended a friendly, informative workshop by Shakeeb Rahman and Christina Cho, members of the Oracle APEX product team.

Reflecting on the worthiness of attending such an event, I thought I'd list out all the things I learned & observed.

  1. Everyone learns.
    It was observed that 'gurus' such as Trent, Lino, and myself were there, and maybe this might influence the level of detail content delivered. Sure, I like the details, and a particular (more...)

Postgres Foreign Keys

Uncategorized
| Nov 6, 2019

Just sorting out how to query the information_schema to discover the magic for a query of a table’s foreign key constraints. This query works to return the foreign key constraints:

SELECT   conrelid::regclass::text AS table_from
,        conname AS foreign_key
,        pg_get_constraintdef(oid)
FROM     pg_constraint
WHERE    contype = 'f'
AND      connamespace = 'public'::regnamespace 
AND      conrelid::regclass::text = 'rental_item'
ORDER BY conrelid::regclass::text
,        conname;

It returns the following for the rental_item table:

 table_from  |   foreign_key    |                           pg_get_constraintdef                            
-------------+------------------+---------------------------------------------------------------------------
 rental_item |  (more...)

Postgres Overloaded Routines

Uncategorized
| Nov 5, 2019

Earlier I showed how to write an anonymous block in PostgreSQL PL/pgSQL to drop routines, like functions and procedures. However, it would only work when they’re not overloaded functions or procedures. The following lets you drop all routines, including overloaded functions and procedures. Overloaded procedures are those that share the same name but have different parameter lists.

Before you can test the anonymous block, you need to create a set of overloaded functions or procedures. (more...)

Do you want to learn about database technology?

Uncategorized
| Nov 4, 2019
Do you live close enough to Melbourne, Brisbane, Sydney, Seoul, or Tokyo to attend a software development roadshow for a day in November?

Are you interested in how easy it is to build data driven web based applications?
Perhaps you're a student of the programming world?

Your local user group is hosting some visitors from the US that I think are worth listening & engaging with, in person. And if you're not a member of (more...)

DBeaver for PostgreSQL

Uncategorized
| Nov 3, 2019

I’m migrating my database classes from the Oracle database to the PostgreSQL database. Using the Oracle Express Edition has always required a virtualized image because students use Windows and Mac OS. Also, the university doesn’t like my use of a virtualized image. Virtualization imposes incremental cost on students to have high end laptops.

The available Docker images don’t typically support the Oracle Express Edition. That means there are licensing implications tied to Oracle.

As a (more...)

Java and Postgres

Uncategorized
| Nov 2, 2019

I wanted to get Java working with PostgreSQL to test some GUI interfaces on Linux. Figuring out the necessary JAR file for the JDBC was my first hurdle. I found it was postgreSQL-42-2.5.jar file.

You can download it with the following command line:

wget https://jdbc.postgresql.org/download/postgresql-42.2.5.jar

I downloaded it to a Java directory off the home/student directory. Then, I added the following CLASSPATH to local java.env environment file.

 (more...)

Getting Started with APEX Plugins

Uncategorized
| Oct 31, 2019

There is a great deal you can build in APEX without even thinking about using a plugin. The development platform has an excellent range of built-in functionality and components so that almost all user requirements can be covered using standard APEX features. Restricting yourself to only the built-in, declarative features (i.e. “low code”) of the tool will result in applications that are easy to maintain and resistant to regression issues when upgrading. However, there (more...)

MATCH_RECOGNIZE Restrictions

Uncategorized
| Oct 31, 2019
Sometimes we think that certain restrictions are not documented when in fact they are. Where do we forget to look? Database Error Messages

Backtracking

Uncategorized
| Oct 30, 2019
By popular request, here are my thoughts about the impact of "backtracking" on performance when using the MATCH_RECOGNIZE clause. This came up again because of a query that Jonathan Lewis wrote recently; however, I will concentrate on the theory, not the query.

User-editable Application Setting

Uncategorized
| Oct 28, 2019

A nice addition to APEX release 18.1 is the Application Settings feature. This allows the developer to define one or more configuration values that are relevant to a particular application. In a recent project this feature came in useful.

I had built a simple questionnaire/calculator application for a client and they wanted a small “FAQ” box on the left-hand side of the page:

I could have built this as an ordinary HTML region, but (more...)

Speicher-Fragmentierung für Linux-Server

Uncategorized
| Oct 28, 2019
Nikolay Savvinov hat zuletzt mehrere interessante Artikel zum Thema der Memory Fragmentation auf Linux-Systemen veröffentlicht, die ich hier einfach mal verlinke, ohne mich allzu intensiv mit den Inhalten zu beschäftigen:

Predict_Clustering_Factor

Uncategorized
| Oct 26, 2019
Jonathan Lewis recently wrote about estimating the clustering factor of an index, taking into account the intended value of the TABLE_CACHED_BLOCKS parameter of DBMS_STATS.SET_TABLE_PREFS. He included a function I wrote called predict_clustering_factor. Here is a corrected and improved version.

Turning your Raspberry Pi into a science research station via BOINC

Uncategorized
| Oct 26, 2019
Use your computing power for the greater good

Subquery

Uncategorized
| Oct 24, 2019


with x as (select sysdate from dual)
select * from utter_bollocks.x;

How to resize a btrfs root partition on Oracle Linux

Uncategorized
| Oct 19, 2019
Resizing a root partition (/) of a running Oracle Linux isn’t hard but the information out there on the web is limited. In my situation, I was given access to an already installed Oracle Linux environment that wasn’t using all the available space of the disk. The root partition, formatted with the btrfs filesystem, only … Continue reading "How to resize a btrfs root partition on Oracle Linux"

Erläuterungen zu Execution Plans in Postgres

Uncategorized
| Oct 18, 2019
Damit ich es wieder finde: David Conlin hat einen Glossar zu den Angaben in den Execution Plans des explain Befehls in Postgres veröffentlicht. Darin finden sich auch Link zu älteren Artikeln von Hubert Lubaczewski (aka DEPESZ). Obwohl ich ziemlich oft auf Ausführungspläne schaue, gibt es in diesem Bereich immer wieder Überraschungen - und da ist jeder erhellende Beitrag nützlich. Interessant ist etwa der Hinweis, dass die summierten Angaben bei "Materialize" Knoten aufgrund von Rundungen manchmal (more...)

Show null for switch items

Uncategorized
| Oct 11, 2019

An application I maintain needed a checklist feature added. I wanted to show a “Yes / No” switch for a list of checklist items. Initially, when the record is created, the checklist is populated with the questions along with a NULL for the response.

I generated the switches in an ordinary Classic report using code like this:

select r.name as risk_category
      ,apex_item.switch
         (p_idx        => 10
         ,p_value      => i.response
         ,p_on_value   => 'Yes'
         ,p_on_label    (more...)

OGB Appreciation Day : Oracle Reports Server Queue Monitoring

Uncategorized
| Oct 10, 2019
Today is a day to celebrate what's great about our Oracle 'Groundbreakers' community. Our favourite stuff, what we're learning, horror stories, or just a little thanks.

I missed the second year because of holiday prep - so I decided to beat that this year by writing this the day I saw Tim's call-out on Twitter. Especially since I'm flying out to Melbourne for AUSOUG Connect, plus a little R&R.

I knew I had a (more...)

OGB Appreciation Day: APEX_DATA_PARSER: Flexible, Powerful, Awesome (#ThanksOGB #OrclAPEX)

Uncategorized
| Oct 10, 2019

For this years OGB Appreciation Day I wanted to highlight the very awesome APEX_DATA_PARSER.

At my current project one of the requirements is that CSV-files are to be uploaded by the user and the application has to figure out what type of file it is and process accordingly.
So the challenge with this is:

  • Which type of file is it?
  • What delimiter was used?

Uploading the file is straight forward enough with Application Express (APEX) (more...)

OGB Appreciation Day : How I failed to appreciate NULL

Uncategorized
| Oct 8, 2019
On the annual OGB Appreciation Day (#ThanksOGB has been renamed multiple times, see Tim Hall's blog post) I am going back in time to when I was a newbie developer, relating a story about me making a mistake that had bad consequences in the customer ERP system. Luckily I had experienced colleagues to repair the damage, and later in my career I learned about NULL and how it could have been so different back (more...)