Designing PL/SQL Programs

When I started out, in COBOL, structured programming was king. COBOL programs tended to be lengthy and convoluted. Plus GOTO statements. We needed program desire to keep things under control.

So I noticed the absence of design methodologies when I moved into Oracle. At first it didn't seem to be a problem. SQL was declarative and self-describing, and apparently didn't need designing. Forms was a 4GL and provided its own structure. And PL/SQL? Well that (more...)

Parallel DML und Buffered Operations

In der letzten Woche musste ich in möglichst kurzer Zeit (und im zum Teil weiter laufenden Betrieb) eine größere Zahl von Aggregationstabellen neu aufbauen und möglicherweise hätte ich dabei Zeit sparen können, wenn ich bei der Abarbeitung meines Blog-Readers nicht so weit im Rückstand wäre. Immerhin hat Jonathan Lewis zuletzt ein Verhalten beschrieben, das mir unter Umständen ein paar Anregungen geliefert hätte. In seinem Beispiel erfolgte eine CTAS-Operation in 4 min, während eine entsprechende INSERT (more...)

SQLCL Monitoring itself with Longops

Longops is a great way to monitor things that take some time to do work.  There's an easy example of using longops on oracle-base here.  I borrowed the script from there and put it into a file named my_slow_thing.sql.  Now here's a nice example of what's possible with sqlcl. The easy way to using this is add it to your login.sql which is what I did. Of course the script could run at (more...)

Viva Las Vegas!

Later this morning I am flying out to Las Vegas to attend my first HIMSS conference. This is the biggest IT Healthcare related event on the planet. I will be hanging out at the Snowflake booth (#14078) in the Clinical & Business Intelligence Knowledge Center, ready to talk about elastic data warehousing and how it can […]

An old dog learns a new trick

Reports of this blogs death have been greatly exaggerated. It has been very quiet here though while I worked on getting the Swedish part of Miracle started. It is now rocking the Stockholm market so it’s time to get back into more geeky stuff.

Talking of which. I have encountered Liquibase for database versioning time after time and always come to the conclusion that it is not what a DBA want to use. I recently took (more...)

Client support for WITH using PL/SQL

My employer has been using 12c for about a year now, migrating away from 11gR2. It's fun working out the new functionality, including wider use of PL/SQL.

In the 'old' world, you had SQL statements that had to include PL/SQL, such as CREATE TRIGGER, PROCEDURE etc). And you had statements that could never include PL/SQL, such as CREATE SYNONYM, CREATE SEQUENCE. DML (SELECT, INSERT, UPDATE, DELETE and MERGE) were in the latter category.

One of (more...)

Talking SQL Analytics with Connor

In about 22 hours I'll be talking with Oracle Developer Advocate (or the southern/eastern hemisphere AskTom adjunct) Connor McDonald about one of my favourite part of Oracle, being SQL Analytics.

If you haven't seen any of his YouTube videos recently, this will be a good chance to catch up and see how easy and practical SQL analytics are, and how you end up with simpler SQL.

I like to think of them like Excel calculations (more...)

Kim Berg Hansen on “Use Cases of Row Pattern Matching in Oracle 12c”

As I write this, I am listening to Kim Berg Hansen explain the MATCH_RECOGNIZE clause. He was kind enough to give me credit for some of the examples and mention this blog. In addition to my blog posts on the subject, you may enjoy my presentation on SlideShare. Please download it to see the animations!

Snowflake Cool Features: Query History 

As promised in the previous post I wrote for Snowflake, here is a deeper dive into one of the Top 10 Cool Features from Snowflake: #10 Result sets available via History There are a lot of times when you want to make a small change to your large query, and want to be able to […]

Zerlegung von Tabellen in rowid ranges

Stew Ashton ist derzeit für einen signifikanten Anteil der ungelesen in meinem Blog-Reader wartenden Artikel verantwortlich, da er gerade eine vielteilige Serie zum Thema "Chunking tables" veröffentlicht hat (veranlasst durch eine entsprechende OTN-Anfrage von Jonathan Lewis bzw. White paper Bezugnahme von Bryn Llewellyn). Die genaue Anzahl der Artikel lasse ich aus, um diese Einleitung generisch zu halten und spätere Korrekturen der Anzahl vermeiden zu können. Freundlicherweise pflegt der Autor im ersten Artikel eine Liste der (more...)

