APEX Low code expressions

Uncategorized
| Nov 19, 2019
The following where clause expression would normally return false, therefore the query would deliver no rows.
select * from dual where null != 'X'

no rows returned
A null can never equal a value, and hence cannot be determined to be disimilar to another value. False is the expected condition.

Turns out when using it as what appears to be the  declarative low code alternative, it returns true – and renders.

So if you want (more...)

So you want to submit a conference abstract?

Uncategorized
| Nov 19, 2019
It used to be our annual Perth conference that got me thinking about abstract ideas, but now it's the Kscope abstract submission deadlines that get my boat rocking.

But every day is a good day to think about the topics I'd like to might like to share with to your local community.

I've also been trying to convince a collegue to do their first presentation, and the questions in the ODTUG submission page got me (more...)

Agile Oracle Database Modeling and Development (#AgileOracleDatabase) – I’m a speaker at #DOAG2019

Uncategorized
| Nov 18, 2019

We try to be agile in developing and merging our features into branches when they are ready for use.

Test Driven Development is on everyone’s lips with Java and other languages.

Whether we develop ADF or JET or Spring, we try to apply these techniques in many places.

But what is the real basis of most of our applications?
This is the Oracle database with its data model and programming language PL/SQL!

Again and again (more...)

Reading External File

Uncategorized
| Nov 13, 2019

I’m working on items for migrating my database class from Oracle to PostgreSQL. I ran into an interesting limitation when I tried using the COPY command to read an external CSV file.

I had prepared the system by creating a new directory hierarchy owned by the postgres user on top of a /u01/app mount point. I set the ownership of the directories and files with the following command from the /u01/app mount point:

chown -R  (more...)

PostgreSQL and LPAD

Uncategorized
| Nov 12, 2019

While porting my Oracle code to PostgreSQL I encountered a little quirk. It’s probably not a quirk except for the fact that I’ve worked in Oracle so long. Oracle implicitly type casts so well that we seldom notice.

PostreSQL doesn’t work like Oracle. It does type cast sometimes but not very often. I tried porting the following segment from my Oracle stored procedure to PostgreSQL:

/* Add account number with zeros. */
FOR j IN  (more...)

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:

Postgres Drop Tables

Uncategorized
| Oct 27, 2019

While building my PostgreSQL environment for the class, I had to write a couple utilities. They do the following:

  1. Drops all the tables from a schema.
  2. Drops all the sequences from a schema that aren’t tied to an _id column with a SERIAL data type.
  3. Drops all the functions and procedures (qualified as routines) from a schema.
  4. Drops all the triggers from a schema.

The following gives you the code for all four files: drop_tables. (more...)

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;