RETURNING RECORD INTO

This is an idea for an enhancement to the PL/SQL syntax.

If I have the following declaration:

DECLARE
  in_record mytable%ROWTYPE;
  out_record mytable%ROWTYPE;
BEGIN

I can do this:

  INSERT INTO mytable VALUES in_record;

I can also do this:

  UPDATE mytable SET ROW = in_record WHERE ...;

I can do this, as long as I list each and every column, in the right order:

  INSERT INTO mytable VALUES in_record
  RETURNING cola, colb, colc INTO out_record;

But I (more...)

Ausführungspläne erzeugen und interpretieren

Bisher habe ich auf die Frage nach einer guten Erklärung für Ausführungspläne in der Regel auf Troubleshooting Oracle Performance von Christian Antognini verwiesen - oder auch auf die im Netz verfügbare (und hier verlinkte) gekürzte Version des sechsten Kapitels. Als Alternative oder Ergänzung dazu kommt inzwischen eine gerade von Jonathan Lewis für AllThingsOracle begonnene einführende Serie in Betracht:

Beyond Analytics: MODEL or MATCH_RECOGNIZE

Analytic functions are powerful and efficient, but sometimes they just aren’t enough. When you try analytics and they alone don’t solve the problem, it’s time to think about the MODEL clause – or upgrade to 12c and use MATCH_RECOGNIZE. All three can use partitions and ordering for simpler, more efficient processing. To illustrate, here’s a […]

Datenbank-Videos von Jens Dittrich

An der Universität des Saarlandes habe ich allerlei studiert, aber keine Informatik - trotzdem hätte ich auf die ebenso umfangreiche wie interessante Sammlung der Youtube-Videos von Jens Dittrich vielleicht schon früher mal hinweisen können. Hier findet man Einführendes aber auch komplexe Detailanalysen - und dabei zeichnen sich die Beiträge insbesondere auch durch die unterhaltsame Präsentation aus, für die der Herr Professor Dittrich schon allerlei Preise gewonnen hat. Einmal mehr muss ich bei Carl Einstein borgen: (more...)

Current_Schema and the Data Dictionary

Being a huge fan of Logger, the PL/SQL logging utility, I really wanted this be to included in the project that I'm currently working on. So I downloaded it (link at the bottom of this blog) and included it in our deployment scripts. Done.... at least I thought so, but of course this wasn't the case.

The regular install script for Logger looks something like the following (parts removed and table names are changed):


set (more...)

Dutch Cars – Technology That Fits

I was just in Amsterdam last week, and they have the smallest cars I have ever seen:

small_carAt first, it looks counter-intuitive, given that the average Dutchman seems to be several meters tall. But really small cars is actually a very obvious solution for a crowded city like Amsterdam with many canals, narrow roads and very limited parking. These mini-cars are actually parked on the pavement, probably avoiding the 5 Euro per hour parking fee (more...)

When Joins Go Bad

So far in the joins series we’ve looked at the effect removing joins (via denormalization) has on performance. We’ve seen that joins can cause primary key looks to do more work. Lowering the normalization level to remove these can negatively impact “search” style queries though. More importantly, we’ve seen the real cost of denormalizing to remove joins is when updating records, potentially leading to concurrency waits and application bugs.

So are joins always “good”?

The (more...)

Änderung für NVL costing

