Oracle SOUNDEX Function with Examples

The Oracle SOUNDEX function is one of the most interesting functions I’ve used in Oracle. Learn what it is and how it works in this article. Purpose of the Oracle SOUNDEX Function The Oracle SOUNDEX function allows you to check what a value sounds like. It returns a value that represents the phonetic value of […]

Leveraging Oracle Developer Cloud Service for APEX & Oracle DB Lifecycle Management

Oracle Developer Cloud Service provides a great environment for the management of code versioning and build automation for SQL and PL/SQL developers too.

Check out this demonstration by Julian Lane showing how he is managing versioning of Oracle APEX applications with the Git repository in Oracle Developer Cloud Service.

He then shows you how you can automate the deployment of SQL scripts to an Oracle Database Cloud Service with simple scripts.


Interactive Grids (Apex 5.1 EA) and TAPIs

DISCLAIMER: this article is based on Early Adopter 1.


I’ve finally got back to looking at my reference TAPI Apex application. I’ve greatly simplified it (e.g. removed the dependency on Logger, much as I wanted to keep it) and included one dependency (CSV_UTIL_PKG) to make it much simpler to install and try. The notice about compilation errors still applies: it is provided for information/entertainment purposes only and is not intended to (more...)

Forecast: Partly Cloudy — Connecting #OBIEE to @SnowflakeDB

My good friends at RedPill Analytics have done it again! In their never ending mission to #ChallengeEverything, they thought it would be cool to try to connect OBIEE (Oracle Business Intelligence Enterprise Edition) to the Snowflake Elastic Data Warehouse as a way to give OBIEE users access to a high performance data warehouse cloud service. This […]

The Importance of Routine

In the last two weeks, I’ve learnt about the importance of a routine. How does it relate to your career, and why should you care? The Last Two Weeks So, you might have noticed there hasn’t been a post on Complete IT Professional in the last week or so. I mentioned there was a delay […]

Index Sanity Check für Cardinality Schätzungen

Auf das Thema weise ich in den OTN-Foren recht häufig hin und da ist es gut, dass Jonathan Lewis einen Artikel dazu geschrieben hat, auf den man verweisen kann: die Löschung eines Index kann den Ausführungsplan einer Query auch dann ändern, wenn der Index gar nicht im Execution Plan erscheint. Die Erklärung dafür sind Sanity Checks die auf der Basis der Index-Statistiken für mehrspaltige Indizes operieren. Im Artikel stellt Jonathan Lewis ein kleines Beispiel vor, (more...)

Have fun #KScope16

Full and utter credit goes to the gang at CommitStrip, you should check it out.

I thought a few choice replacements would help Joel Kallman and team rip into what I'm sure will be another stellar set of Sunday Symposium in Chicago.

Credit to CommitStrip, text modified from original

We'll be listening around the world, keep your tweet on.

INSERT into a View with a GROUP BY Clause

When I wrote the previous post, about updatable views, I noticed an interesting issue.

The documentation says:

If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the view.

Let’s create a view with a GROUP BY clause and a group function:

ORA$BASE> create table t  (more...)

Kill DB sessions the easy way with SQLcl

Seems I can not tweet these animated GIFs anymore. So this is another very short blog post to show real case for adding a new command into SQLcl which can be downloaded here. It's hard annoying tedious to find SID and SESSION then alter to kill that when needed.  What if there was a simple kill command. Here a sample one that takes in the 2 options:   kill sid serial#   Example :   kill (more...)

New, Improved IN Lists!

In SQL, the "varying IN list" problem comes up constantly and there are many published solutions. My new favorite solution is inspired from the video Synthesizing rows inside Oracle by Connor McDonald (start at the 6 minute mark).

Historical SQL Plan from Statspack using DBMS_XPLAN in 12c

Up to Oracle 11.2 it was possible to display archived SQL plans from Statspack using DBMS_XPLAN. I make use of this in some of my scripts and SQL Developer Reports since I first saw this in Christian Antognini’s Book “Troubleshooting Oracle Performance“. But in 12c (here: on Linux), there’s a piece missing now: So […]

Monitoring AWS Costs

I’ve been running my Apex sites on Amazon EC2 for many years now, and I’ve gone through a number of infrastructure upgrades and price changes over time. I have some alerts set up, e.g. if a server starts getting very busy or if my estimated charges go over a threshold. Today I got an alert saying my estimated monthly bill will be over $100 which is unusual.

One of the most useful reports in AWS (more...)

Hide nulls in Value Attribute Pairs report

If you have one record where you want to display multiple columns of information, the 'Value Attribute Pairs - column' report template is pretty nifty.

Some of the packaged applications use this within the breadcrumb bar, above a region display selector, and it looks really tidy.

Nulls shown with tilde

Note, I've modified region attribute setting 'Show null values as' to a tilde (~).

But what if I wanted to hide those null values for (more...)

What is Success?

Late last week I took a quick trip back to central New York (Syracuse-area) to attend a family funeral for my brother’s father-in-law (who I had know for 20+ years). While it was of course a sad occasion, it did allow me an unplanned visit to see my father (for an early father’s day no less), […]

Typ-Konvertierung und cardinality-Schätzung

Jonathan Lewis weit in seinem Scratchpad darauf hin, dass Funktionsaufrufe für Spalten im Fall einer Bedingung "function(column) = constant" üblicherweise zu einer Schätzung von 1% führen. Dies gilt aber nicht für einfache Typ-Umwandlungen: eine Bedingung in der eine Charakter-Spalte mit einem numerischen Wert verglichen wird, verwendet eine Variante der Standardformel zur Berechnung gleichverteilter Werte, nämlich (Anzahl Werte)/(Anzahl distinkter Werte) - wobei die Klassifizierung als Variante damit zusammenhängt, dass die Anzahl distinkter Werte in einer Charakter-Spalte (more...)

Misconceptions about (Regular) Views Revealed when Presenting Editioning Views – Part 2

In a previous post I wrote about one misconception about views that is revealed when I talk about Editioning Views in my EBR (Edition-Based Redefinition) presentations.

This post is about another misconception.

In the part of the presentation in which I “preach” to cover every table with an Editioning View and to replace every reference to tables in the code with reference to the corresponding Editioning Views, I usually get the following question from the (more...)

Blog retrospective

Not long ago I suddenly realized I had published my 100th blog post, so I figure it's time to look back and write down some thoughts about the blog.

I started this blog in 2008, so it has taken me all of 8 years to produce one hundred posts. Not exactly a huge output when compared to many other bloggers, but at least the pace has been reasonably consistent over the years, and hopefully most (more...)

Oracle PL/SQL Programming 6th edition by Steven Feuerstein

opp6_catI have reviewed the latest edition of Oracle PL/SQL Programming. This is not a book, at least for me, that I would read front to back. I use it mostly as a reference, but the great thing about this book is that you can also use this book to learn programming PL/SQL from scratch.

The book is nicely ordered into different parts, like Program structure and how to use SQL in PL/SQL (which by the (more...)

SQLcl and Query Change Notification

The database has had Query Change Notification for a while but to use it required a custom program. Such as Tim outlines on his blog Since SQLcl has Nashorn, now it can be integrated with a few lines of javascript to get notified when changes happen.  The script is below and posted.  The catch is QCN only works on Varchars and

Charting Predictions, al a AskTom

The 'grand algorithm' favoured this particular tweet from Connor McDonald in my 'highlights'.
I found this intriguing considering a side project I've been tinkering on. The solution ended up looking much simpler than a model clause, though I'm going to need to let it digest for a while before I fully understand how it works. Maybe read (more...)