Temporal validity and open/closed intervals

A short while ago I created a SQL quiz for Oracle Dev Gym (PL/SQL Challenge) demonstrating the use of temporal validity and VERSIONS PERIOD FOR syntax to create a "change report" for changes in item prices. Use of temporal validity makes this easier, shorter and more readable code, but even though I thought I had it all covered, player Iudith Mentzel pointed out a tiny quirk I'd overlooked.

You see, the "change in price" wasn't (more...)

APEX Plugin: Calendar Heatmap Region

I was looking at the GitHub "contribution chart", which shows number of commits per day as a heatmap calendar. I thought this type of chart would be cool to have as an APEX plugin, so I went ahead and implemented it.

Here is a video I recorded to walk through how the plugin was made:

Note: For an alternative implementation, see the "Block Calendar" in Oracle JET. As far as I can tell, there (more...)

Powerless Javascript

I was writing a small javascript function, part of which needed to evaluate 10 to the power of a parameter – I couldn’t remember what the exponentiation operator is in javascript so as usually I hit F12 and typed the following into the console:



Wrote and tested the code, checked in to source control. Job done.

A few days later we deployed a new release that included dozens of bug fixes into UAT for testing. (more...)

Announcing the SQL Annual Championship for 2016

I've been making SQL quizzes for years now over at the Oracle Dev Gym (or PL/SQL Challenge as it started out as.) Every year I really have to work my few braincells hard when it is time for the annual championships. The players that have made it through at year and managed to rank in the top are awesome at SQL, so I have to write some really hard quizzes for the championship.

But (more...)

Re-evaluating APEX Authorization Schemes

Authorization schemes in Oracle APEX are used to control access to page, buttons, and all sorts of other components.

In my experience, these are best defined at a privilege level, where the same privilege could be allocated to multiple business roles, but that's for another post.

In this post, I want to mention a cool API function called apex_authorization.reset_cache, which helps control the behaviour of these authorization schemes.


While googling something else (more...)

Cloud Data Warehousing: Is it for real?

  Our industry is full of hype and hyped terms. Big Data. NoSQL. The Cloud. Self-service <whatever>. And Cloud Data Warehousing. Some of the offerings and solutions are real. Some less so. Newest on the scene is cloud data warehousing (or data warehousing in the cloud). As with all new tech, there are a variety […]

Zur Semantik des USE_NL Hints

Ich erinnere mich, dass Jonathan Lewis diesen Punkt schon häufiger erwähnt hat, aber offenbar hatte er tatsächlich noch keinen Artikel dazu geschrieben, und dies jetzt nachgeholt: häufig sieht man in Oracle SQL-Queries Hints der folgenden Form:
use_nl(t1 t2)
Und es gibt wohl in der Tat viele Verwender, die davon ausgehen, dass man den Optimizer damit anweist, einen NESTED LOOPS Join zu verwenden, bei dem t1 via NL Join mit t2 verknüpft wird, wobei t1 die (more...)

New Year, New Look – Get a New UI on Us

Looks DO matter! Today’s users are fed up with the current look and feel of their Oracle Forms / EBS System, many of which were developed last century.  But we know how hard it can be to take the first step towards modernization. As Forms administrators / DBA’s your management many not want to believe there is any life left in these applications and won’t move forward. He probably thinks its wiser to move on. But (more...)

The Relational Data Model

Mid-eighties. I was studying Computer Science. Little did I know back then that this thing called "The Relational Data Model" (RDM) would become huge in the IT-industry. The topic was still hot in academia at that time. My luck was that I liked those courses. Predicate Logic, Set Theory, Database Design, SQL. I aced them all. It was no surprise then, that I ended up working with Oracle software

How to add an NCSA style Access Log to ORDS Standalone

What ORDS Standalone is      ORDS Standalone webserver which is Eclipse Jetty, https://eclipse.org/jetty/ .  For the standalone, ORDS sends output to STDOUT, it runs on the command line.  That means there's nothing like a control commands like startup, shutdown,status nor log files, access logs.  It's bare bones intentionally to get up and running fast.  Then it's recommended for anything with

Redundante Prädikate zur SQL Optimierung im SQL Server

Da ich nicht so oft lobende Erwähnungen auf anderen Webseiten erhalte, will ich nicht darauf verzichten, diese hier zu verlinken: mein alter Freund und ehemaliger Kollege Andrej Kuklin hat im SDX Blog einen Artikel veröffentlicht, der sich damit beschäftigt, wie man Queries im SQL Server durch die Ergänzung eigentlich redundanter Prädikate optimieren kann. In seinem Beispiel läuft eine Query mit einem Inner Join schnell, so lange über eine gegebene Variable auf ein bestimmtes Datum eingeschränkt (more...)

Maria Colgan antwortet bei AskTom

Ein vielversprechender Start ins Jahr 2017: Maria Colgan gehört jetzt neben Chris Saxon und Connor McDonald zum Team bei AskTom. Außerdem hat sie damit angefangen, in ihrem eigenen Blog zu schreiben. Da es vermutlich kaum jemanden gibt, der mehr über den Optimizer (oder ind In-Memory-Optionen) weiß als Frau Colgan, ist das eine günstige Entwicklung.

Multi-Table Inserts with Sequences

Multi-table inserts are a drag when sequences are involved, because we don't have enough control over when the sequence is incremented. I've seen some dangerous workarounds. Here is a workaround that I think is safe.

Disappearing Data

Did you know that Oracle DML can silently change data you want to put into the database? A recent twitter discussion started by Martin Widlake (@MDWidlake) prompted me to study more closely what happens when.

Oracle Diagnostic Queries

It’s always a challenge when you want to build your own Oracle SQL Tools. I was asked how you could synchronize multiple cursors into a single source. The answer is quite simple, you write an Oracle object type to represent a record structure, an Oracle list of the record structure, and a stored function to return the list of the record structure.

For this example, you create the following table_struct object type and a table_list (more...)

APEX attributes for Escaping Special Characters

A relatively common on the forums is regarding the escaping of special characters in reports, but it seems the developer isn’t always sure what is actually happening and how to how to search for it.

It seems I’ve had this on my “to blog” list since April 2015, but now that 5.1 has been released, it seems more people are coming out to leave 4.x can’t work out where the Standard Report (more…)

Want to Learn Something New in 2017?

Happy New Year everyone! This is the time of year when we all make plans and set goals for the new year, right? So what are you going to do different this year? How about grow your career by learning something new? How? Read a book on an area of tech you are not so familiar […]

APEX: Display the Page Alias on every page – without modifying the Template

Having a Page Alias shown on the page can make communication with your end users a little bit easier. Instead of directing them to the URL and asking them for the second value shown after the "f?p", it is a little bit simpler to direct them to the location of the Page Alias, e.g. bottom left corner of your screen.
For my projects I tend to use the Page Alias as a link (more...)

Happy New Year!

Filed under: Uncategorized Tagged: #HappyNewYear, Happy New Year

The Future of Forms – DOAG Forms Day in Berlin, 18.01.2017

Today, and it's the last day of the old year, I had to announce, that the German Oracle User Group (DOAG) will have a whole day of forms-talks:

DOAG Forms-Day 2017

Come and share your Forms 12c experience with the community.

I wish all of you a good start in the new year