Repository Database License Cost for Oracle Forms 12c Explained

Michael Ferrante, the Oracle Form Product Manager, has been asked to clarify the database repository licensing for Forms 12c. He was generous enough to share his response with the Oracle Forms Community as the second post in our Oracle Forms 12c guest series.  If you’re a Forms guru and want to contribute click here for more information.

Be sure not to miss our webinar with Michael on 2/29 at 1pm EST: Oracle Forms 12c: New Features Unveiled and (more...)

Google Map Apex Plugins

I’ve just published two Apex Region Plugins on that allow you to incorporate a simple Google Map region into your application. They’re easy to use, and you don’t need to apply for a Google API key or anything like that.

1. Simple Map


This allows you to add a small map to a page to allow the user to select any arbitrary point. If you synchronize it with an item on your page, (more...)

Erläuterungen zum Nested Loops Join

Nikolay Savvinov hat zuletzt in seinem Blog eine interessante Artikelserie zum Verhalten des Nested Loops Join veröffentlicht und dankenswerterweise die Ergebnisse und die Links noch einmal in einem summary zusammengefasst. Und diese Zusammenfassung fasse ich jetzt hier zusammen:
  • der Nested Loops Join (NL Join) ist der einfachste Join-Mechanismus in Oracle. Er liest die Daten einer (driving) row source und testet die Ergebnisse gegen die zweite row source (probe-Zugriff, üblicherweise via Index lookup). Da das Verfahren (more...)

Apex API for Tabular Forms

Ever since I started exploring the idea of using a TAPI approach with Apex, something I was never quite satisfied with was Tabular Forms.

They can be a bit finicky to work with, and if you’re not careful you can break them to the point where it’s easier to recreate them from scratch rather than try to fix them (although if you understand the underlying mechanics you can fix them [there was an article about (more...)

Top 10 Cool Things I Like About Snowflake Elastic Data Warehouse

I have now been with Snowflake Computing for a little over two months (my how time flies). In that time, I have run the demo, spoken at several trade shows, and written a few blogs posts. I have learned a ton about the product and what it means to be an Elastic Data Warehouse in […]

Chunking tables 8: second solution

Here is the second complete solution to the problem of dividing a table into chunks of equal size. This is the solution whose first version was marked "correct" on OTN. It is the basis for what Bryn Llewellyn calls "Approx_Method_Sql_Ashton_2" in his white paper.

Parallel partition scans

When a table is accessed by multiple members of a parallel query server set, the execution plan may show the use of block range granules (PX BLOCK ITERATOR) or partition range granules (PX PARTITION [RANGE|LIST|HASH] ITERATOR or PX PARTITION [RANGE|LIST|HASH] ALL).

The basic ideas surrounding these concepts are discussed in numerous blogs and books, including my own, but discussion of partition range granules is normally restricted to either partition wise joins or the creation (more...)

Fire Sale #2: Loading Your Data Vault with Informatica

As I mentioned the other day, Dan and Sanjay have been hard at work re-vamping the Learn Data Vault site and have relaunched it. In celebration they are offering amazing deals on their world class Data Vault Implementation classes. The 2nd course that is ready to go is Implementing Data Vault with Informatica. This online course walks […]

Join mit Datums-Bereichen

Wenn man mit SQL arbeitet, dann gehört die Behandlung von Datumsintervallen zu den Themen mit eher beschränktem Unterhaltungswert. Insofern ist es sehr nützlich, dass Stew Ashton eine Artikelserie veröffentlicht hat, in der er diverse Spezialfälle genauer beleuchtet, mit denen man in diesem Kontext konfrontiert werden kann:

Chunking tables 7: prior sys_guid() ???

The second solution for dividing a table into equal chunks does not do a JOIN. Instead it expands extent rows that contain several chunk boundaries, using an obscure method that I need to explain.