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: 12.1.0.1 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...)

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 https://oracle-base.com/articles/10g/dbms_change_notification_10gR2 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...)

Use the database to do the heavy lifting

Suppose you have data in your PL/SQL program stored in a collection. I am using the EMP table (14 records) but you should imagine you have many, many more records in your collection. If you want to order (sort) your collection in a different manner somewhere in your code you can of course write your own sorting routine but the Oracle database is doing this for ages and probably a lot smarter (after all these (more...)

Subtleties – Part 3 (more workarounds for COLLECT DISTINCT in PL/SQL)

In Part 1 we saw that the SQL function COLLECT with the DISTINCT option is not natively supported in PL/SQL, and saw two workarounds – using dynamic SQL and using the SET function.
In Part 2 we saw that the SET function can operate on Nested Table but not on Varray.
In this post we’ll see two more workarounds.

Inline View

We can first remove the duplicates in an inline view, and then use the (more...)

Checkbox Item check / uncheck all

If you have an ordinary checkbox item based on a list of values, here is a function which will set all the values to checked or unchecked:

function checkboxSetAll (item,checked) {
  $("#"+item+" input[type=checkbox]").attr('checked',checked);
  $("#"+item).trigger("change");
}

For example:

checkboxSetAll("P1_ITEM", true); //select all
checkboxSetAll("P1_ITEM", false); //select none

It works this way because a checkbox item based on a LOV is generated as a set of checkbox input items within a fieldset.

Note: If it’s a (more...)

Kakuro Helper using SQL Query with the PowerMultiSet Function

When solving Kakuro it is essential to know for a given integer X and a given number of elements N all the combinations of N non-repeating digits [1-9] that their sum equals to X.

For example, there is only one combination for creating the number 7 from 3 elements:

1+2+4

And there are 6 combinations for creating the number 15 from 4 elements:

1+2+3+9
1+2+4+8
1+2+5+7
1+3+4+7
1+3+5+6
2+3+4+6

Let’s generate a list of all (more...)

Synchronous Dynamic Actions in APEX 5.1

If you've ever used a PL/SQL dynamic action with the default 'wait for result', you would have seen the following warning if you have the browser console open.

Text for bots: Synchronous XMLHttpRequest on the main thread is deprecated because of its detrimental effects to the end user's experience.

Consider this scenario of dynamic actions on change of P42_ITEM:

Synchronous vs Asynchronous server calls
First JavaScript takes value of P42_ITEM, concatenates a letter and places (more...)

Merge Operationen und überflüssige Spalten.

Vor ein paar Jahren hatte ich hier einen Artikel von Alexander Anokhin verlinkt, in dem erläutert wurde, dass die in der USING clause eines Merge verwendeten Spalten selbst dann in die workarea aufgenommen werden müssen, wenn sie weder im ON noch in der UPDATE-Spaltenliste erscheinen - obwohl es eigentlich durch eine semantische Analyse möglich sein sollte zu bestimmen, dass man diese Spalten gefahrlos ausklammern könnte. Jetzt hat Jonathan Lewis auf einen naheliegenden anderen Aspekt dieses (more...)

SQL Server Wait Typen und Latch Klassen

Zwei großartige Hilfsmittel hat Paul Randal vor wenigen Wochen in seinem Blog bekannt gemacht:

  • SQL Server Wait Types Library: eine Liste mit den seit Version 2005 im SQL Server vorkommenden Wait Typen und Erklärungen zu ihrer Bedeutung, ihrer Verfügbarkeit, ihrer Beziehung zu des Extendes Events, sowie Informationen zum Troubleshooting.
  • SQL Server Latch Classes Library: mit entsprechenden Informationen zu den Latch Klassen.
Diese Links könnten mir allerlei Suchoperationen in Books Online ersparen.

Misconceptions about (Regular) Views Revealed when Presenting Editioning Views

Sometimes when you present an advanced feature, questions from the audience reveal misconceptions about basic features.

It happens to me almost every time I talk about Edition-Based Redefinition. I present Editioning Views, and then I get questions that reveal misunderstandings about views in general.

One such misunderstanding is regarding what is kept in the view definition.

When we create a view as “select * from table”, the * is expanded to actual (more...)