Jonathan Lewis weist in seinem Blog darauf hin, dass die cardinality für Prädikate der Form: NVL(spalte, konstanter_datumswert) bis Version 11.1.0.7 mit dem Standardwert "5% for range-based predicate on function(col)" berechnet wurde, aber in 11.2.0.4 eine plausiblere Behandlung erfährt, die das Prädikat in den NULL-Fall und den Fall des Vergleichs der vorhandenen Werte aufspaltet, und dadurch deutlich zutreffendere Schätzungen liefert (was für ähnlich gelagerte Fälle auch schon in früheren Releases (more...)

This just in: Win Dinner with Monty at #KScope14

Amazing but true – you can now enter a contest to win dinner with ODTUG President Monty Latiolais at ODTUG’s annual conference KScope14. This year KScope will be held in beautiful Seattle, Washington from June 22nd – 26th. Who knows what amazing dinner adventure will be in store for the winner! Get the details here: […]

Unusable unique constraint

Another annoying thing about unusable indexes

I’m surprised that I can’t remember coming across this before before.

I want to archive some data from a bunch of partitions.

Unfortunately, I can’t follow the virtuous circle of data lifecycling and partition the table in such a way that I can archive all the data from a particular partition (or subpartition) via exchange partition.

Without going into too much detail and skipping a bunch of other steps, (more...)

Thinking it Through – Nest Smoke Alarm Fail

The company Nest, recently acquired by Google for the usual billions, makes smart thermostats and smoke detectors. Unfortunately, they did not think through the user experience of their Nest smoke and CO detector.

In principle, it’s great that you can turn off your smoke detector by waving your hand at it – like in “oh, cut it out, I just overcooked my microwave popcorn a bit.”

Less great is that if people experience an (more...)

Down with Firefox

Warning: this post is not technical and it is not about Oracle. Brendan Eich recently resigned under pressure from his job as CEO of Mozilla, the makers of Firefox. The reason given was a campaign contribution that Mr. Eich made in 2008. The State of California has the “referendum”: a proposition is submitted to a […]

APEX Shortcuts Use Case

Recently Martin described APEX Shortcuts, a shared component that I don't often use - and probably even less now.

APEX wizards create them for the delete process in forms, but I thought I'd describe an example of how you might use one for your own needs.

The sole purpose I've used them is to generate HTML text from PL/SQL - often for links to appear near text items. For example, you could use them (more...)

Denormalizing for Performance is a Bad Idea – Your Updates are Killing Me

In the previous article in the joins series we compared query performance between a third normal form schema and the same schema denormalized to second normal form. We then extended it the example so our denormalized schema was in just first normal form.

The normalized approach performed better overall. The differences were small though – generally just a few consistent gets and all the queries executed in under a second. As Jeff Atwood points out (more...)

PL/SQL vs SQL

plsql_logoThere is a ‘rule’, I think it was created by Tom Kyte, stating: If you can do it in SQL, do it in SQL. I came across some code the other day that makes perfect sense to do then you are running an Oracle 10g (or earlier) instance. I rewrote the code to use only the EMP and DEPT tables to protect the suspects and maybe innocent.

The function defined is something like this:

On HTTP 401 Unauthorized (with Oracle EPG)

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates. It could happen that suddenly your Apex application that has been working for years starts asking for a username and password in order to access the XDB repository. You hit the Cancel button and all you get is:   401 Unauthorized No html, no images, nothing is returned, your app is

SQL Developer’s Interface for GIT: Cloning a GitHub Repository

SQL Developer 4 provides an interface that allows us to interact with Git repositories. In this post, I’m going to show how to clone a GitHub (A web based hosting service for software development projects that uses the Git revision control system) repository.

First you need to sign up for a GitHub account. You can skip this step if you already have one.

Your account will give you access to public repositories that could be cloned (more...)

The Future of Oracle Forms: The Survey Results

Last month AuraPlayer created and distributed the “Evolution of Forms” survey in conjunction with Oracle product management and PITSS.  The responses about how people use Forms now and their plans for the future gave us great insight into how Forms and its users are evolving. Based on the responses AuraPlayer wants to share some new content.  AuraPlayer CEO, Mia Urman, and Oracle Forms Product Manager, Michael Ferrante, have collaborated to host this webinar at the end (more...)

Add Column DDL-Optimierung in 11g

Im OTN-Forum wurde heute ein Feature erwähnt, das mir bisher entgangen war (oder das ich schon wieder vergessen hatte): das Hinzufügen einer Spalte mit Default-Wert und NOT NULL Constraint kann ohne Update der betroffenen Datensätze durchgeführt werden und erfordert als reine Metadaten-Anpassung nur sehr wenig Zeit. Ohne den NOT NULL Constraint muss hingegen jeder einzelne Datensatz aktualisiert werden, was sehr lange Laufzeiten hervorruft. In 12c ist inzwischen auch die Variante ohne den NOT NULL Constraint (more...)

Off topic – 2014 predictions

Here are my predictions for 2014.

I realise that traditionally yearly predictions are made in the first month of the Gregorian calendar year... but I've been busy. So I'm going to make some predictions in my birthday month and see how things eventuate over the next 12 months. Many sites do this over the new year, and I do love the commentary when one looks back over the previous 12 months. It's amazing, well not (more...)